Interaction with Bots and Actions

[John_666]

Hi,

I thought I could create a simple BOT/Action relationship to modify a column value on a trigger.  Not to be.  I suspect it is a simple problem.

I have a working Bot that on a change in the table Job Board sets the colour column [colour] to the value "green".  I wanted to add a Step in the Bot to then set the [Inv Status] in a ref list [related Xeros] to "invoiced".  The Bot above works fine.

Next I added a new steop in the process...

The Referenced action is...

As I said I think the solution is simple but thank you for your help in advance.

John H

[John_666]

Note that the Action works when triggered manually by clicking the icon on the form.

[Suvrutt_Gurjar]

Could you confirm if the table "Job Board" is parent table of the table "Xero"

If so, does the second action "Set[Inv Status] reference the child table rows of the Xero table that are children of the parent table "Job Board"s same record where the first action changes the [Colour] column?  

[John_666]

Hi Suvrutt,

"Job Board" is the parent of "Xero". If a Job row is deleted in Job Board then the related Xero rows with the ref [Job ID] matchig the Job Board[ID] are also deleted.

Secondly per your question, the second item in the process to set the  [Inv Status] to "Invoivced" is a text column in the child "Xero" table.  The first item in the process to change the [Colour] to "green" is a colour column in the "Job Board" parent table.

[Suvrutt_Gurjar]

Thank you for the details. Assuming you wish to change the status of children records of the same parent record where the first bot changes the parent record's [Colour] column to "Green", you could try the Grouped action option in the bot.

You could create the data change action "Change Colour to Green" and reference action to change the related children of "Xero" table to "Invoiced" status. Then you could group these two actions and use in the bot as grouped action option. My testing showed that grouped action option executes both the actions.

Grouped action in actions pane:

[Suvrutt_Gurjar · Accepted solution]

Thank you. You could use grouped actions in the bot. Testing showed grouped action executes.

In the example below "Orders" is the parent table and "Order Details" is the child table.

The grouped action

Set value to Green on parent table

Reference action on parent to select child table rows for setting invoice status:

Referenced action on child to set invoice status:

Grouped action included in bot:

[John_666]

Suvrutt,

To clarify, when you say "and reference action to change the related children of "Xero" table to "Invoiced" status." Is the action created in the Xero table or the Job Board table?   Can you group actions from different tables?  Or do you mean to create an action for the Job Board table that somehow references the Xero rows and columns I wish to set to "invoiced"?

[Suvrutt_Gurjar]

I have detailed the reference action in the screenshots above. Reference actions always act in pairs. The first action created on parent table lists the key of the rows of the child table that need to change. In the example, the following is the first reference action on parent. As you can see, it lists the records of the child table to act on and also mentions what action to execute in the child table. It also mentions the parent table and referenced table or the child table

Then following is the second action on the child table to change the child table column as desired. 

While grouping you need to group the actions from the same table. However in the case of reference actions, the initiating or master action of the reference action is on the parent table. In grouping you need not mention the second action of the reference action ,because it automatically gets executed when the action on master action is executed. Consider the pair of reference actions as tightly coupled and only master action initiates both the actions-on parent and child tables.

[John_666]

Thank you for your patience.  

I cannot select the child reference action from the list (see image below) in the Parent ref action?  I have saved etc.  The child  action is called Set [Inv Status].

[Suvrutt_Gurjar]

Your referenced table setting needs to be "Xero" and not "Job Board" 🙂

The examples provided show this needs to be child table. 🙂

[John_666]

Yes! My oversight.

This is more complex than I thought.  Thanks for working with me.

I have checked the structure and while the [Colour] changes to green, I can't get the child table to update with [Inv Status] = "Invoivced".

I have noticed the group action ihn Job Board is not being called by the Bot so I tried that and an error came up that two group actions can't be called in one event.  Makes sense.

What can I try next?  I am stuck.

[Suvrutt_Gurjar]

Are both actions included in the group action? Colour and Master reference action?

And is the group action being called in the bot as sample screenshots l shared?

Actually,  the bot configuration is relatively simple and I shared the screenshots after testing at my end that it works. Request you to carefully again verify if you are not missing some configuration or setting.

[John_666]

The test of the referenced rows [Related Xeros] is returning the Xero[Inv ID] list values as expected, but the Set [Inv Status] action is not being applied?

[Suvrutt_Gurjar]

Could you share relevant screenshots of the actions and bot?

[John_666]

Here they are

[Suvrutt_Gurjar]

What kind of action "Set [Invoice Status] to Invoiced "is?

[John_666]

Data: set the value of some of the columns in this row.

[Suvrutt_Gurjar]

It needs to be " execute an action on a set of rows" type. That is master action of reference actions pair.

[John_666]

Set [Inv Status] was my version of your earlier post...

This is the child action?  

[Suvrutt_Gurjar]

Oh okay , it needs to be the following action ( master action) included in the group action.

[John_666]

Sorry Suvrutt, my mistake,

This is my master ref.

[Suvrutt_Gurjar]

Oh okay, then it sounds good to go. Could you update what error and where are you getting it?

[John_666]

I am not getting any errors.  It's that the value "invoiced" is not being inserted into the table "Xero" in the column [Inv Status].  The Job Board[Colour] is being applied just fine on the change.

[John_666]

I might call it a night.  Thank you for your patience once again.  I will at it with fresh eyes tomorrow.  If you have any other ideas let me know.

[Suvrutt_Gurjar]

You can see how the bot is executing in "Audit History".  The following shows in my testing. The step 3 updates parent table "Orders" with Green status and step 4  updates 3 records of the child table "Order Details"

[Suvrutt_Gurjar]

One thing you may want to mention is if these child table rows are existing rows or are you adding those new along with the new parent record?

[John_666]

The child rows in Xero are invoice line items associated with the Job so they are mostly added after the Job has been completed.  They can be also updated or new ones added.  This is  the reason why I need the [Inv Status] flag to change when the Job has been finalised and the invoice items sent to Xero accounting via an API.

Below is the audit log showing the error.  Not that helpful to me at least. Again the [Colour] is changing, not the [Inv Status] flag.

[John_666]

This is the error message...

"Errors": "Error: 'Execute a Sequence of Actions' Grouped action 'Grouped Colour and Invoice status' child action 'Set [Inv Status] to \"Invoiced\"' failed due to error: Error: Perform DataAction 'Set [Inv Status]' failed because Row having key '284514da' in table 'Xero' in field 'Reference' is required to have a value \nError: Perform DataAction 'Set [Inv Status]' failed because Row having key '5dfb9c16' in table 'Xero' in field 'Reference' is required to have a value",

[John_666]

Suvrutt,

The problem was that a required column was no populated in the Xero table and so it threw out an error.  I changed deselected the required box in the column and your solution work well!

Thank you again for your help.

[Suvrutt_Gurjar]

Thank you for the update. It will help anyone looking for a similar solution and revisiting this thread. A confirmation that it works will help a future reader.

Good to know it works now for you. I would still suggest to watch out the bot behaviour when you are adding the new parent and child records together,