Warning for Duplicates when submitting form

[TesDA]

I'm hoping to add a notifcation() that would prevent someone from entering duplicate employees (see image below for example)

Gallery: Resource_Gallery

Form: Form_Emp

Button code currently (basically all of the current notifications):

If( 
	
	Len(varRecord_TextLabel.Text)<1, Notify("We have lost the connection to your project. Please go to the gallery to find your request or start over", Warning),
	Len(BRListYesNo_DropDown.SelectedText.Value)<1, Notify("Is this a Borrowed Resource?", Warning),
	BRListYesNo_DropDown.SelectedText.Value = "Yes" && Len(ResourceName_DataCardValue.SelectedText.Value)<1, 
		Notify("Please select the Borrowed Resource (BR), if no BRs appear please submit an ICWO by clicking the link for the ICWO below", Warning),
	BRListYesNo_DropDown.SelectedText.Value = "No"&& Len(EmpTitle_DataCardValue.SelectedText.Value)<1, 
		Notify("Please select the title of your employee, if you dont know the title please use outlook or do a canvas search", Warning),
	Len(BRListYesNo_DropDown.SelectedText.Value)>1 && Len(PLC_DataCardValue.Text)<1, Notify("Please provide the Project Labor Cat for your resource", Warning),
	Len(BRListYesNo_DropDown.SelectedText.Value)>1 && Len(PLCRate_DataCardValue.Text)<1, Notify("Please provide the rate for your resource", Warning),
	Len(BRListYesNo_DropDown.SelectedText.Value)>1 && Len(EmergencyLocation_DropDown.SelectedText.Value)<1, 
		Notify("Please let us know if you need to provide the location of your resource", Warning),
	Len(BRListYesNo_DropDown.SelectedText.Value)>1 && EmergencyLocation_DropDown.SelectedText.Value = "No" &&
		(Len(DataCardValue2.Text)<1 || Len(DataCardValue16.Text)<1 || Len(DataCardValue26.SelectedText.Value)<1 || Len(DataCardValue21.Text)<1), 
		Notify("Please provide the Street, city, state and/or zip where your resource will be working", Warning),
	Len(BRListYesNo_DropDown.SelectedText.Value)>1 && Len(DataCardValue31.SelectedText.Value)<1, Notify("PLease certify that the resource you have provided meets all labor category requirements -you may need to scroll down", Warning),	
SubmitForm(Form_Emp);ResetForm(Form_Emp);NewForm(Form_Emp) )

Let me know if I can provide anything else that would be helpful

[poweractivate]

@TesDA In SharePoint there is setting on each column called "Enforce Unique values" - I recommend to do it that way on the column if it is possible as it is simpler and stronger way. Doing this will generate notification automatically and will block the insertion of the record into SharePoint. If using Dataverse you even have more options to do this and it is possible still in SharePoint data source too.

[TesDA]

I'm using sql server and I really need to do this directly in the app before any communication with sql server occurs

[RandyHayes · Accepted solution]

@TesDA 

First off, you are using the SelectedText property a lot in your formula.  You should change this and use the Selected property instead as the SelectedText property is deprecated and should be avoided going forward.  The Selected property is more reliable in this case.  I have replaced the SelectedText property with Selected in the formula below - note, the use of Value may not be accurate to your control as the column name will be dependent on the Items property of the control.  So, substitute the Value with the actual name of the column you need to check.

If you want to check for an existing record in your formula, consider the following change:

If( 
    Len(varRecord_TextLabel.Text)<1, 
        Notify("We have lost the connection to your project. Please go to the gallery to find your request or start over", Warning),
    Len(BRListYesNo_DropDown.Selected.Value)<1, Notify("Is this a Borrowed Resource?", Warning),
    BRListYesNo_DropDown.Selected.Value = "Yes" && Len(ResourceName_DataCardValue.SelectedText.Value)<1, 
        Notify("Please select the Borrowed Resource (BR), if no BRs appear please submit an ICWO by clicking the link for the ICWO below", Warning),
    BRListYesNo_DropDown.Selected.Value = "No" && Len(EmpTitle_DataCardValue.SelectedText.Value)<1, 
        Notify("Please select the title of your employee, if you dont know the title please use outlook or do a canvas search", Warning),
    Len(BRListYesNo_DropDown.Selected.Value)>1 && Len(PLC_DataCardValue.Text)<1, 
        Notify("Please provide the Project Labor Cat for your resource", Warning),
    Len(BRListYesNo_DropDown.Selected.Value)>1 && Len(PLCRate_DataCardValue.Text)<1, 
        Notify("Please provide the rate for your resource", Warning),
    Len(BRListYesNo_DropDown.Selected.Value)>1 && Len(EmergencyLocation_DropDown.Selected.Value)<1, 
        Notify("Please let us know if you need to provide the location of your resource", Warning),
    Len(BRListYesNo_DropDown.Selected.Value)>1 && EmergencyLocation_DropDown.Selected.Value = "No" &&
     (Len(DataCardValue2.Text)<1 || Len(DataCardValue16.Text)<1 || Len(DataCardValue26.Selected.Value)<1 || Len(DataCardValue21.Text)<1), 
        Notify("Please provide the Street, city, state and/or zip where your resource will be working", Warning),
    Len(BRListYesNo_DropDown.Selected.Value)>1 && Len(DataCardValue31.Selected.Value)<1, 
        Notify("PLease certify that the resource you have provided meets all labor category requirements -you may need to scroll down", Warning),	
    LookUp(yourTable, yourEmployeeNameColumn = yourEmployeeNameControl.Selected.Value, true),
        Notify("Duplicate employee, please change", Warning),	

    SubmitForm(Form_Emp)
)    

you will see the last If condition that does the lookup.

And finally, you should NOT have a ResetForm and NewForm IN the same formula as your submit....what if the Submit fails???  In your existing formula, the user would never know - the form would be cleared and they would think all was well.

Instead, put the following in the OnSuccess action of the Form:

ResetForm(Form_Emp);
NewForm(Form_Emp)

I hope this is helpful for you.

[TesDA]

@RandyHayes That worked flawlessly. Appreciate your help on this one. Good to know about SelectedText - I need to make some changes elsewhere.