I can't make to update some basic ingredients from a compound preparation

[vramirez80]

Hi all, how are you?

I have an app where I can make sales and I can sales of products which are made of several basic products. All is fine when the parent product is added to the sale but the problem appears when I need to discount the basic products from the inventory..

I have three tables like follows

The table Productos which has a column for the original quantity in inventory of any product called Cantidad, one column fort the sold quantity called Cantidad vendida, one column for the spent quantity of basic ingredients based on the parent product and a column called Producto_compuesto which indicates if the product of that row is a parent product or a basic products (TRUE or FALSE).

I have a table called Orden_detalles which stores the quantity of any product added to a specific sale, that column doesn’t store the quantity of basic products added to the sale because I considered that the price of that basic product is in the price of the parent product.

I have a table called Receta, this table has the content of any compound product based on basic products and the quantity of basic product needed to make the parent product, the table has columns Producto_padre, Producto (the basic ingredients), Cantidad_requerida (the quantity needed of basic product to make the compound product) and a column called Suma_acumulada which is intended to store the quantity of a basic product used in any sale when a compound product is added.

I’ve been using Gemini to try to make it work but none of the suggestions or my ideas works in the intended way because of the following: I have several action all executed when the form for add a new element to the sale is saved like the following image

For some reason the sequence of the actions doesn’t update the column Suma_acumulada in Receta, then the column Cantidad vendida compuesta is filled with zeros instead of the sum of basic ingredients, so the basic products aren’t discounted from the inventory.

It is supposed that the third action in the grouped action calculates total punto of basic ingredientes spent in a compound product, then the last action in the grouped action copies those values to the column Cantidad vendida compuesta column of the Products table but when I first add a new compound product to the sale then this column is filled with zeros, I have to edit a compound product and then save it to allow those values to appear in the mentioned column.

What can I do to solve this?, thanks in advance for the help

[vramirez80]

note that I know that using spreadsheet formulas the program will work flawlessly but I want to trying to make the app to work on it own

[WillowMobileSys]

@vramirez80

For some reason the sequence of the actions doesn’t update the column Suma_acumulada in Receta

I would recommend using an App formula instead of using an action to update this value. Anytime the “Receta” row is edited, the App Formula will recalculate the value.

However, it should work with an action as well. Can you show us your definition for the “Suma_acumulada“ column? Maybe something in the column configuration is preventing it from being updated.

It would also be helpful to see what those first three actions action in the action group are doing.

NOTE: Your fourth action seems to be a navigation action to go to another view? Once a navigation action is executed, all actions AFTER it will be ignored. If you need to re-direct the user to a new view, make sure all other actions are performed first and set the navigation action as LAST.

[vramirez80]

Yes, but the action that is after the view action putted there only for testing, it does the same if is before the view action, anyway, in a moment I will put the configuration of Suma_acumulada

[vramirez80]

There it is the configuration of the Suma_acumulada column

[WillowMobileSys]

@vramirez80

Suma_acumulada

This doesn’t match with what was shown in the Receta table configuration above. In the table, the column has a data type of Decimal but in the new image, the column has a data tyope of Text. What has changed?

I wanted to see in the column configuration if there was anything that might make it not accept a value which would be in the collapsed areas. Is there anything in the Valid_If or the Editable_If properties?

[vramirez80]

You are right, I sippose that the page loaded i the cellphone in that moment was a cache or something, here is the correct config of Suma_acumulada

[WillowMobileSys]

Is there anything implemented in any of the collapsed sections? “Data Validity”, “Auto Compute” or “Update Behavior”

[vramirez80]

no, all the calculations are made through actions, following are the collapsed sections

the following is the action that supose to calculte the spent basic ingredients (the action you saw after the view action), I putted the action in the list before the view action again but it shows the same behavior, only acts if the specific order related to that compound product is edited after being added to the sale.

the row selection criteria for the action

NOTE: I assume that this is happening because appsheet hasn´t written the values to the spreadsheet yet, then this action reads zero and because of that it writes zeros in the column Suma acumulada compuesta, and becuse of that, it makes the correct calculation when the sale is edited, because the action in that moment sees the quantities of compound products written in the spreadsheet.

[vramirez80]

Finally I had to use spreadsheet formulas to calculate the real inventory but, which forced me to erase several actions which was distributed in several tables in my app, but right now is working.