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

[LegoLow7]

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:

1. Grab a list of all current 'Live' projects from tbl_SOW (filtered on the Enum field SOW_Status)
2. 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
3. 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 · Accepted solution]

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!!

[LegoLow7]

Thanks for this @WillowMobileSys !

I will take a look at your alternative solution.  One challenge I have is that I require a similar process but instead of projects the updates are for acceptance criteria against the project.  I only have 8-10 projects so I think the in-line approach  will work for those.

However I could have in the region of 50 acceptance criteria against a project, so I think the inline approach would be tedious for those, and hence why I was looking at a different approach of batch updating rows.  If I could figure out how to do this for the projects my plan was to replicate for acceptance criteria.

It might be that I use both approaches, you suggest!

[WillowMobileSys]

---

@LegoLow7 wrote:

and hence why I was looking at a different approach of batch updating rows.

---

If the batch updates are of the kind that you want the same update applied to all the rows, e.g. marking each row as Completed, then there is a built in Appsheet feature for this.

From a Table or Deck view, there is a checkmark icon in the upper right.  When tapped it gives you the ability to select one or more rows.  Then you can choose an action to be applied to all those selected rows.

For more details on this feature click on this article below

Use Bulk actions 

[LegoLow7]

Hi @WillowMobileSys , I have tired to set up the actions - however I seem to be hitting an issue. 

This is how I have setup the actions, I can see that there is an isssue with my selction of rows in "Referenced Rows" but I am not sure how to resolve tis, any ideas?

Action1

Action2

[paulbroomfield]

Hi there,

The Referenced Rows portion of Action 1, is asking you to provide a list of Refs to the Referenced Table aka a list of ids from your tbl_SOW that link to tbl_Update_SOW.

Try the below as your formula

SELECT(tbl_SOW[Row ID],([SOW_Status]="Live"))

The above formula is returning a list of tbl_SOW[ROW ID]'s, and as these are already a ref to tbl_Update_SOW, it will return the rows in the tbl_Update_SOW table that need to be updated.

Let us know how you go

cheers,

[LegoLow7]

Thanks @paulbroomfield!  I shall take a look.

[LegoLow7]

Hi @paulbroomfield - thanks for your help.  The select query has resolved the error - however I am having trouble understanding where to trigger this from.  

It seems in the setup I currently have a user needs to go into the update table to trigger the events at a record level, which seems unintuitive for what I am trying to achieve.  In the current set up I would ideally like someone to go into the tb_b_SOW view and trigger from that location, but I can't figure out which action to trigger first or how to get this to run for all records.

Ideally what I would like is:

1. a stand-alone button somewhere in my app that an admin clicks
2. when they click this button an input field pops up saying something like "you would like to create a new set of records for reporting, please specify the reporting month"  ...to which they would add the reporting month as 2025-01 etc
3. The actions would then kick off, first checking what Projects have a status of live, then adding those records into the child update table.

I (ideally) don't want the admin to have to click each individual row in the projects table (tb_b_SOW) in order to generate the update records (in tb_Update_SOW).

Am I going beyond what AppSheets can do here do you think?

Massive thank you to you and @WillowMobileSys for your help so far!

For context here are the two actions again (note you may have already notice that I have changed the table name slightly from my original post to tbl_SOW to tb_b_SOW....not super important but thought I would mention!):

[WillowMobileSys]

---

@LegoLow7 wrote:

Am I going beyond what AppSheets can do here do you think?

---

No, you are not.

The actions look good are far as I can tell.  You would Action 1 as your button.  Unfortunately, AppSheet identified this action as a "row level" action and will not allow it to be shown as a Primary presented button - one that floats in the view.  Instead the button must be shown on the Detail View (and can be primary there) OR as an Inline button displayed on every table row.  Neither are ideal solutions and is part of the "less user friendly"  portion I alluded to in my prior post.

I hope this helps!

[WillowMobileSys]

FYI.  I just came across this Tip that claims to provide a solution to allowing a Primary button to appear on a Table View even if the actions are considered row level actions.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Actions-triggered-by-a-primary-button-on-table-v...

I have not studied it so not sure how well it works and what is involved.  

[paulbroomfield]

I've done something like this before using a table and some custom actions.

1. You need a table called MenuViews
2. You need an action that is called when a MenuView row is clicked
3. Add a new item to your app menu
4. Set your new view in behaviour when a row is clicked
5. A table called ReportDates
6. You need a form view

Setup:
1. MenuViews table, with the following columns

id

view name

name

image

Example data:

id	view name	name	image
1	AdminRegions	Regions	AdminViews_Images/1.image.082728.png
2	AdminFranchises	Franchises	AdminViews_Images/2.image.082752.png
3	AdminPlatform Mediums	Platform Mediums	AdminViews_Images/3.image.082803.png
4	AdminPlatform Images	Platform Images	AdminViews_Images/4.image.082813.png
5	AdminCollections	Collections	MenusViews_Images/5.image.090313.png
6	AdminGamePlatforms	Games by Platform
7	AdminGamesWithMissingData	Games with missing data
8	AdminPlatforms	Platforms

Note: The image col is there incase you want to display an image as the "button".

Add the new table as a datasource

2. In the views section, goto your MenuViews section and add a new action called "Link to view" It should look like this:

What this does is, when a row in MenuViews is clicked this will open (LINKTOVIEW) a view with the value from the rows [view name] column

3. Add a new item to your app menu, see below screenshot - note the behaviour setting - this should be the name of your action from Step 2

4. Confirm you have set the behaviour as per Step 3

5. report dates table, this will be 1 row and the year_month col will be an Enum of YYYY-MM which will be displayed as a dropdown on your form

e.g.

id	year_month
1

Note: not need to populate the year_month col, we'll do that in AppSheet

Add the new table as a data source, edit the year_month col to look a bit like this (there is probably a better way of doing this, like with a dynamic list, but lets see how this does)

6. Your form, in the reference section of your App Menu, create a new view, set its type to form, name it the same as your view name from your MenuView table, set it's "For this data" to be "ReportDates".

In Behavior for this form, set the "Form Saved" to be your action that adds rows to another table. You'll probably need to update your SELECT to also include the ReportDates.year-month field

Let us know how you go, this is all very off the top of my head 🙂

[paulbroomfield]

Had another thought about this and my first idea is way too complicated:)

you can skip step 1 & 2 about creating the menu table, and instead add a new menu item that links straight to the form

also there’s a part in thr from that I forgot to add, it asks for a row that this form will reference, that should be the [id] = 1

cheers

[LegoLow7]

Thanks @paulbroomfield and @WillowMobileSys - l will take at your suggestions and let you know how I get on!