Summary suggestions on food order app

[Onceokc]

Summary suggestions on food order app

Looking for suggestions on how to show summary data from my food ordering app.

3 tables
Product
(columns: ProductID, name, calories, fat grams, sugar grams, protein grams)
Order
(columns: Order ID, client name, time, date)
Order details
(ID, OrderID[referenced], ProductID[referenced], Quantity )

The ordering system will allow the client to order the same items within the order, creating multiple separate lines with the same product.

Id like to create a summary slice view that tallies up the total calories, fat grams, sugar grams, protein grams for each product in an order.

I think I need to create virtual columns with Sum equations.

Not sure how to write an equation that will sum the data per product per order (biggest HELP point).

Any suggestions or links to similar questions would be appreciated.

[Suvrutt_Gurjar]

Onceokc:

Not sure how to write an equation that will sum the data per product per order (biggest HELP point).

Since the individual parameters related to calories, sugar grams etc. in the Product table, you may wish to take a look at a very similarly configured sample app referred below.

You may wish to apply the same principle that calculates total price for an order based on individual products.

There are two ways the total price is calculated in the sample app using VC ( Column [Order Total]) and using spreadsheet formula ( Column [Total Cost]) in the Orders table.

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=83b...

[Onceokc]

I would like to thank you for replying back to my message so promptly. The sample app you provided would help in the situation I stated.

That said…
I will modify my order details table to include columns for calories, fat, sugar and protein to that table. Data will be pulled from the product table.

My goal is to create a summary slice, from the order details table that will extract the sum of the of the calories, fat, , sugar and protein information per product per order. Any suggestions on how to setup an equation in a vc to achieve this aim?

[Suvrutt_Gurjar]

Onceokc:

My goal is to create a summary slice, from the order details table that will extract the sum of the of the calories, fat, , sugar and protein information per product per order

You are welcome. I think the above statement contradicts a bit itself. You may not have a summary per product per order. You may have a summary per order for all products in that order added.

Per product per order means simply create a view of order details per order which anyway should be available in inline Order details view.

Could you please elaborate what you wish to do?

[Onceokc]

I reviewed the order detail table. There are no duplicate products in one order. When I add a duplicate product to an order and review the orders details, it shows each order detail entry separately.

I would like to find a way to combine duplicate products within the same order and sum up the values in associated columns as well.
Do you have suggestions on a way to achieve that goal?

[Suvrutt_Gurjar · Accepted solution]

Onceokc:

I would like to find a way to combine duplicate products within the same order and sum up the values in associated columns as well.

Yes, that can be done. The below expression is with respect to the sample app. Please create a VC in the “Order Details” table called say Total Prices Per Product with an expression such as

SUM(SELECT(Order Details[Total],AND([Product ID]=[_THISROW].[Product ID], [Order ID]=[_THISROW].[Order ID])))

Here [Total] is the Price per product in the “Order Details” table. You could similarly try a spreadsheet formula as well. This expression principle you could use for Calories etc. as well.

Please however note that all these expressions are going to be sync time expensive since these are multirow expressions.

[Onceokc]

The equation you provided works for both scenarios. I will create a slice of just the VCs.

Do you have a suggestion on how to remove duplicate data? I would like to create a slice view that doesn’t have multiple lines with the same information.

[Suvrutt_Gurjar]

In general, the expression for the slice filter could be

[OrderDetail ID]=MINROW(“Order Details”,"_ROWNUMBER", [Order ID]=[_THISROW].[Order ID])

This will select only the first row from the “Order Details” table for one Order ID

help.appsheet.com

MINROW()

Row with lowest value in column

Edit: A change to the expression.

[Onceokc]

Thank you for the help.