Solved: create action - Page 2

juan_1963 · 09-30-2023 02:18 AM

Good morning
I am looking for a way so that when filling out a form the data is copied into two tables, in table A I copy all the fields, and in table B I copy only 3 fields: id table A, name of the Product and the quantity, this is how To do so, what I'm really looking for is that only unique fields remain in table B.
If in table b there are two matching values IdTable_A and the name of the product, do not add them, but add the amount of the product that is spent.

Buenos días
Busco la manera de que al rellenar un formulario se copien los datos en dos tablas en la tabla A me copie todos los campos, y en el la tabla B me copie solo 3 campos id tabla A, nombre del Producto y la cantidad esto se como hacerlo, lo que realmente busco es que en la tabla B solo quden campos unicos.
Si en la tabla b existe dos valores coincidentes IdTabla_A adeMas el nombre del producto no los añada, pero si que me sume la cantidad del producto que se gasta

Julio35

I would suggest the following solution:

Tables

Create a table "Product" with following columns:

ID product
Product name
...

Create a table "Purchase" (or whatever is best for you) with the following columns:

ID purchase
Product (Ref to product table)
Quantity

Create a table "Product quantity" with the following columns:

ID product
Product name
Quantity total

Automations

When a new row is added (then you should also handle updates and deletes i guess) in the Product table then copy the ID and the Product name in the Product quantity table, so:

Trigger: Row is added to the Product table

Process: Run a data action -> Add new rows (to Product quantity table)

ID product (Product quantity table) = [_thisrow].[ID product] (Product table)
Product name (Product quantity table) = [_thisrow].[Product name] (Product table)

As you see, "quantity total" is missing. Assuming you are using Google Sheets, you can add this formula in your sheet to calculate the total quantity:

sumif('Product Quantity'!B:B, C2; 'Product Quantity'!C:C)

Here i am just assuming that you have a Product quantity sheet and that the Column B is Product (Ref) and column C is Quantity.

I am assuming in B2 you have the quantity total column

Explanation

You have a list of products (Product table) and when you add an item to this list it will be copied to the Product quantity table.

When you add an item, with its quantity, to the Purchase table, the quantity field in the Product quantity table is automatically updated.

View solution in original post