I've got a Valid_If expression to validate th...

expressions
(simon bailey) #1

I’ve got a Valid_If expression to validate the length of time of a shift and make sure it’s on quarter hours =AND([_THIS] > [Start Time] ,[_THIS] <([Start Time]+(10/24)),TRUE,IN(MINUTE([_THIS]-“00:00:00”),LIST(0,15,30,45)))

It works on chrome, but not on ipad or android. I’ve tried it without the quarter hours and it still doesn’t work. Locale is set correctly, I’ve removed he expression and then saved a date to test it. Any ideas? Thanks

(Tony Fader) #2

@simon_bailey1 Hmm, I would expect it to behave the same independent of what device you’re using. That could be a bug and I’ll look into it.

You might consider making separate “hours” and “minutes” fields in your form. The minutes field could be an Enum column with values 0, 15, 30, 45. That would make it easier for the user to enter the time, and would also prevent invalid inputs.

You could then combine the two values in a separate column using an app formula.

(Steven Coile) #3

What is ([Start Time]+(10/24)) doing?

(Steven Coile) #4

What is TRUE as the third argument to AND() there for?

(simon bailey) #5

@tony the field is date and time and needs to be together as it feeds into other stuff to work out which day they’re working etc. I can leave that bit out, that just an extra check as I check duplicate jobs, but only each 15 minutes, so it’s handy. It is weird that chrome is fine. Thanks for looking.

(simon bailey) #6

+Steve Coile ([Start Time]+(10/24)) is adding 10 hours to the start time, so a shift can’t be longer than that. I don’t know why TRUE is the third argument :slight_smile: someone helped me with the expression and it worked. I’m assuming that’s not the problem as chrome is working with it in.

(Aleksi Alkio) #7

@simon_bailey1 the “TRUE” argument is not needed in here. It’s only needed if you try to use IN expression like IN([_THIS],LIST(0,15,30,45)). This will generate a dropdown where AND(TRUE,IN([_THIS],LIST(0,15,30,45))) eliminates the dropdown list.