how to select the topping, the topping flavour and the quantity in a single column?

AppSheet AppSheet Q&A
, , ,
1

Oooh lords of appsheet! please listen the pray of this humble noob!

  1. I have a table where I record all orders from my customers at my ice cream parlor. The order types have a standard format with standard flavors, which makes them easy to handle. However, I am having a problem with a specific column, the “Toppings” column.

In this column, I want to be able to indicate which toppings were included, along with their respective specific flavors and quantities, so that in a second column, called “Total Toppings,” I can add up the price of each one multiplied by its quantity, taking into account the types and values.

Apart from this, I have two more tables: 2. The “Flavors” table, where I have:

unique_id column | product column | flavor column

In the first column, a unique id, in the second the product (both standard and toppings), and in the third, the flavors of each one. Therefore, there is repetition of the same product in consecutive rows for each different flavor.

  1. And a second table, where I have:

    unique_id column | category column | product column | value column

In the first column, also a unique id, in the second the category to which it belongs, “topping,” in the third, the products of the various categories (standard and toppings), and in the fourth, the value of each one.

Therefore, with regard to the column in that first table, we have the products from the second table, filtered by the category column where the term “topping” appears. From there, we have to indicate the quantities of each topping, if any, with their respective flavors, referring to the flavor column of the third table, filtered by the product column of that same second table according to the selected toppings. Finally, in the second column of that first table, we calculate the result of multiplying the quantities of each topping by their respective values in the third table.

It’s an odyssey, ladies and gentlemen.

I count on your infinite wisdom. Greetings…

2

If I read you correctly, this is one thing you could try..

  1. Create a child table (call it, say, Toppings) to the Orders table to record related toppings

[uniq id], [ref to the Orders table](check “is a part of” option), [ref to the Flavors table], [Quantity], [VC: Topping Price]

Here, [VC: Topping Price]'s expression is [ref to the Flavors table].[product column].[value] * [Quantity]

, assuming the [product column] in the Flavors table references the third table (your Products table) .

  1. Create a VC in the Orders table which totals the topping prices of an order with an expression SUM([Related Toppings][VC: Topping Price])

Hope this is what you are looking for.

1 Like