Conditional Control From Save Button

[desmond_lee]

How to write this expression ton control the input form in 2 conditions as follows.......
Condition 1 :-
1. Col G Initial Value now() in SCAN IN table. The input time range on the form is valid_if between 7am ~ 21pm reference to now() time at column G.
2. The shift code in Employee table has to be 11.

Condition 2 :-
1. Col G Initial Value now() in SCAN IN table. The input from the form is valid_if between 19pm ~ 7am (next day) reference to now() time at column G.
2. The shift code in Employee table has to be 12.

[TeeSee1 · Accepted solution]

My expression does not work in your app.

Within your LOOKUP, a comma is missing between "EMPLOYEE" and "EMPLOYEE ID".

EDITED,

There is also a missing comma in my post...

[TeeSee1]

You can try the expression below (please adjust the col/variable names/comparison conditions to fit your definition/needs)

SWITCH(
 LOOKUP(
  [_THISROW].[Emp Code],
  "emp table"
  "emp code",
  "shift id"
 ),
 11,
 AND(
  START TIME < [CLOCK IN],
  [CLOCK IN] < END TIME
 ),
 12,
 AND(
  START TIME < [CLOCK IN],
  [CLOCK IN] < END TIME
 ),
 FALSE
)
 

[desmond_lee]

Thank very for replying. I have try but it shows this msg

[TeeSee1]

A comma missing

[desmond_lee]

Hi Tee

I copy and paste exact its already shows that same msg. Couldn't spot where is that comma. Don't mind to tell me? Thank very much

[TeeSee1 · Accepted solution]

My expression does not work in your app.

Within your LOOKUP, a comma is missing between "EMPLOYEE" and "EMPLOYEE ID".

EDITED,

There is also a missing comma in my post...

[desmond_lee]

Hi See

That error msg resolve but this error pops up

SWITCH(LOOKUP([_THISROW].[EMPLOYEE ID],"EMPLOYEE","EMPLOYEE ID","SHIFT CODE"),11,AND(START TIME < [TIMESTAMP IN],[TIMESTAMP IN] < END TIME),12,AND(START TIME < [TIMESTAMP IN],[TIMESTAMP IN] < END TIME),FALSE)

[TeeSee1]

Please read this help doc and adjust time comparison expressions

https://support.google.com/appsheet/answer/10107326?hl=en

[desmond_lee · Accepted solution]

Thanks Teesee

I have a another way to get around in the below expression.

CONCATENATE(DATE([TIMSTAMP IN]), ", ",IF([SHIFT CODE]=11, IF(TIME([TIMSTAMP IN])<"16:30:00", "07:00:00",TIMENOW()),IF([SHIFT CODE]=12,IF(TIME([TIMSTAMP IN])<"16:30:00", "08:00:00",TIMENOW()), "")))