Create of record should trigger a set of record being created in another table - possible?

behavior
(mahesh G) #1

The app is to help run sessions, where each session has a number of members attending, each member with a specific role/duty.

For this, I have a “Session” table which will holds set of dates for each session held or scheduled.

The set of possible roles that members would perform are defined in a “Role” table.
e.g.
Speaker1
Speaker2
etc

The technical problem is figuring out how to populate a third table “SessionRoles” automatically when a new Session is scheduled i.e. record is created. SessionRoles is roughly a union of the two tables with key fields SessionDate and Role, each field coming from the two tables.

What I would like is when adminstrator creates a new Session record, system has to automatically create new records in SessionRoles for that new Session date. In the example above, if a session record for 03/15/2019 is created, I would like to have SessionRole to have 2 new records created, one for each role in the Role table

03/15/2019 Speaker1
03/15/2019 Speaker2

Possible?

(Steve Coile) #2

Not currently possible from within the app itself.

(Levent Kulacoglu) #3

@Mahesh
You can do it with using AppSheet API and creating a webhook workflow rule for ADDS on your Session table. You can read below page for further info:
https://help.appsheet.com/integrations/api/adding-records-to-a-table

(mahesh G) #4

Thanks @LeventK.
Using the API is defintely a very intriguing idea. I did stumble upon this page previously but could not figure out how to make it work for me.
So in my case add on table1 will trigger copy of all records in table2 to be copied to table3 plus including one column value from table1. Since the API payload is dynamic (versus hard coding the payload like in the sample), I was not sure how to construct the payload for such an API call.
Thoughts?

Thanks in advance.

(Levent Kulacoglu) #5

@Mahesh
Constructing the payload is easy as explained in detail on the page I have referenced. Let’s assume that you have constructed a webhook workflow for Table1 / ADDS_ONLY and your intention is to record some rows in Table2:

Firstly, you need to turn on the REST API from Manage > Integrations > IN: from cloud services to your app

Webhook endpoint

URL:
https://api.appsheet.com/api/v1/apps/{appId}/tables/{tableName}
URL Parameters:
{appId} is your own app's id where you can found when you turn on REST API
{tableName} is the table name that you will connect to and add records; in this example it will be Table2
NOTE: If you have spaces in your table name, then you should encode that table name by replacing all space characters with %20. i.e. My Table Name >> My%20Table%20Name

Payload

{
"Action": "Add",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [
{
"Table2Column1": "< <[Table1Column1]> >",
"Table2Column2": "< <[Table1Column2]> >",
"Table2Column3": "< <[Table1Column3]> >"
},
{
"Table2Column1": "< <[Table1Column1]> >",
"Table2Column2": "< <[Table1Column2]> >",
"Table2Column3": "< <[Table1Column3]> >"
}
]
}

This payload will create 2 records in Table2 and for each column you specify in Table2, it will record the value of the current row/record values of the Columns you have specified in Table1.

(Fernando López) #6

@LeventK, Is it possible to use START: and END to add “n” number of rows?

(Levent Kulacoglu) #7

@Fernando_Lopez
I believe it’s possible :slight_smile:

1 Like
(mahesh G) #8

@LeventK, In the sample you have actually entered each row with each column value which of course makes sense in that example.
But I do not see how this will work when I have a dynamic number of rows in my table and I need copy each one of them into another table. One would need some construct which would iterate over each row and build the payload like you showed, and I do not know how one would achieve that part.

.

(mahesh G) #9

@Fernando_Lopez, what do you mean by “START” and “END”. I do not see anything like in API sample here

(Fernando López) #10

I think you can use START: and END to iterate over a LIST or a SELECT, just like a report TEMPLATE. Check the documentstion about templates on reports.

1 Like
(mahesh G) #11

@Fernando_Lopez, I am interested in learning how it works out for you. Please do post your experience with using Start/End

(Philip Garrett) #12

See this article which explains how to invoke the API from a webhook. It contains an example JSON template that includes Start and End.

1 Like