ValidIf expression to exclude _ThisRow

[jonbowles84]

Hi there,

I have this formula as a ValidIf expression that works fine to prevent duplicate start times for an initial entry:

Not(In([_THIS], SELECT(Timesheet[Start Time], AND([Start Time] = [_ThisRow].[Start Time],[Date] = [_ThisRow].[Date],[Full Name] = [_ThisRow].[Full Name]))))

Problem is if I go into edit any particular row after it has been saved, the same condition does not allow me to save changes. Are there any solutions to avoid this and allow me to overwrite a row with the same start time? 

Thanks

[Joseph_Seddik · Accepted solution]

Enclose your NOT() statement in a check to see whether the row already exists in the table. 

IF( IN([_RowNumber], Timesheet[_RowNumber]),
  TRUE,
  NOT( ... )
)

[jonbowles84]

Thankyou!

[SkrOYC]

There is a variety of ways to solve this.

Maybe the easiest would be to remove from your list expression your current row, although I don't use it.

You could vote here

Different Valid_If for each case: Add new record t... - Google Cloud Community

[Steve]

Not(
  In(
    [_THIS],
    SELECT(
      Timesheet[Start Time],
      AND(
        [Start Time] = [_ThisRow].[Start Time],
        [Date] = [_ThisRow].[Date],
        [Full Name] = [_ThisRow].[Full Name],
        [_RowNumber] <> [_ThisRow].[_RowNumber]
      )
    )
  )
)