Compare multi-choice combo box to Share Point list Text field populated from a multi-choice combo box

[SBlack6]

I need to verify the options selected in a multi-choice combo box are the same (although order doesn't matter) as the options in a text field from SharePoint list being referenced in a gallery.

Set Up: The app allows people to submit inputs. Once inputs are submitted, they can look at all their previous inputs from a gallery and make edits if needed. I track changes to any fields that are updated in the SharePoint list the inputs are patched to by creating additional Yes/No dropdowns that compare the input within a control in the app to the corresponding field in the SharePoint list. A yes for the dropdown value will track that a change was made and when the inputs are patched to SharePoint documents the field the change was made.

This is straight forward for text inputs and single value combo boxes, but I cannot get the multi value combo boxes selected items comparisons with the previous submission (stored on a SharePoint list). This may have to do with the fact that inputs from the original submission are patched with Concat to the SharePoint list. All SharePoint list fields are text fields, and I get the inputs from the initial submission of information into SharePoint using the Patch function, not using edit forms.

Names of Databases/Controls:

• Share Point list name: Sand Box
• Name of (one of the) combo boxes for new submissions: cbLang

*When "Other" is selected as one of the options of cbLang, it opens up a text input called txtLang, the text input is patched to SharePoint with the inputs from the combo box.

• Name of SharePoint column this combo box is patched to: Language(s)
• Name of gallery on the view/edit screen of the app: galSandBox
• Name of corresponding combo box in the view/edit screen: cbLang_1
• Name of dropdown which compares values: ddChLang

Patching Code for the above combo box from the New Submission screen is:

Patch(

    'Sand Box',Defaults('Sand Box'),

    {'Language(s)':Concat(Filter(cbLang.SelectedItems, !(Value="Other")), Value&Char(10)) & txtLang.Text})

Default Selected Value for cbLang_1:

{Value: galSandBox.Selected.'Language(s)'}

*Note: the default values appear as a single block of text, so you cannot remove only one choice; if you need to delete a value, you would need to delete the entire input.

Default Selected Value for ddChLang:

If(ddLang_1.Selected.Value<>galSandBox.Selected.'Language(s)',"Yes","No")

The above works so that if the user never makes a change to the combo box it will accurately mark the input as no change. The above also works if you add one item to the default selected value and then remove that value. Example: if the initial inputs were "English" and "Spanish", and the user adds "French" and then removes "French", the dropdown will accurately mark the input as no change.

However, if the user deletes the initial default inputs and then realizes they didn't mean to change that field and put the same inputs back to how they originally were, the dropdown will still mark the input as changed. Example: if the initial inputs were "English" and "Spanish", and the user deletes the "English Spanish" input, then reselects "English" and "Spanish" the dropdown will mark the input as changed, despite the fact that the initial input and the edited input have the same text and will look identical in SharePoint.

Is there a way to fix my code so the dropdown will compare the SharePoint list field to the selected values in the combo box as I'm intending to do?

[WarrenBelz]

Hi @SBlack6 ,

You have not provided your Filter code, but this blog of mine should point you in the right direction.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[SBlack6]

@WarrenBelz: Thank you for the the response. I was not able to get any improvements trying methods listed in the blog. I believe the issue with attempting to use that method is that my SP list column for 'Language(s)' is a text field rather than a choice column. I have it as a Text field so I can Patch both the multiple select combo box and the text input which appears if the "Other" option is selected in the combo box to the same cell within the "Language(s)' column.

I was able to get a little closer to the ideal by adding a variable to the input field, varChLang, which is false until the combo box is changed. With this variable set, I updated the Default value of ddChLang to: 

If(varChLang=false,"No",
If(Concat(
Filter(cbLang_1.SelectedItems, !Value="Other")), 
Value&Char(10)) txtLang_1.Text<>galSandBox.Selected.'Language(s)',
"Yes","No"))

With this I get am able to achieve a value of 'No Change' if I do not make any changes to the combo box or if I edit the combo box and replace the selected values, including the 'Other' text input, but only if the combo box inputs are selected in the same order as they appear in my SharePoint list.

Is there a way to make the dropdown list selected values not dependent on the order the combo box selections are made while still keeping the initial submission for both the combo box and the text input patch through to the same cell within SharePoint?

[WarrenBelz]

Hi @SBlack6 ,

The main thing I can initially see here is that the second argument (which is missing a bracket) needs to be tested against a value of some sort.

 If(
      Concat(
      Filter(
         cbLang_1.SelectedItems, 
         !(Value = "Other")
      ), 
      Value & Char(10)
  ),  // equals what ?

[SBlack6]

@WarrenBelz: 

It is testing to see if it is not equal to the information in the SharePoint list for the item the gallery is referencing. I double checked my code and realized I lost an & the last time I pasted it:

 If(
      Concat(
      Filter(
         cbLang_1.SelectedItems, 
         !(Value = "Other")
      ), 
      Value & Char(10)
  )  & txtLang_1.Text
<> galOOE.Selected.'Language(s)',
"Yes",
"No")
)

Above is what is I currently have which works as long as the selections are made in the same order as the original selection. I believe this is because the original submission uses Concat, turning the selections into a single text string, so the If statement is comparing if the entire text string is exactly the same, including order, rather than checking to see if the individual selected options and the Other text field initially selected are all present. I'm not sure if there is a way to do this when combining the initial submission combo box values with the initial submission Other text field into the same cell within the SharePoint list.

[WarrenBelz]

Hi @SBlack6 ,

Try this - note the brackets around the ! argument.

If(
   varChLang <> false &&
   Concat(
      Filter(
         cbLang_1.SelectedItems, 
         !(Value = "Other")
      ), 
      Value & Char(10)
   ) & txtLang_1.Text <> galSandBox.Selected.'Language(s)',
   "Yes",
   "No"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[SBlack6]

@WarrenBelz: That code gets me the same result. It will produces a No Change value as long as the combo box inputs are selected in the same order as they are listed in the SharePoint list they are comparing the value to, but if the selections to the combo box are made in a different order it produces a Yes Change value.

I don't know if there is a direct solution to this issue. I was trying to look into using Sort as a workaround, but I haven't figured out how to do a sort of combo box selected values or how to sort the combo box values through the patch function.

[WarrenBelz]

@SBlack6 ,

I cannot see your data and must admit I am a little puzzled on how the second part could equal a string in a field. I only tried to tidy up the syntax a bit.

[SBlack6]

@WarrenBelz 

Not sure if this will help you see what is happening at all, but here is what the combo box looks like in the app when it pulls the data from SharePoint before making any changes to the Language(s) combo box, cbLang_1. The drop down below the languages combo box is the ddChLang which compares what is in SharePoint to what is in cbLang_1 with the code we have been discussing.

As well as what the data looks like in the SharePoint list it is pulling the data from:

In the app, if I delete the default submission and then add them back in the same order, this is how it looks - Notice the drop down indicating if the value is changed from what is in SharePoint still indicates "No"

If I change the order of what I select in cbLang_1 to still include all the same selections, but they are in a different order, the drop down indicating if the field is changed changes to "Yes":

The goal is to have the app recognize that the contents of cbLang_1 and txtLang_1 (which is visible if "Other" is selected in cbLang_1) are the same as the contents in the single cell in my SharePoint list where the information was patched on a previous submission, even if the selected order of items in cbLang_1 is a different order than how it appears in the SharePoint list cell.

If it is possible to use Sort when patching the initial submission and when evaluating the if cbLang_1 & txtLang_1 matches what is in SharePoint, I believe this would work, but I haven't found a way to use the Sort function on combo box inputs.

Is this possible to do (either the Sort function on combo box selections or another method to achieve a similar result), or am I trying to achieve something outside the boundaries of Power Apps functions?

UPDATE: I was able to get the sort function to work for patching and the comparison as a workaround using:

If(
Concat(
Sort(
Filter(cbLang_1.SelectedItems, !(Value="Other")
),
Text(Value)
),
Value&Char(10)
) & txtLang_1.Text

Thank you for helping me walk through this!