Answered

Prevent Duplicate Records on multiple fields in a Collect Formula

(0) Share

Report

Posted on by PPanfili

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Verified answer

CarlosFigueira Microsoft Employee on at

Like
a
(4)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
PPanfili 45 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Aaronpatel 38 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Aaronpatel 38 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Anjum 2 on at

Like
a
(0)

Report
Copy link

Link copied!

I have a form that is connected to SQL Server. My requirement is an pop up message would pop up when user attempt to submit form that has the same record with existed record in Employee Table( Date ,EmployeeID)
Another requirement is that i want to submit one EmployeeID in one Date at once,If one EmployeeID already have entered in one date ,it will prevent to duplicate records(Each Date will have Unique ID)
Currently, this is what i do:
If(
IsBlank(
LookUp(
Employee,
Date = DatePicker2.SelectedDate And Employee_ID = TextInput4.Text
)
),
SubmitForm(Form2),
Set(
showDialogue,
true
)
);

Was this reply helpful? Yes No