Solved: Add selected values from a table (Projects) to a s... - Page 2

LegoLow7 · 01-29-2025 01:17 AM

Hi, Ive been looking at actions and how I could use them to run a regular (but not scheduled) task.

Scenario

Scenario is that I have a projects table (called tbl_SOW) and a projects update table (called tbl_Update_SOW). 'Row ID' is the key field for both of these tables. tbl_Update_SOW includes a Ref field back to tbl_SOW called 'SOW_tbl_ref'

I am using an AppSheets database for this (not google sheets).

tbl_SOW is a list of all projects, live and complete. tbl_SOW includes a field called SOW_Status, which is an Enum field, with values of 'Live' and 'Status'

What I would like to do is have a user perform an action (click a button perhaps?) towards the end the month where they trigger an event to generate records within tbl_Update_SOW that need updating with a description of the current progress against that project:

Grab a list of all current 'Live' projects from tbl_SOW (filtered on the Enum field SOW_Status)
Based on that list create a new entry for each row into the tbl_Update_SOW table (carrying over the value of 'SOW' field from tbl_SOW and adding this into the SOW_tbl_ref field (also a Ref colum) within tbl_Update_SOW
Then I would like to update the new records in tbl_Update_SOW, so that the field tbl_Reporting_Month_YYYYMM_tbl_ref is updated within tbl_Update_SOW. The field tbl_Reporting_Month_YYYYMM_tbl_ref is how I identify rows within the tbl_Update_SOW that are the updates for the specific period.
1. the field tbl_Reporting_Month_YYYYMM_tbl_ref is a ref field within the table tbl_Update_SOW, which references the table tbl_Lookup_Reporting_Month

Once the records are created within tbl_Update_SOW the user can update the records with the latest update.

Ive been looking into AppSheet actions, but can't seem to get this to work. can anyone help?

List of tables and fields:

tbl_SOW

Row ID (key)
SOW (Text)
SOW_Status (Enum - 'Live', 'Complete')

tbl_Update_SOW

Row ID (key)
SOW_tbl_ref (Ref)
tbl_Reporting_Month_YYYYMM_tbl_ref (Ref)
SOW_Update (Text)

tbl_Update_SOW

Row ID (key)
Reporting_Month_YYYYMM (Text)

WillowMobileSys

If I understand correctly, the suggested approach is to provide a button that some user taps and generates a set of pre-loaded NEW rows based on current Project details. A view of these new rows is presented and user then select their desired rows to EDIT it and complete the details. Do I have that right?

First, I'd like to suggest a slightly different approach that would save on implementation and may be more user friendly.

Create an action shown on each project, maybe named 'Project Update" that when tapped opens the Update Form and pre-fills fields from the Project row leaving only those fields the user needs to complete. This creates the rows only as needed.

You can then create a view of projects with Updates Needed - i.e. do NOT yet have a Project Update row created. The Project row drops from the view when the Update is inserted.

For placement of the action, you could just show the action on the Project Detail view. But that then means a user need to tap the Project row to open the Detail view and then tap the button. Instead I like to add Inline Actions. These are actions that are shown in the Table view of rows itself.

So the idea from a users perspective is this...at some point the Updates Needed view is shown with a list of Projects needing updates. The user taps the button on the Project row tey wish to update. The Project Update Form opens pre-populated with all the needed Project details and user fills in the remaining fields and taps Save. With the new Update Row added, the Project falls off of the updates needed view.

***********
YOUR REQUEST....

If the above still doesn't appeal to you then to perform the pre-copy of the tbl_SOW rows into tbl_Update_SOW rows, you'll need two actions

1) Action of type "execute and action on a set of rows". Set the Reference Rows expression to select the list of tbl_SOW rows. You set the Reference table totbl_Update_SOW row (yes same table) and the Reference Action as described in 2 below. This is the user Button and placement may be a challenge.

2) Action of type "add a new row to another table using values from this row". This allows you create the tbl_Update_SOW row and assign it values from the current tbl_SOW row.

Once, you have this action set working, you will still have the need to present a view, this time of tbl_Update_SOW rows. I would think you'd also want to remove the completed rows or indicate that are completed somehow.

I hope this helps!!

View solution in original post

Add selected values from a table (Projects) to a second table (Project Updates) and update a value