Sorting text based on values

[[email protected]]

Hi

 

I have a problem with sorting. I need this to properly create food labels.

 

A certain product (e.g. chicken sandwich) contains several ingredients (Chicken, Butter, Tomato, Wheat, etc.). These ingredients have to appear on the food label in descending order based on their weight.

 

Amongst others, I have a table called "Recipe" with the fields "Product Name", "Ingredient", "Amount in g". Here, I add all the ingredients of a certain product together with the corresponding amount (in grams) of each ingredient. There is also another field in this table called "Ingredient_Label_List_S", which is a statistics field that sorts all the ingredients of a product (e.g. chicken sandwich) in descending order based on the field "amoung in g".

 

So far so good...however, i need the information "Ingredient_Label_List_S" in another table called "Products". In the table "Products" I have a field where I enter the formula "=Recipe::Ingredient_Label_List_S". The problem is, that in this field the ingredients are no sorted anymore. 

 

How can I solve this issue?

 

Thanks

Sam

[comment]

I am having trouble following your description. How exactly is the field called "Ingredient_Label_List_S" defined - and why is it in the Recipe table? Some more background is needed here, esp. what do your tables represent (your naming seems to be a little off) and what are the relationships between them.

 

 

My guess is that if you sort the relationship between Products and Recipe on the Recipe side by "Amount in g", descending, then a calculation field in Products =

Substitute ( List ( Recipe::Ingredient ) ; ¶ ; ", " )

will get you the list you're looking for.

[Lee Smith]

Why don’t you attach a copy of your file. Just follow the steps found here.  Attach File

[eos]

Shouldn't this simply be a structure like

 

Recipes --< Ingredients >-- Products

 

where amount is an attribute of an ingredient join table record?

 

Then create a textual description in Ingredients (Product::name & ": " & amount & "g" = e.g. ”Chicken: 200g”);

sort the Ingredients TO via Products by amount, descending;

and use Substitute ( List ( Ingredients::cDescription ) ; ¶ ; ", " ) to generate a label list sorted by amount.