Formula in Valid If not working as expected

AppSheet AppSheet Q&A
,
1

Hi community fellows,

I have a question about my formula that I set in a column

Right now i have 2 table, 1 I called APPROVAL table as shown below

id working_type start_date end_date email

and the other one is TIMESHEET

id id_approval working_date email

I’ve put a formula in the Valid_if section of the column [end_date] and [start_date] to check if the end date is already on the list of [working_date] in TIMESHEET table. It looks something like this

IFS(
  [working_type] = "Regular (Làm Bù)",
  NOT(
    IN(
      [_THIS],
      SELECT(
        TIMESHEETS[working_date],
        [_THISROW].[email] = [email]
      )
    )
  ),
  
  AND(
    [working_type] <> "Regular",
    [working_type] <> "Regular (Làm Bù)"
  ),
  NOT(
    IN(
      [_THIS],
      SELECT(
        TIMESHEET_OT[working_date],
        [_THISROW].[email] = [email]
      )
    )
  )
)

When I test to type in the data in a form, Appsheet always gives me “[end_date] is invalid” error.
The error doesn’t happen when I change the [working_type] to something not “Regular” and also the formula in valid_if of [start_date] doesn’t run into any error even though I put the same formula in [start_date] and [end_date]

2

Your expression currently does NOT cover the use case when [working_type] = “Regular”.

3

Actually if I only add this formula to check only for existing date in the TIMESHEET table:

NOT(
    IN(
      [_THIS],
      SELECT(
        TIMESHEETS[working_date],
        [_THISROW].[email] = [email]
      )
    )
  )

it still give me the same error that I’ve mentioned. This doesn’t happen if I check the date on the TIMESHEET_OT table

FYI, 2 table that I’ve mentioned are identical. The only different is the extra column of Working Type in TIMESHEET_OT

4

Here’s an update to the problem

If the end date and start date are 2 days or more apart (for example: 11/9/2024 - 13/9/2024), then the app will give me “end date is invalid”

But if I chose the end date is the next day of the start date like 11/9/2024 - 12/9/2024 then everything would be fine

Can anyone know how to deal with this? Please help