cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PPanfili
Level: Powered On

Prevent Duplicate Records on multiple fields in a Collect Formula

So, what I am doing is Collecting a bunch of information, and making it go to a Spreadsheet.  I have various fields, Last Name, First Name, Grade, Etc..(see all fields in attachment.)

 

How can I make it to prevent the User from entering the same exact record?  I currently have something set up to hide the "Add To Points & Dis Log" button.  However,  I have it set to only trigger when it is pressed.  I cant wrap my head around how to disable it, if all the data on this form, already matches data in datasource?

 

Here is the code for the button, that is doing the collection, it will give you details of all my fields, and where i am collecting them too.

 

Collect(All,{Last_Name:LastNamePoint.Text, First_Name:FirstNamePoint.Text, Grade:GradeDropPoint.Text, Gender:GenderDropPoint.Text,Infraction:StudentOffensePoint.Text,Incident_Date:DateofOffensePoint.Text, Code:CodeInput.Text, Point_Assigned:PointsInput.Text, Discipline_Assigned:Discassinpoints1.Text, Actual_Resolution_Date:REsolutionDatePoints.Text,Points_YTD:PointsYTD.Text, Ethnicity:EthnicDropPoint.Text}); Collect(Log,{Last_Name:LastNamePoint.Text, First_Name:FirstNamePoint.Text, Grade:GradeDropPoint.Text, Code:CodeInput.Text, Administrator:AdminInput.Text, Power_School_Grade:PowerGradeText.Text, Disc_Assigned:DisciplineInput.Text, Start_Date:EndDateText_1.Text, End_Date:EndDateText.Text, ISAP_Periods:PeriodGivenInput.Text, Points:PointsInput.Text});UpdateContext({ClickableYesNo: true})

 

How do I disable the button if the info on this sheet, is already in the datasource?Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Prevent Duplicate Records on multiple fields in a Collect Formula

You can set the DisplayMode property of the button to an expression that will check whether the item already exist. For example, if you need the set of (First_Name, Last_Name and Incident_Date) to be unique, you can use this expression:

If(
    IsBlank(
        LookUp(
            All,
            And(
                Last_Name = LastNamePoint.Text,
                First_Name = FirstNamePoint.Text,
                Incident_Date = DateofOffensePoint.Text))),
    DisplayMode.Edit,
    DisplayMode.Disabled)

It tries to look up an item from the 'All' collection; if it is not found, then the IsBlank function will return 'true', and the button will be on DisplayMode.Edit (it can be clicked). Otherwise (an item was found) it will be disabled.

 

If you need the uniqueness condition to have more columns, you can then add more comparisons to the expression above.

View solution in original post

5 REPLIES 5
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Prevent Duplicate Records on multiple fields in a Collect Formula

You can set the DisplayMode property of the button to an expression that will check whether the item already exist. For example, if you need the set of (First_Name, Last_Name and Incident_Date) to be unique, you can use this expression:

If(
    IsBlank(
        LookUp(
            All,
            And(
                Last_Name = LastNamePoint.Text,
                First_Name = FirstNamePoint.Text,
                Incident_Date = DateofOffensePoint.Text))),
    DisplayMode.Edit,
    DisplayMode.Disabled)

It tries to look up an item from the 'All' collection; if it is not found, then the IsBlank function will return 'true', and the button will be on DisplayMode.Edit (it can be clicked). Otherwise (an item was found) it will be disabled.

 

If you need the uniqueness condition to have more columns, you can then add more comparisons to the expression above.

View solution in original post

PPanfili
Level: Powered On

Re: Prevent Duplicate Records on multiple fields in a Collect Formula

I like your method.  

 

I ended up going with;

 

If(LastNamePoint.Text exactin All.Last_Name && FirstNamePoint.Text exactin All.First_Name && StudentOffensePoint.Text exactin All.Infraction && Discassinpoints1.Text exactin All.Discipline_Assigned && DateofOffensePoint.Text exactin All.Incident_Date, Disabled,DisplayMode.Edit)

 

I am not sure how accurate my method is as of yet.  Do you see any drawbacks to the way I did above?

Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Prevent Duplicate Records on multiple fields in a Collect Formula

If you have the following items:

 

Last_Name  First_Name  Infraction  Discipline_Assigned  Incident_Date
---------|-----------|-----------|--------------------|--------------
  Doe    |   John    |  Speeding |  Fine              | 2017-12-17
  Roe    |   Jane    |  Parking  |  Towing            | 2017-12-18

And John Doe has a Parking violation (with Towing discipline) on 2017-12-18, then your logic will consider the record to already exist, but that's not the case. You need to do either a Filter with all conditions at once (and use CountRows) or the LookUp expression.

Aaronpatel
Level: Powered On

Re: Prevent Duplicate Records on multiple fields in a Collect Formula

What if there is a drop down? I have dropdowns for brand and models based on a customer (User().Emal). I concatenated User().Email&brand_dropdown.selected.text&model_dropdown.selected.text to create a unique records and my title field in the SharePoint holds these value. I want the app to show a message when a user selects a same combination of brand and model to display "you have completed the survey for these selection, please choose another value or exit". Any assistance is appreciated.
Aaronpatel
Level: Powered On

Re: Prevent Duplicate Records on multiple fields in a Collect Formula

I am able to achieve the unique record functionality by creating a text box that captures Lable1 = User().Email&brand_dropdown.selected.text&model_dropdown.selected.text, then Lookup the Title from the Sharepoint list where Lable1 = Title if so display the records.  Then on the submit button, I chose a condition to disable a button if there is a value (!IsBlank) in Lable1.

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (6,167)