How to change data on a different table

[BobG]

I have 2 tables a Job table and a Log table. When an employee checks into a Job initially a row is added to the Log table. Multiple employees can check into the same job. When an employee checks out (from the Job table) I need the related Log table row to be updated to “Checked Out”.

I have a column on the Log table that references the Job table which created a “Related Log” list.

I have a “Check Out” action button for the Job table that changes the Job table status and an action on the Job table to execute and action on a set of rows but can’t, after many hours of trying, figure out how to change the status on the related Log table. I don’t know if I’m using an incorrect reference the the action or if its a workflow issue. I read the support articles on workflows and references and still can’t make it work.

[Steve]

This will be a two-part process, since it sounds like the user is interacting with the Job row rather that with the Log row directly. The action attached to the Job row will need to identify the appropriate Log row, then prompt that Log row to update itself with the new status.

[BobG]

Yes, the user interacts with the Job row.

How do I identify the appropriate Log row then prompt the Log row to update?

[Steve]

BobG:

How do I identify the appropriate Log row

Good question! How would you know which of the Log rows correspond to the current user? And of those rows, how would you know which one should be updated with the check-out?

[BobG]

USEREMAIL() = Log[Email] but in the log the user will have other Jobs he has checked in to previously and possibly the same Job.

Example - Checks out for the day and back in the next day.

[BobG]

Still can’t get things working.
I must just have a mental block and am not grasping procedure.
Can you help with the details please.

[Steve]

You need to provide a way to determine which Log entry should receive the check-out status change. Is there anything about the correct Log entry that sets it apart from all others?

[BobG]

Only the LogKey would be unique to each Log row.
But how can this be uniquely referenced in a formula if it is not a column value in the Job table.
I’m also not sure how to use the Related Log List VC that AppSheet created. I assume it is a list of log entries related to the each record in the Job table but I don’t know what data if contains.

It sounds like what I need to do is possible according to your 1st response but I just am not getting it. I’m feeling like I should just scrap this and take another approach?

[Steve]

Here’s what I’m understanding:

• Each job has many log entries.

• Each log entry has a Ref column value that points to the job it’s for.

• Each log entry identifies the user who added it. The identity is the user’s email address (from USEREMAIL()).

• Each log entry has a status column to note the user’s current status. If the status is not Checked out, the user has not checked-out.

• An individual may check-into and out of an individual job many times.

Given the above, the current user’s most-recent log entry for this job (while viewing a job) that doesn’t yet indicate check-out can be found with:

ANY(
  SELECT(
    [Related Logs][key-column],
    AND(
      (USEREMAIL() = [Email]),
      ("Checked Out" <> [Status])
    )
  )
)

Replace key-column with the name of the key column of the Log table.

Can you give this a try to see if it seems to identify the right log entries?

[BobG]

Enter the formulas in the referenced rows field of an action named “Update Log Status to Checked Out” on the Job table “Data: execute an action on a set of rows” and it returns this error:

“The expression is valid but its result type ‘Ref’ is not one of the expected types: List”

Have I entered it in the wrong Action?

[Steve · Accepted solution]

Ah, sorry, my bad. Remove ANY() from around SELECT():

SELECT(
  [Related Logs][key-column],
  AND(
    (USEREMAIL() = [Email]),
    ("Checked Out" <> [Status])
  )
)

[BobG]

Sorry about the delay. That solved it.
Thanks.