Duplicate Entry Validation

Ben_Rix
Participant V

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

My first and definitely not last post of the year

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

Solved Solved
0 10 436
1 ACCEPTED SOLUTION

Nope, just do this:

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

View solution in original post

10 REPLIES 10

Steve
Participant V

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.

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])
    )
  )
)

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

Hey Steve, I seem to be getting an error

Hello @Ben_Rix youโ€™re missing a comma after โ€œCase Sessionโ€ i believe

Thats the one, cheers Rafael

Yep! My bad. Earlier posts corrected.

Ben_Rix
Participant V

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

Top Labels in this Space