How to not add the same answer in two different columns (Choice columns)

[Fields]

Hey guys,

This question is pretty straight forward!

I have two choice columns, with the same exact items:

User A

User B

User C

What i want is, when they first choose the first choice column, if they pick User A, User A will dissapear on the second choice column!

If they pick User B in the first columns, the User B will dissapear on the second column, and exact same with the User C!

This serve to stop them to give the exact same answer in two diferent columns!

Is there any ideia how to do this?

[Meneghino]

OK, so let's confirm my understanding.

1) You are asking for 3 responses on three different topics from the same person

2) The responses have the same structure for all three topics

3) The structure is the following: 7 ratings (1-5) and 2 choice columns and 3 multiline texts (12 columns in total)

Is that correct?

[Fields]

Exactly like that! But the columns have diferent names in each list! So, in the first list, They have USER A, B, C, the second list have the USER 1, 2 ,3, and in the third list have the USER !, ", #

[Meneghino · Accepted solution]

Ok, then it is even easier than I thought.  I suggest that we do not use choice columns for the users but store the text instead.  For the topic column I would store an integer (but more on the topic later)

Here is what I suggest:

1) Create a dummy app without worrying about the data source initially

2) Create the correct data source (i.e. one SP list with the correct structure), connect it and then add the patching function

For the first step you should do this:

1. Create a new blank app
2. In your app screen place vertically in order
   1 dropdown called DropdownTopic
   7 rating controls called Rating1 to Rating7
   2 dropdowns called DropdownUser1 and DropdownUser2
   3 textiputs called TextInput1 to TextInput3
3. For the Items property of DropDown topic use the following with your real topic names:
   

   Table({Id: 1, Topic_name: "Topic A"}, {Id: 2, Topic_name: "Topic B"}, {Id: 3, Topic_name: "Topic C"})

4. For the OnStart property of the screen use this:
   

   ClearCollect(MyUserChoices,
   Table(
   {Topic_Id: 1, Value: "User A"},
   {Topic_Id: 1, Value: "User B"},
   {Topic_Id: 1, Value: "User C"},
   {Topic_Id: 2, Value: "User 1"},
   {Topic_Id: 2, Value: "User 2"},
   {Topic_Id: 2, Value: "User 3"},
   {Topic_Id: 3, Value: "User !"},
   {Topic_Id: 3, Value: "User X"},
   {Topic_Id: 3, Value: "User #"}
   )
   )

5. For the DropdownUser1 Items property, use this:
   

   Filter(MyUserChoices, Topic_Id = DropdownTopic.Selected.Id)

6. For the DropdownUser2 Items property use this:
   

   Filter(MyUserChoices, Topic_Id = DropdownTopic.Selected.Id, Value <> DropdownUser1.Selected.Value)

Once you have all of this, the rest is even easier.  Please let me know how you get on so far.  The only difficulty may be if you see numbers instead of text choices in the dropdowns, but there is an easy fix, you just need to choose the correct column in the advanced properties of the dropdown.

[Meneghino]

PS I just tested and all works as expected.  There is only one thing you need to do.  Once you have set the OnStart property, you need to save the app, close it, and reopen for editing.  This is because the OnStart only fires once when the app is opened, so you need to open the app in order for the choices collection to be initialised.

[Meneghino · Accepted solution]

Once all the above works then for the second step you can create a new list in SharePoint called whatever you like but please avoid spaces and starting it with a number.  Let's say you call it Responses.

Then create the following columns in the list:

• One column called Topic of number type
• Seven columns called Rating1 etc. of number type
• Two columns of single line of text type called User1 and User2
• Three columns of multiple lines of text type called Comment1 etc.

Next go back to the app and add the list as a data source (please see here for how to do this)

Then simply create a button at the bottom of the screen and set its OnSelect property to this:

Patch(Responses, Defaults(Responses),
{
Title: User().Email,
Topic: DropdownTopic.Selected.Id,
Rating1: Rating1.Value,
Rating2: Rating2.Value,
Rating3: Rating3.Value,
Rating4: Rating4.Value,
Rating5: Rating5.Value,
Rating6: Rating6.Value,
Rating7: Rating7.Value,
User1: DropdownUser1.Selected.Value,
User2: DropdownUser2.Selected.Value,
Comment1: TextInput1.Text,
Comment2: TextInput2.Text,
Comment3: TextInput3.Text
}
)

That is all you need to do.

Once this works then there are many ways you can enhance your app:

1) Improve speed of performance if this is an issue (i.e. time it takes to save)

2) Have a process whereby the user does not choose a topic but is guided through each topic in turn (this is not done via three screens but using a context variable)

3) Basically anything you like

PS This might be good reading for you: https://powerapps.microsoft.com/en-us/tutorials/working-with-data-sources/

[Fields]

Hey @Meneghino,

It works great! First time patching, and there was no problems at all! Altough i liked the ideia of the dropdown column having the Topics, for what i am having, it isn't a great ideia, it would be better, if they were actually in split screens, (But im gonna keep this, this will be usufull!)

The only problem is, it doesn't reset when i send an answer, but i think i can work that out! 😄

Ill read the links you send!

I have a question, if i change the name os the Ratings (Per example, to ExpectRatings1, would i had to change it on patch?)

Also, imagine, i put all the topics and the rating and etc..., how do i make it on the patch? (Because, even tho it's the same question, i need several responses from the 3 topics, and each question has a different meaning in each topic)

EDIT: I am trying to do this on 3 different data sources, do i have to submit and patch everysingle answer or is there anyway to patch everything at once?

Please, tell me if you not understood something here!

Once again, thank you for all the help you are giving me!

[Meneghino · Accepted solution]

Hi @Fields

Here is how you do the split screens, for example:

1) For the submit button, add this after the Patch:

; UpdateContext({CurrentTopicMinusOne: CurrentTopicMinusOne+1})

2) Delete DropDownTopic

3) Whereever in the code you have DropDownTopic.Selected.Id replace with CurrentTopicMinusOne+1

4) Add a label on the screen with Text property:

"The current topic is "&
If(
CurrentTopicMinusOne+1=1, "Topic A",
CurrentTopicMinusOne+1=2, "Topic B",
CurrentTopicMinusOne+1=3, "Topic C",
"Error"
)

I am not sure I have understood your question in regards to name changes.  Do you mean if you change the name of a column in a SharePoint list after it has been created?

I am not sure I have understood "Also, imagine, i put all the topics and the rating and etc..., how do i make it on the patch?"

In regards to the data sources, I still don't understand why you need three different SharePoint lists and not one.  Having one list will make your life much easier.

[Fields]

Hi @Meneghino,

About the "Also, imagine, i put all the topics and the rating and etc..., how do i make it on the patch?", you already answered by giving me the code! Thank you about that

And the reason i need several databases, is, because it's a lot easier to analyse and export to excel, rather than having everything in one list, and the 3 screens is to let the user know that: "Hey, you finish this topic, now click here and we move to the other one!".

If i am able to do that... let's say "feeling" in one screen, that would be awesome!

And yes, about the first question "I am not sure I have understood your question in regards to name changes.  Do you mean if you change the name of a column in a SharePoint list after it has been created?", that's exactly what i mean!

PS: I already know the answer of the last one!

[Meneghino · Accepted solution]

Hi @Fields

The latest code I provided with the context variable will give the impression of three screens, please try it.

For exporting to Excel, all you need to do is to filter your SharePoint views by topic.

Anyway, if you really still want to keep three different data sources, then all you need to do is to change the save button OnSelect to something like this:

If(
CurrentTopicMinusOne+1=1,
Patch(Responses_topic_A, Defaults(Responses_topic_A),
{
Title: User().Email,
Rating1: Rating1.Value,
Rating2: Rating2.Value,
Rating3: Rating3.Value,
Rating4: Rating4.Value,
Rating5: Rating5.Value,
Rating6: Rating6.Value,
Rating7: Rating7.Value,
User1: DropdownUser1.Selected.Value,
User2: DropdownUser2.Selected.Value,
Comment1: TextInput1.Text,
Comment2: TextInput2.Text,
Comment3: TextInput3.Text
}
),
CurrentTopicMinusOne+1=2,
Patch(Responses_topic_B, Defaults(Responses_topic_B),
{
Title: User().Email,
Rating1: Rating1.Value,
Rating2: Rating2.Value,
Rating3: Rating3.Value,
Rating4: Rating4.Value,
Rating5: Rating5.Value,
Rating6: Rating6.Value,
Rating7: Rating7.Value,
User1: DropdownUser1.Selected.Value,
User2: DropdownUser2.Selected.Value,
Comment1: TextInput1.Text,
Comment2: TextInput2.Text,
Comment3: TextInput3.Text
}
),
CurrentTopicMinusOne+1=3,
Patch(Responses_topic_C, Defaults(Responses_topic_C),
{
Title: User().Email,
Rating1: Rating1.Value,
Rating2: Rating2.Value,
Rating3: Rating3.Value,
Rating4: Rating4.Value,
Rating5: Rating5.Value,
Rating6: Rating6.Value,
Rating7: Rating7.Value,
User1: DropdownUser1.Selected.Value,
User2: DropdownUser2.Selected.Value,
Comment1: TextInput1.Text,
Comment2: TextInput2.Text,
Comment3: TextInput3.Text
}
)
);
UpdateContext({CurrentTopicMinusOne: CurrentTopicMinusOne+1})

Hope this helps.

[Fields]

Hi @Meneghino,

Well, if you say, that the first ideia goes well with the impression i want to make, i will definately try it! About the views, i tried that way, but for some reason, it always go to the default one! So, if i don't change the default one, it will never change, and we are talking about 3 excels here... 

It might be a browser problem, or i should iniciate explorer as an Admin maybe?

Anyway, thank you for this gigantic help! I finally understood this whole thing of patching! 

Once again, thank you!