How do i change a certain value in Table A when i add a new row in Table B?

Hello. I’m working on a time-tracking app and would like to update the column “active” in the user table when that user checks in or out by adding/modifying rows in the timesheet table. Is this possible? If so how?

The purpose is to show a list of all users grouped by who is active and who is not.

0 11 822
11 REPLIES 11

Steve
Participant V

You’ll want to use actions, possibly as part of a workflow.

Thanks for the quick reply. Didn’t manage with actions alone but will check out workflows. never messed about with that.

Can’t seem to figure it out. Made a workflow rule that should run when my table “timesheet” is changed in any way. Also made two actions for my “user” table that changes status to “in” or “out”. So far so good.

However these actions are not visible under the workflow rule -> change data -> action.
I only see actions connected to the “timesheet” table there. Not for the “user” table that the actions I want to run are connected to.

What am I missing?

On you first table define an “Action:execute an action on a set of rows”.
That allows an action triggered for one table to invoke an action on another table.

The action on the other table can be a “Grouped” action that triggers a set of actions on that other table.

Thank you for the answer. Still haven’t gotten it to work but i think something is wrong with my select statement to generate a list for the “execute an action on a set of rows” action on table B.

SELECT(TableA[name], [name] = [_THISROW].[name])

I thougt this would get me a list of the names in TableA that matches the names that got added or changed in TableB and that the action would be run on these rows.

The actions work when i put them as buttons so I figure the problem must be with this select statement.

Have you tried temporarily adding a virtual column on table B that uses the expression as its AppFormula? You can then use the expression wizard to test the result.

If that fails, reach out to Steve Coile or Aleksi who are both expression wizards.

@Martin_Eidensten
Hello Martin have you try Mr. Aleksi’s sample on event action? https://www.appsheet.com/portfolio/531778 I was doing the same this as you mentioned, and it worked for me.

Sorry @Aleksi for quoting you work

Steve
Participant V

Spent more time considering this. You may not need actions or workflows at all. The below makes assumptions about table and column names, adjust as appropriate:

  1. In the Users table, add a virtual column named Latest to contain a Ref to the user’s latest time sheet entry, with an app formula of:
MAXROW(
  "Time Sheets",
  "_ROWNUMBER",
  AND(
    ISNOTBLANK([User]),
    ([User] = [_THISROW].[User])
  )
)
  1. Make the Active column of the Users table a virtual column with an app formula of:
IFS(
  OR(
    ISBLANK([Latest].[Out When]),
    ([Latest].[Out When] > NOW()])
  ),
  "Active"
)

How the Latest column app formula works:

  1. MAXROW("Time Sheets", "_ROWNUMBER", ...) gets the row from the Time Sheets table with the highest number in the _ROWNUMBER column, but only from those rows matching the given criteria (...; see below). Usually, the higher the value of _ROWNUMBER, the newer the entry. See also MAXROW().

  2. AND(..., ...) limits the rows considered by MAXROW() to only those that match both of the given criteria. See also AND().

  3. ISNOTBLANK([User]) selects only rows where the User column (of the Time Sheets table) is not blank, just in case a time sheet entry was incorrectly made. See also ISNOTBLANK().

  4. ([User] = [_THISROW].[User]) further limits the selection to only those rows that are for this row of the Users table. [User] refers to the value from the Time Sheets table, [_THISROW].[User] to that from the Users table.

How the Active column app formula works:

  1. IFS(..., "Active") returns the text, Active, if the given criteria (...; see below) is met. If not met, a blank value is returned. See also IFS()

  2. OR(..., ...) either of the given criteria (below) must be met. See also OR().

  3. ISBLANK([Latest].[Out When]) looks at the Out When date-time column of the user’s most-recent time sheet entry. If Out When is blank, the user hasn’t clocked-out yet, so must be active. This criteria presumes the mere existence of the time sheet record indicates the user clocked-in and that the corresponding In When column is not blank. See also ISBLANK().

  4. ([Latest].[Out When] > NOW()]): if Out When isn’t blank, the user has clocked-out, but may have clocked-out in the future. If so, the user is still active until that time. See also NOW().

Thanks for the excellent explanation @Steve! The instructions really help understand your approach and allow for re-use in other situations. Thanks for sharing!

Wow Steve thanks a lot! Will have to messa around a bit with that and also read it a couple of more times. Never used or really understood ref so this might be a good learning opportunity!

Thanks again!

Also worthwhile reading:


Top Labels in this Space