Formula to summarise the information from another table in a text

[DexCaesar]

Hi,

I am looking for a formula to summarise the information from another table in a text.

Here is an example of what I am looking for :

————————
Purchases table
————————

Product	Quantity
Product_1	10
Product_2	10
Product_1	20
Product_3	10
Product_2	20

————————
Purchase_Summarize table
————————

Purchase_ID	Purchase_Text
123423	“Hello, I would like to order : <br /> Product_1 30pc<br /> Product_2 30pc <br /> Product_3 10pc”

————————

I try to write a Formula for “Purchase_text” (being a sumup without duplicates)

Thank you in advance for all the help you can give me. 🙂

[Suvrutt_Gurjar]

Are the two tables related by referencing relationship?

References between tables - AppSheet Help

In fact the example given in the help article are exactly as per your app set up. Purchases table is equivalent of "Order Details" and "Purchase_Summarize table" table is equivalent of "Orders" table.

Referencing bewtween tables will make exchanging data between the tables much easier.

[DexCaesar]

Hello,

Yes, in the Purchase Table, there is a reference to the Purchase_Summarize table

[Suvrutt_Gurjar]

Great. 

So do you have a virtual column titled something like [Related Purchases] in the "Purchase_Summarize" table?

[DexCaesar]

Yes

[Suvrutt_Gurjar]

Great, thank you.

Please create a VC in the "Purchases" table called say [Product_Quantity] with an expression  something like 

CONCATENATE([Product], "-", [Quantity])

Then in the Purchase Purchase_Summarize table add a VC of type long text called something like [Purchase_Text] with an expression something like 

SUBSTITUTE(LIST("Hi I would like to order: ")+[Related Purchases][Product_Quantity], ",", "
")

[DexCaesar]

Thank you very much for the help. Much appreciated.

The last problem is that it does it still has duplicates. There is one line per related product and it does not sum up when I tested it.

[Quote]
Hello, I would like to order:
Yogurt-400
Granola-50
Red fruits-50
Yogurt-9.6
Granola-1.2
Red fruits-1.2
[/Quote]

[Suvrutt_Gurjar]

---

@DexCaesar wrote:

The last problem is that it does it still has duplicates.

---

Oh yes, sorry,  I missed that bit as it was not very obvious in your post. To achieve the multi lines for same product in the same order and then to add it before summation can be a bit complex to achieve, even though of course it is achievable. But it may need some extensive actions to be added to achieve the needful or sync time expensive virtual columns

As easier alternative option will be to prevent duplicates products to be ordered in the same main order.

So in the [Product] column in the Purchases table you could add a valid if expression of 

ISBLANK(
FILTER(
"Purchases",
AND ([_THIS] = [Product], [Purchase_Id]=[_THISROW].[Purchase_Id])
)
- LIST([_THISROW])
)

This will prevent the user from adding the same item twice in the same [Purchase_ID]

You can add a valid_if error message such as "You have already ordered this product earlier in this order. Please change the quantity there if you want to purchase more quantity of this product"

[DexCaesar]

The problem is that the Purchase table is not manually but automatically filled: (We are an F&B company) the client orders different recipes, and those recipes have ingredients. The ingredients are added via a BOT in the Purchase table.

We always have multiple duplicates.

[Suvrutt_Gurjar · Accepted solution]

Okay, I will mention a simpler to implement but sync time expensive way. You could achieve the same with implementing reference actions as well that will not be sync time expensive.

Please add a column called say [Total_Product_Quantity] in the Purchases table with an expression something like 

SUM(SELECT(Purchases[Quantity],AND([Purchase_Id]=[_THISROW].[Purrchase_ID], [Product]=[_THISROW].[Product])))

Please note that expressions such as above can take a lot of app sync time as the table size increases. You can of course have some configurations such as using security filters, slices to reduce the sync time but that is a larger subject.

Now please revise the expression for the [Product_Quantity] column as 

CONCATENATE([Product], "-", [Total_Product_Quantity])

Please revise the expression for the [Purchase_text] column as 

SUBSTITUTE(UNIQUE(LIST("Hi I would like to order: ")+[Related Purchases][Product_Quantity]), ",", "
")

0 Likes

[Suvrutt_Gurjar · Accepted solution]

SUBSTITUTE(UNIQUE(LIST("Hi I would like to order: ")+[Related Purchases][Product_Quantity]), ",", "
")

[DexCaesar]

It is working great. Thank you very much 😃👍