Hello, Would someone be kind enough to tell ...

Hello,

Would someone be kind enough to tell me what I have wrong in this expression? =IF([_THIS]=LOOKUP([_THISROW].[IDKEY],Hours Worked,IDKEY,Concat),

TRUE,

NOT(IN([_THIS],Hours Worked[Concat])))

The [IDKEY] is the actual key in the table I am trying to make sure has no duplicate entries in.

The [Concat] column is a column I am trying to make sure has no duplicates once the row is added to the table.

0 6 333
6 REPLIES 6

Joseph, I take it you are adding this as a Valid_If constraint on the Concat column, which is part of the Hours Worked table, is that right?

And are you finding that the IF() always returns true?

I suspect you want to see if there is any row whose IDKey does not match but whose Concat value matches.

i.e. COUNT(SELECT(HoursWorked[IDKey],

AND([IDKey] <> [_THISROW].[IDKey],

[Concat] = [_THISROW].[Concat]))) = 0

That is correct.

I created the Concat column to ensure that the user(s) can not add the same employee as working on the same date.

I replaced my expression with yours, however I am still allowed to put in the duplicate values.

@Joseph_Corbett Would it be easier to add a virtual column with the userโ€™s IDKey and date like CONCATENATE([IDKey],":",[Date]) and if you set this as a key column, you would not need any validation check.

@Aleksi_Alkio

Awesome idea!

Works like a charm!

Youโ€™re welcome

Top Labels in this Space