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

expressions
(Joseph Corbett) #1

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.

(Praveen Seshadri (AppSheet)) #2

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

(Joseph Corbett) #3

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.

(Joseph Corbett) #4

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

(Aleksi Alkio) #5

@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.

(Joseph Corbett) #6

@Aleksi_Alkio

Awesome idea!

Works like a charm!

(Aleksi Alkio) #7

You’re welcome