Unique Date Entry for SharePoint form

[ghutchinsMicron]

I am trying to limit entries to a SharePoint list within a Power App form.  I created a calculated column in the SharePoint list based on the date field (Audit Date) the user enters into the form.

• Audit Period = Standard SharePoint Date field set to 'Date Only'
• AuditPeriod =YEAR([Audit Period])&" - "&MONTH([Audit Period])

Now in PowerApps Maker I want to use the 'OnChange' action for the DateCardValue14, (AuditPeriod), to give immediate feedback the entry already exists...  instead of waiting for the user to submit the form then find out it already exists but i cannot figure out the syntax to do that.  If that is not the right way to do it i am open to other possibilities.

If(LookUp('ListName',AuditPeriod) = DataCardValue14, <Do not submit but post a message that the record exists>)

If any of that makes sense I humbly ask for a, little bit of assistance.  TIA

[LaurensM]

Hi @ghutchinsMicron,

Could you try the code below in the OnChange of the AuditPeriod datacard:

If(CountRows(LookUp('ListName',AuditPeriod = Self.Selected.Value)) = 1, Reset(Self); Notify("This record already exists", NotificationType.Error)) 

Adjust the field names where needed.

This will notify the user that the record already exists and reset the DataCard control.

I hope this helps!

[ghutchinsMicron]

@LaurensM thanks this irons out the reset and error message.  Used what you suggest and some variations of it and getting errors saying trying to compare text/record, or text/number

I am trying to validate the date the user inputs in the Audit Period (space between) datacard on the app form.  If this date translates to a pre-existing AuditPeriod (no spaces) field in the SPO list, then i would use your reset and error script above.  I am thinking that the 'OnChange' action for the 'Audit Period' input on the form is where the validation will occur.

I created the calculated AuditPeriod field, (Year(Audit Period)&" - "&MONTH(Audit Period)), since they could pick any other date in the month.  I need only one entry per month.  I then use that to get a unique entry for each month

Hope that makes sense

[ghutchinsMicron]

@LaurensM   after working with it today this is what I came up with for the 'OnChnage' property for the "Audit Period" field on the form....  has an issue with the second variable vLookItUp...  i think.   Do you see where i am going and what it would take to get the errors removed?

//Set a variable with the date entered in the format matching the SharePoint column
Set(vAuditPeriod, Year(ThisItem.'Audit Period')&" - "&Month(ThisItem.'Audit Period'));

//Need to lookup the SPO list to find any instances of the variable matchin any data point in the list.
Set(vLookItUp, LookUp('SharePoint List', AuditPeriod = vAuditPeriod))

//Set up if the either allow the entry of reset and display a notification of the error.
If(vLookItUp =vAuditPeriod,

//What to do if the if statement is true
true,

// what to do if the if statement is false
Reset(Self); Notify("Duplicate Entry", NotificationType.Error))

[LaurensM]

@ghutchinsMicron I tested this and the code should work.

However, I do get a delegation warning with the Set(vLookItUp...) because we are using calculated columns.

This column type is unfortunately not delegable. Note: With small datasets (<500 or after adjustments<2000), it should not be an issue.

Should your list be larger than those limits, please change the calculated column to a regular Text column and patch the data manually with a concatenate() function from within the app (during record creation).

If you want to learn more about delegation & the limits, I will link the documentation here.

If I answered your question fully, would you be so kind as to accept it as a solution.
Thanks!

[ghutchinsMicron]

@LaurensM Sorry but i don't seem to be getting anywhere....   AuditPeriod is a SharePoint calculated field, so it does not have a 'OnChange' action, therefore I am putting the 'OnChange' script in the 'Audit Period' datacardvalue's 'OnChange' action.

 This part is giving me an "Invalid Argument type (Record) . Expecting a (Table) value instead, 

CountRows(LookUp('ListName', AuditPeriod = vAuditPeriod))

//Set a variable with the date entered in the format matching the SharePoint column
Set(vAuditPeriod, Year(ThisItem.'Audit Period')&" - "&Month(ThisItem.'Audit Period'));

//Need to lookup the SPO list to find any instances of the variable matchin any data point in the list.
Set(vLookItUp, LookUp('ListName', AuditPeriod = vAuditPeriod));

//Set up if the either allow the entry of reset and display a notification of the error.
If(CountRows(LookUp('ListName', AuditPeriod = vAuditPeriod)) = 1,

//What to do if the if statement is true
true,

// what to do if the if statement is false
Reset(Self); Notify("Duplicate Entry", NotificationType.Error))

[LaurensM · Accepted solution]

@ghutchinsMicron regarding that error, could you swap LookUp with Filter:

CountRows(Filter('ListName', AuditPeriod = vAuditPeriod)) > 0

[ghutchinsMicron]

Thant works, you worked hard for that kudo!  Thanks for your time and assistance.

[LaurensM]

Glad I could be of help!