How to use the action "Data: add a new row to another table using values from this row"

AppSheet AppSheet Q&A
,
1

I’m trying to create a simple app, I have 2 Table in the same Google Sheets file, “TableA” is a list of product, I don’t want to add, delete, or edit nothing from “TableA”. I want to select a product from “TableA” and add to “Table B”, this means select a row from “TableA” to add to “TableB”. But “TableB” has more columns than “TableA”, because it will work almost like a shopping list, so I need other column to organize it better.

TableA(product list): Column (Product_Code) ; Column (Product_Description) ; Column (Price)

TableB(Shopping list): Column (ID) ; Column (Label) ; Column (Product_Code) ; Column (Product_Description) ; Column(Price) ; Column (Amount) ; Column (Cost)

Column (ID) needs to be a formula, in Google Sheets it would be =sequence(counta(TableB[Product Code])), I don’t know how to do that on App Sheet

Column (Label) needs to be a Enum, I don’t know how to do this, the way I did it is Type = Enum, Base type = Text, and in Data Validity → Valid if = Table_Label[Label], yes a created a new Sheet in Google Sheet File and create the Table_Lael, with it a can organize the product in the shopping list like T-shirt ; Pants ; Shoes.

Column (Amount) I’m going to write the amount that I’m going to buy of that product.

Column (Cost) in google Sheets would be = TableB[Price]*TableB[Amoun], I don’t know how to do that on App Sheet

When I select the action “Data: add a new row to another table using values from this row”, the error “The data action ‘New Action’ does not define a value to set the column…” shows up to all the columns above, the column that is only on TableB.

I don’t know what I need to do in " Set these columns → To the Constant or expression"

2

Hi,
Both tables should have a column “ID” and Apsheet detects it and sets the formula UNIQUEID()
Every record in any database should have a unique identifier value (called a key column).

Table A: [id],[product_code].[product_description],[price]
Table B: [id],[product_id] (ref to Table A), [product_code],[product_description],[label],[amount],[price],[cost]

For the columns you need to manual input when the record in Table B is added you need to use INPUT(). This will show a form for the user to fill in the values.

In the action you set the columns:

[product_id]=[id]
[product_code]=[product_id].[product_code]
[product_description]=[product_id].[product_description]
[price]=[product_id].[price]
[label]=INPUT(“label”,“”)
[amount]=INPUT(“amount”,“”)
[cost]=[amount]*[price]

I hope it helps!

1 Like