cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TesDA
Continued Contributor
Continued Contributor

Warning for Duplicates when submitting form

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) )

 

 

2021-10-21_9-10-58.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
poweractivate
Super User
Super User

@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
Continued Contributor
Continued Contributor

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

RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
TesDA
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,017)