Duplicate Entry Validation

Happy New Year eveyone, I hope you all had a great holiday.

My first and definitely not last post of the year :slight_smile:

I had to research this formula a bit to figure out how to stop duplicate sessions being created, I found this and implemented it into my app and it works great.

NOT(IN([Ref], SELECT(Case Session[Ref], [Date]=[_THISROW].[Date])))

There is one downside to this formula that it will not let me update or change any detail regarding the session as it thinks that the session is already there. How would I go about amending this formula so that If wanted I could change the time of the session even if it is on the same day?

Regards
Ben

The pattern I currently use:

ISBLANK(
  FILTER(
    "table",
    AND(
      ([column] = [_THISROW].[column]),
      NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
    )
  )
)

replacingtable and column as appropriate. Essentially, are there any rows of the table that have this same column value that aren’t this row? If not, there are no duplicates.

4 Likes

Hi Steve thanks so much for that, essentially are you saying that I need to create a virtual column that has to have values that could potentially be changed i.e date, time, attendance those would be the three variables that change in that session.

I mean, a virtual column value that concatanates the three columns that could change

Nope, just do this:

ISBLANK(
  FILTER(
    "table",
    AND(
      ([column1] = [_THISROW].[column1]),
      ([column2] = [_THISROW].[column2]),
      ...,
      ([columnN] = [_THISROW].[columnN]),
      NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
    )
  )
)
5 Likes

Oh wow, thats pretty cool, i would have just added another virtual column. Like i dont have enough already haha

1 Like

Hey Steve, I seem to be getting an error

Hello @Ben_Rix you’re missing a comma after “Case Session” i believe

3 Likes

Thats the one, cheers Rafael :slight_smile:

1 Like

Yep! My bad. Earlier posts corrected.

3 Likes

Thanks Steve, ive now implemented it into the app and its working great.

1 Like