Check for duplicates - two column combination - multi select combo box - SharePoint list

[dearears]

Hi All,

1. I have a multiselect combo-box (ComboBox1) where users can select multiple 'Country' (e.g. US, CA, MX)

2. Before this selection users type their email in (Textbox1). (xxx@email.com)

3. The form is submitted after these two entries. The data is saved in sharepoint list (SPList) as field names "List_Country" and "List_EM"

Example based on above inputs

List_Country   List_EM

US xxx@email.com

CA xxx@email.com

MX xxx@email.com

I would like put a warning text box that alerts the users if an existing "List_Country" & "List_EM" combination is selected during the form entry.

Thanks in advance.

[Gochix]

Hi @dearears ,

Please try :

If(LookUp(SPList,Textbox1.Text = List_EM,List_Country) = ComboBox1.Selected.Value,"Warning!","")

[NandiniBhagya20]

Hi,

you can add a label above submit button set the text property to varTextMandate

OnChange of both the text boxes add below 

Set(varText, Concatenate(Lower(DataCardValue1.Text), DataCardValue2.Text))

on button click you can add below - 

If(!IsEmpty(Filter(SPList, Concatenate(Lower(Title), anotherColumn)=varText)), Set(varMandate, “Similar entry is there in list”), Set(varMandate, “”);SubmitForm(Form1))

[dearears]

Thanks, one of them is a multi select combo box.

[dearears]

Does not work when multiple entries are selected in the combo box.

[Gochix]

Hi @dearears ,

As you have multiple selections possible then this should be checked in a different way.

Try this:

If(Textbox1.Text = "","",
If(Or(LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,1)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,2)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,3)).Value),
"warning",""))

[dearears]

Thanks ! This assumes that the number of selection can only be upto 3. In my scenario the number of selections can change and be upto 10.

[Gochix]

Hi @dearears ,

Yes, If you look in the code I provided then you can see that this goes up to 3 selections. However you can change and add another 7 selections by changing only the end of the code. 

If(Textbox1.Text = "","",
If(Or(LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,1)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,2)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,3)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,4)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,5)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,6)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,7)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,8)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,9)).Value,
LookUp(SPList,Textbox1.Text = List_EM,List_Country) = First(LastN(ComboBox1.SelectedItems,10)).Value)
,
"warning",""))

[v-jefferni · Accepted solution]

Hi @dearears ,

I assume that the List_Country column allows multiple selections. Please check and see if below formulas will work for you:

1. OnChange of the Combo box:

ClearCollect(colCountry, Self.Selected.Value);
UpdateContext(
    {
        varCheck:
        IsEmpty(
            Filter(
                AddColumns(
                    colCountry,
                    "Check",
                    If(
                        ThisRecord.Value in LookUp(SPList, List_Em = TextBox1.Text).List_Country.Value,
                        true,
                        false
                    )
                ),
                Check = true
            )        
        )
    }
)

2. You can use this variable varCheck (Boolean) to control Visible of an Error Label, or Display Mode of the submit button:

If(varCheck, DisplayMode.Edit, DisplayMode.Disabled)

Best regards,

[dearears · Accepted solution]

Thanks your logic appears alright. But the formula does not work. I suspect something is not right here..

ThisRecord.Value in LookUp(SPList, List_Em = TextBox1.Text).List_Country.Value,

Thoughts?

Thanks,

Update: Just a parenthesis missing for if.  Appears to be working. Thanks heaps.