Valid If formula

[John_666]

Hi,

My App seems to have selective adherence to the below Valid If expression especially in the marked section.  If you can see the issue or know a way to fix/improve the validation logic I would like to know.

Note [Storage Out] is a date.

Cheers

John

[Marc_Dillon]

"selective adherence"? As in, it's not working under some cases? Can you show an example of such cases?

[Marc_Dillon · Accepted solution]

I would write that valid_if like this:

IF(
  [pickup date] < "07/10/2023" ,
  IN( [client name] , LIST( "d2..." , "8" ) ) , 
  OR( [_THIS] <> "Invoiced" , ISNOTBLANK( [Related Xeros] ) , [Job Type] <> "STORAGE ONLY" , ISNOTBLANK( [Storage Out] ) )
)

Although that logic is my attempt at converting exactly the logic that I see in your expression. I imagine your issue is a mistake in the logic itself, not the way the expression is written. Maybe my way of writing the expression will make you realize the error in your logic?

[John_666]

Thanks Marc,

While your expression didn't work quite the same way as mine, you were right in that by working through it I found the issue.

It was a simple mistake of not de-referencing the [Job Type] column and so the test was not picking up the invalid combination of inputs as it was returing the ID string not the text.

AND(
[Pickup Date] >= DATE("07/10/2023"),
[_THIS] = "Invoiced",
[Job Type].[Job Type] = "STORAGE ONLY",
[Storage Out] = ""
),
FALSE

Thank you for assisting and I will work in some of your efficiency suggestions into my expression!

Cheers

John