Only Allow Unique Entries to SharePoint List

[ghutchinsMicron]

I have the following in my 'OnSave' property for  SharePointIntegration which I am trying to use to only allow unique Site/Year/Month entries in my SPO list.  Now i am getting a curious problem.  The first IsBlank statement has an error that says I cannot compare Site' = DataCardValue2.Selected.Value.  The error shows Record to Record (???) or Text to Record depending on single quotes on Site.  The end goal here is that I am trying to look at the SPO list and if there already is an entry for that Site/Year/Month...  not allow the data to be submitted.  Can someone point me in the right direction?  

thanks in advance,

gh

If(
IsBlank(LookUp('Global EHS OSHA Data','Site' = DataCardValue2.Selected.Value ).Site)
&&
IsBlank(LookUp('Global EHS OSHA Data',Year.Value= DataCardValue3.Selected.Value ).Year)
&&
IsBlank(LookUp('Global EHS OSHA Data',Month.Value= DataCardValue4.Selected.Value ).Month),

SubmitForm(SharePointForm1),
Notify("An entry for that site and date has already been made")
)

[iAm_ManCat]

Hey @ghutchinsMicron 

I would do this on the SharePoint side rather than relying on complicated formulae in Power Apps - create a new column that will hold the value (site/month/year), then set that column as the identifier for unique items in that list by changing its properties to only allow unique items:

Then when you try to submit to it, only unique items will be allowed, so they cannot duplicate or re-use existing values,

Cheers,

Sancho

[ghutchinsMicron]

Yes...  I could do that but people would have to fill out the whole form and then find out that the data cannot be entered.  I dont want to waste their time, or the time i spent developing the code so far.  I am so very very close to being there I just need to figure out why the data cannot be compared.  

• Site = DataCardValue2.Selected.Value  (Error is "These data types cannot be compared.  Record, Text)
• 'Site' = DataCardValue2.Selected.Value  (Error is "These data types cannot be compared.  Record, Text)

If(!IsBlank(LookUp('Data', Site = DataCardValue2.Selected.Value ).Site)
, SubmitForm(SharePointForm1)
, Notify("An entry for that site and date has already been made")
)

[iAm_ManCat]

What type of field is Site?

If it is saying it's a record, then there will be properties within it, so type Site. (dot) and then see what it brings up - I had a guess at 'Value' since its probably a choice column

If(
   !IsBlank(
      LookUp(
         'Data', 
         Site.Value = DataCardValue2.Selected.Value
      ).Site
   ), 
   SubmitForm(SharePointForm1),
   Notify("An entry for that site and date has already been made")
)

[ghutchinsMicron]

First...  Thank you for the resolution for the syntax issue.

Secondly...  I also would like to know what the .Site is doing on the end of the ISBLANK() statement.  Any help is appreciated.

If(
    IsBlank(LookUp('Data', Site.Value = DataCardValue2.Selected.Value ).Site) &&    
    IsBlank(LookUp('Data', Year.Value=  DataCardValue3.Selected.Value ).Year) && 
    IsBlank(LookUp('Data', Month.Value= DataCardValue4.Selected.Value ).Month) ,

SubmitForm(SharePointForm1), 
Notify("An entry for that site and date has already been made")

)

[iAm_ManCat · Accepted solution]

Hey @ghutchinsMicron 

So the reason we do this is that a LookUp returns a record that contains multiple columns - IsBlank checks a column value for blank not a record or multiple values - and you might think it would know which field to check, but it won't, it will guess - the problem then is that it might check Title or some other field for whether its blank

IsBlank() checks for if a column value doesn't exist (so we use .site)

IsEmpty() checks if there are no records in a table/collection

In your current code, you are looking up three individual lookups, which will not work as you want it to, as it will find ANY record with a blank site and then try to find ANY record with a blank Year and then ANY record with a blank Month, you should combine those into one record lookup as that is what we are looking for, we are looking for if there is already one record which matches that Site and Year and Month:

If(
   IsBlank(
      LookUp(
         'Data', 
         Site.Value = DataCardValue2.Selected.Value && 
         Year.Value = DataCardValue3.Selected.Value &&
         Month.Value= DataCardValue4.Selected.Value
      ).Site
   ),
   SubmitForm(SharePointForm1), 
   Notify("An entry for that site and date has already been made")
)

Cheers,

Sancho

[ghutchinsMicron]

Sincere thanks for the explanation!  That really helps my understanding of how it is working.  It is so obvious to me now that you have explained it so well.

[iAm_ManCat]

Happy to explain 🙂 We can't all know everything and we all start at different levels so I'm happy to share the knowledge - if you'd asked me this five years ago I wouldn't have known 😂