Duplicate Entry Validation

[Ben_Rix]

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

[Steve (Steve Coile)]

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.

[Ben_Rix]

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.

[Ben_Rix]

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

[Steve (Steve Coile)]

Nope, just do this:

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

[Ben_Rix]

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

[Ben_Rix]

Hey Steve, I seem to be getting an error

[Rafael_ANEIC-PY (Rafael Almeida)]

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

[Ben_Rix]

Thats the one, cheers Rafael

[Steve (Steve Coile)]

Yep! My bad. Earlier posts corrected.

[Ben_Rix]

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