Re: Update column of a table when deleting records...

juliocock · 08-13-2022 11:43 AM

I have a query as I would in the following: I have 3 tables, a Sales table,
another sales_detail and finally table_inventories, the sales table is related to the sales_detail by the id sales,
and the sales_details is related to inventories by the product field.

I have created an action (updatestock) that when I make a change in the table (detail_sales) takes the quantity
of the product from the detail_sale table and updates the stock of that product in the inventory table. so far so good...

Now, when I delete a sale, it deletes all the products sold from the Sale_detail table because of the relationship
they have, but what I need is that it also update the stock of the inventory table because when I delete the sale,
the stock of all those products returns to the state as they were before the sale.....hopefully you can help me

WillowMobileSys

There is no automatic feature in AppSheet that will adjust your Inventory table for you. You will need to build that function.

The easiest way, I believe, is to use Automation in combination with the INPUT() function. Create a Bot that is triggered upon Deletes only of your "sales_detail" table. That Bot calls a "sales_detail" action of type "execute an action on a set of rows" that will pass your quantity through the INPUT() function to an Inventory action that physically updates the Inventory row.

To read more on how to use the INPUT() function, please refer to this article:

https://support.google.com/appsheet/answer/11623804?hl=en

Don't be afraid to come back and ask questions. It took me a bit to figure out how to use the INPUT() function but it's worth learning. This problem would have been MUCH harder to solve before INPUT() was available.

juliocock

Dear Thank you for replying

I already had the bot before and it works when any change is deleted or made in the Sale_detail table,
in fact it works well because it updates the stock in the inventory table perfectly

But when when I delete the "father" table (Sales), and all the records of the "daughter" table (Sales_Detail)
are deleted, that form of elimination does not call boot

calls it as long as records are removed from table SALES_DETAIL one by one

That is why deleting the SALES table and its referenced records from the SALES_DETAIL table
does not update the product stocks as they were prior to this sale.

WillowMobileSys

@juliocock wrote:
Dear Thank you for replying
I already had the bot before and it works when any change is deleted or made in the Sale_detail table,
in fact it works well because it updates the stock in the inventory table perfectly

Ok, so you already have an action created that is called by a Bot to update the Inventory and you have the Bot set to trigger on "All Changes" since it does work when you Delete a single sales_detail record.

As you point out, when you Delete the "father" record, all its children are also deleted. The question is then should we expect any Bots to trigger for those child rows when they are deleted in a cascading fashion?

I agree with you that we should expect it to work but I must say I have never needed to use a Bot in that manner so I don't know if the expectation holds true. Hopefully, someone else has some experience with that and can answer. In the meantime, I'll try to set up a Bot to test this out.

I wonder if you could include an image of how you have the sales_detail Bot configured? That may help a lot.

WillowMobileSys

By the way, have you tried checking the Automation Monitor for any reported errors?

WillowMobileSys

I created a little tester app and found the same results you have. When I deleted the Parent, both Parent and Children were physically deleted but my Child Bot was never triggered.

I tried turning on the the "Trigger Other Bots" setting - Did not work

I tried Deleting the children inside of the Parent Bot (even though they were being automatically Deleted) - Did not work.

I believe this to be a bug. Cascading Child deletes should trigger Bots as if you had individually deleted them.

The only work around I can think of is something I have used in the past. Use a column on the Parent named [Delete?] to mark the row for deletion and then allow the Bot to handle the deleting of the rows - first physically deleting the children to trigger the Child Bot and then Deleting the Parent.

juliocock

Thanks for giving you the trouble to find out, it's true, it's very rare, surely it must be a mistake

juliocock

I am including the images

Action1

action 2

Boot

Steve

@WillowMobileSys wrote:
I believe this to be a bug. Cascading Child deletes should trigger Bots as if you had individually deleted them.

I seem to recall that the Is a part of?-driven deletes do not trigger Automation.

Fabian_Weller

@Steve wrote:

I seem to recall that the Is a part of?-driven deletes do not trigger Automation.

That's true.

https://www.googlecloudcommunity.com/gc/Feature-Ideas/Include-IsPartOf-child-row-deletions-in-automa...

Update column of a table when deleting records from 2 other tables