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]

Hi again @Fields

I suggest you try this experiment (reference is this post of mine from a while back), then all will be much clearer.

1. Create an SP list called Test_list, make its Title column not required and add two choice colums, both with three choices: Choice A, Choice B, Choice C.  Call these columns MyChoice and MyOtherChoice
2. Create a new blank PowerApp (not from the sharepoint list, but directly a blank app)
3. Add the list as a data source to your app
4. Create a dropdown in the app (call it Dropdown1) and set its Items property to:
   

   Table({Id: 1, Value: "Choice A"}, {Id: 2, Value: "Choice B"}, {Id: 3, Value: "Choice C"})

5. Create another dropdown in the app (call it Dropdown2) and set its Items property to:
   

   Filter(Table({Id: 1, Value: "Choice A"}, {Id: 2, Value: "Choice B"}, {Id: 3, Value: "Choice C"}), Id <> Dropdown1.Selected.Id)

6. Create a button in the app and set its OnSelect property to the following
   

   Patch(Test_list, Defaults(Test_list),{
       MyChoice: {Id: Dropdown1.Selected.Id, Value: Dropdown1.Selected.Value, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"},
       MyOtherChoice: {Id: Dropdown2.Selected.Id, Value: Dropdown2.Selected.Value, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}
       })

7. Run and test, pressing the button will create a new item in the SP list

I have just done all of the above in the last 5 minutes, the result is the screenshot below.

Please let me know how you get on, and if you have issues with any of the above steps.

[Fields]

Hi @Meneghino

It works perfectly, it wasn't on the begining because i was putting My Choice, instead of MyChoice!

It works great, but is there anyway of doing the patch without being forced to put the name all together? Or that a must do? (Don't get me wrong, ill change that if i need to)!

[Meneghino]

Hi @Fields

In my view it is never a good practice to use spaces in column names, list names etc.  It is not a major issue if you do, and in PowerApps this is dealt with by enclosing any names that have a space with single quotes.  So in your case that would be:

'My Choice'

Please let me know how you get on.

[Fields]

Hey @Meneghino,

So, i tried to do that in my APP, and this time, the first custom dropdown and second were doing great, was no errors! But when i tried to patch it, this happens:

[Meneghino]

Hi @Fields, if you hover over the bold red underlined 'test', what error do you get?  What other errors appear as you hover over the other pieces?

Are you sure you remembered to add test as a data source to your app?

Also, what is the purpose of the initial NewForm command?

Thanks.

[Fields]

Hey @Meneghino,

When i hover the bold red 'test', it says that some patch arguments are invalid!

It also says that the column '1st Person Name' doesn't exist, even though, that's literally the name on the sharepoint list!

Same with the '2nd Person Name'

I put the Newform, because like i said earlier, i put 3 diferent sharepoint lists, in 3 diferent screens, so when they all submit, they all submit at the same time, for different sharepoint lists!

So in the end will be SubmitForm(X); SubmitForm(Y); Submitform(Z)

Being X;Y;Z the name of the editforms

[Meneghino]

Hi @Fields

It is also not very good form to start column names etc. with a numeric character, and that is the issue.

Because of the initial numeric character and the space, then column name '1st Person Name' has been turned into something like the following:

OData__x0031_st_x0020_Person_x0020_Nam (now that it has no spaces or special characters, you do not need the single quotes around it)

The way to check the exact column name in your case is to create a label in your app, then in the Text property start typing the following:

First(test). [with a dot at the end]

Then you should see a list appear below and you can scroll down and select the column name that you can then copy to the Patch function.

That is why I avoid spaces (use _ instead) in names and avoid names starting with numbers.

If you use the Patch, you will not need forms and hence the SubmitForm, you just Patch all the fields you need in one relatively simple statement.

Please let me know how you get on.

[Fields]

Hi @Meneghino,

That means i have to patch every single item in the end? I have tried using patch in that exact aspect, and it didn't worked out well... I will try my best and let you know (Gonna edit this comment)!

Also, i tried the    First(test). and it said that the "." was an invalid name...

Edit: My bad, i got it all wrong!

Conclusion: It still says that i am doing wrong here! It says that, that name doesn't exist, even though i copied from there...

[Meneghino]

If you have the patience and if I have understood what you need correctly, then I think there is a very good way to show you how to do the whole thing very efficiently with only one screen.

If you agree, the first thing I would like to understand is why you have three SharePoint lists instead of just one with an extra column to identify which one of the three types the individual entry belongs to.

PS I can spend time on this since my wife and children are away on holiday...

[Fields]

Hi @Meneghino,

The only reason that i have 3 lists, is in the beginning, i tried to do everything together, but, instead of sending everything in one item, it was sending in 3 items, and i don't understand the patching very well (It seems so confusing to me), so i did 3 sharepoint lists, and i started to work in that way, and it works!

And besides, Even they are exactly the same, they all 3 different topics, but i wouldn't mind learning a lot more about patching!