SUM dereference value is different to the SUM in the original table

[mb945879]

I have a product database.

PRODUCTS [productID] ref to many ORDER_DETAILS table [detailsID] ref to an ORDER table [orderID].

This all works OK. So I added a RECIPE table and RECIPE_DETAILS table.

In the RECIPE_DETAILS table a Virtual Column RECIPE_DETAILS [Ingredient Cost] which calculates the price of the ingredient base on the the quantity specified (a recipe detail) and the most recent price paid (product property) etc etc (all works OK) but has and IFS statement of : IFS(AND([recipeUnit]="pc",[productID].[productSplit]>0),([productID].[Products Most Recent Price]/[productID].[productSplit])*[recipeQty],
TRUE,([Ingredient Volume]/[Product Volume])*[productID].[Products Most Recent Price]
)

In the RECIPE table there is a Virtual Column [Recipe Cost] = SUM(SELECT(Recipe_Details[Ingredient Cost], [recipeID] = [_THISROW].[recipeID])). This adds up the total ingredient cost and gives the correct SUM.

For example: lets say Apple Butter = $2.65

OK to now and the totals have been manually worked out and checked to be correct.

Some of the products in the PRODUCTS table are of [productType]="Homemade".  So in the PRODUCTS table there is a Ref to the RECIPE table [recipeID]. I put this here so I could "dynamically" work out the cost of these homemade products and enter the cost in the PRODUCT table [productPrice] column from the RECIPE [Recipe Cost] column.

Problem:

Any dereference back to the RECIPE table [recipeID].[Recipe Cost] from the PRODUCTS table the value I get is not correct anymore. Lets say Apple Butter now = $2.05.

This is the same for all values and it seems some aspect (ingredient costs) of the [recipeID].[Recipe Cost] is being lost?

Any thoughts??

[WillowMobileSys]

Manually check your Recipe_Details table by temporarily showing a table view with the {recipeID] and [Ingredient Cost] columns.  Each time you check the [Recipe Cost]  confirm the rows being shown in Recipe-Details for the [RecipeID].

I have to think that either a row is switching RecipeID's on you, getting blanked out OR there is some filtering removing the rows.

[mb945879]

Thanks I will try this.

[mb945879]

Thank you at least I have somewhere to start looking now.

I created a new ERROR Table in the source data with columns [errorID] [productID] [recipeID] with ref to relevant fields. Correct price shows in this table so there must be a filter or something somewhere, bit more digging required 🙂

[mb945879 · Accepted solution]

Oh dear, in my naivety of using AppSheet I had not realised the TEST function produces a list of values on which a SUM calculation would be performed.. So there are no issues as I was just seeing the first value in a list of values which I had assumed to be the result of SUM!! Phew!