Setting Virtual Column when adding or editing a ro...

amldee · 10-24-2024 01:39 AM

My table "Calendar form" has a virtual column [Status], an enum type where it will serve only as an indicator/condition. Since this table has a slice, users will only see their rows/entries.
I plan to have 5 values for this column where:
UPCOMING - [Date] is before and during TODAY() but [Time] is before TIMENOW()

HAPPENING - if Date is TODAY() and TIME is TIMENOW() and the remaining time of TODAY()

DONE - Date is yesterday and Time is midnight

RESCHEDULE - if row is edited and edited rows require Approval (so it is transferred to another table), should also be both approved by TL and Manager. When approved, it will set back to UPCOMING

CANCELLED - if [Route Code] aligned is REJECTED from TL approval or Manager approval

My formula is this:

IFS(
AND([Date] > TODAY(), [Time] >= TIMENOW()), "UPCOMING",
AND([Date] = TODAY(), [Time] <= TIMENOW()), "HAPPENING",
AND([Date] < TODAY(), [Time] = "12:00 AM"), "DONE",
AND(
ISNOTBLANK([_THISROW_BEFORE].[Date]),
ISNOTBLANK([_THISROW_BEFORE].[Time]),
OR(
[_THISROW_BEFORE].[Date] <> [Date],
[_THISROW_BEFORE].[Time] <> [Time]
)
), "RESCHEDULE FOR APPROVAL",
OR(
LOOKUP([_THISROW].[Route Code], "Team Leader Approval", "ID", "Team Leader Approval Status") = "REJECTED",
LOOKUP([_THISROW].[Route Code], "Manager Approval", "ID", "Manager Approval Status") = "REJECTED"
), "CANCELLED"
)
Here's the problem, when I add rows, [Status] sets to RESCHEDULE even when row is new or unedited. Also, the table should not see the rows that has [RESCHEDULE] or [CANCELLED] on it. tnx.

Steve

AND([Date] > TODAY(), [Time] >= TIMENOW()), "UPCOMING"

Why is this expression considering TIMENOW()? What has today's time have to do with an event that happens after today? I would think ([Date] > TODAY()), "UPCOMING" would be sufficient.

AND([Date] < TODAY(), [Time] = "12:00 AM"), "DONE"

Likewise, I'd think ([Date] < TODAY()), "DONE" would be sufficient here.

eyadiii

oh, so I used < and > wrong? I just want the [Status] value to be UPCOMING if the [Date] input is before TODAY() and time before the time input.

Steve

when I add rows, [Status] sets to RESCHEDULE even when row is new or unedited.

Try this expression instead:

IFS(
  OR(
    (
      LOOKUP(
        [_THISROW].[Route Code],
        "Team Leader Approval",
        "ID",
        "Team Leader Approval Status"
      )
      = "REJECTED"
    ),
    (
      LOOKUP(
        [_THISROW].[Route Code],
        "Manager Approval",
        "ID",
        "Manager Approval Status"
      )
      = "REJECTED"
    )
  ),
    "CANCELLED",
  OR(
    ISBLANK([Date]),
    ISBLANK([Time])
  ),
    "",
  OR(
    AND(
      ISNOTBLANK([_THISROW_BEFORE].[Date]),
      ([Date] <> [_THISROW_BEFORE].[Date])
    ),
    AND(
      ISNOTBLANK([_THISROW_BEFORE].[Time]),
      ([Time] <> [_THISROW_BEFORE].[Time])
    )
  ),
    "RESCHEDULE FOR APPROVAL",
  ([Date] < TODAY()),
    "DONE",
  ([Date] > TODAY()),
    "UPCOMING",
  ([Time] <= TIMENOW()),
    "HAPPENING"
)

eyadiii

IFS(
OR(
IN("APPROVED", SELECT(Supervisor Approval[Supervisor Approval Status], [ID] = [_THISROW].[Itinerary Key])),
[Date] <= TODAY(),
[Time] < TIMENOW(),
AND(
ISBLANK([_THISROW_BEFORE].[Date]),
ISBLANK([_THISROW_BEFORE].[Time]))
),
"UPCOMING",

AND(
[Date] = TODAY(),
[Time] <= TIMENOW()
),
"HAPPENING",

[Date] = TODAY() - 1,
"DONE",

OR(
[_THISROW_BEFORE].[Date] <> [Date],
[_THISROW_BEFORE].[Time] <> [Time],
[_THISROW_BEFORE].[Previous Schedule (if any)] <> [Previous Schedule (if any)]
),
"RESCHEDULE FOR APPROVAL",

OR(
IN("REJECTED", SELECT(Team Leader Approval[Team Leader Approval Status], [ID] = [_THISROW].[Itinerary Key])),
IN("REJECTED", SELECT(Supervisor Approval[Supervisor Approval Status], [ID] = [_THISROW].[Itinerary Key]))
),
"CANCELLED",

TRUE,
""
)

as of now, this is my formula. But when I edit the row, the value remains UPCOMING even when changing the Time.

Steve

Your expression makes no sense to me. I'm afraid I have no advice to offer.

eyadiii

Understood. Still thanks for the help.
I redo the values and only used 3.

Setting Virtual Column when adding or editing a row