I have been attempting to resolve this Webhook Scheduled Bot issue I’m having. I already have spent 2 days using a number of different AIs to find a solution but nothing they suggest works.
Criteria: Every day at 2:00 am, the bot should go through all the records in a Slice and create for each record found (currently only one for testing) the associated Work Orders for all Facilities listed in the Facilities field.
Issue: When using the Webhook (found out I can’t use my working Looping Actions I created) in a Scheduled Bot. Those must use the Webhook method of using <> <>. No matter what I try when attempting to retrieve the values in the facilities field it always returns the ID key for the single document from the Slice (in this case, value of “Fy45762c”) and then attempts to use this value in the Facility field of the Work Orders which is NOT an allowed value.
Background:
A Slice (called “ReOccur Events – Due Today” ) that is created from a table called Reoccur Events.
Table Reoccur Events has the following fields: ID (Text - Key), Facilities (EnumList) of type Ref, Work Details (Text), plus a couple other fields that are used in all new Work Orders.
The Slice is filtered currently to one document that has the following data:
ID - “Fy45762c”
Facilities - L001, L002 (These were selected from a dropdown tied to a table called tblFacilities
Work Details - Change Filters
A table called Work Requisitions that holds the work orders that need to be performed that have the following fields:
Request ID - (Text) populated using UNIQUEID() function
Facility - (Text) populated from Webhook but must be a valid allowed value
Work Details (Text) populated from Webhook
Plus other fields that also come from Webhook
Bot Details:
Event - Scheduled to run every night, For each Row in Table: ReOccurr Events - Due Today
Process - Webhook using Reoccur Events targeting table Work Requisitions
They either return “Fy45762c” or “L001, L002” I need “L001” for the first record to be created and “L002” for the second record to be created.
Here is a printout from one of the errors in the Audit Logs:
“Errors”: “Error: Can’t add or update a row because of an invalid value. Update the value and try again.\n\n\tRow ID to correct: a1979f5e \n\tInvalid value for column Facility: Fy45762c”,
and from below you tell it knows there are two records to be created, except it keeps trying to populate the wrong data in Facilities field.
Your syntax does not seem to be correct it needs to be something like Facilities[ID] where ID is the key column of the table. Then you would use simply <<[_THISROW]>> for Facility.
Thanks for the reply. Unfortunately your suggestion had been previously tried. It always returned the same value of the ID for each of the items in the EnumList. However, I finally did get it to work and I’ll post the solution below.
Ah, it wasn’t clear to me that you had a column named [Facilities] that you were trying to expand into individual rows.
If the [Facilities] column is defined as an EnumList with a base type of REF, then you should be able to assign to the START/END block simply this - “[Facilities][Facility ID]”
Yes, it seems like it is the same as just [Facilities]. I don’t WHY the extra syntax is required except maybe to reinforce that it is what you really want as opposed to something like “[Facilities][Name]”. It could also be that an EnumList by itself doesn’t have the right structure to iterate over.
Lastly, it looks lie you are using a common technique to choose from a list of items on a Form and then trigger an automation to expand those choices into a child table. One issue you might run across with this method is that there is a delay as to when those child records are available. That delay can vary based on server processing times.
There is another approach that is widely used - Looping with actions. You can create a set of actions that perform this looping process and attach them to the Form Saved behavior. The benefit is that the child rows are available to the user immediately. The downside is that there is a small bit of a learning curve in implementing the set of actions to loop and identify when to stop looping but it’s no more difficult than figuring out how to use the AppSheet API!!!
If you want to take advantage of action based looping, you can get started with more details on it here. Thanks to @Steve for starting the whole Looping with actions “phenomena”!!!
