Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Consolidate portal data


nhjelmberg

Recommended Posts

I have a product database. Every product consists of x number of recipes and ingredients. Every recipe consists of x number of ingredients. For each product I would like to find out the total amount of ingredients used. So far I have a layout with one portal showing the amount of ingredients that were added to the product and one portal showing the amount of ingredients that are part of the recipes that were added to the product. Are you with me so far?

 

Which is the best way to find out the total amount of ingredients used for a product? I could build a complicated script to copy and summarize the data this but it feels like there is a simpler and more elegant solution that I'm missing.

Link to comment
Share on other sites

You can Count() the ingredients or Sum() something about the related ingredients (it's not clear which you are trying to do here.) Either way, add a calc field to the product table that performs the aggregate function on the related field:

 

Count of Ingredients (calculation, number result) = Count(Ingredient::RecordID)

 

or

 

Total of Calories (calculation, number result) = Sum(Ingredient::Calories)

Link to comment
Share on other sites

Thank you. Actually, I need both sum of ingredients and sum of calories. For sum of calories I can use a SUM function that summarizes calories per product. However, for sum of ingredients I need a sum per ingredient used in the product.

 

For example Product Vegetable Soup needs 1 unit of Water and 1 unit of Vegetable Mix. Vegetable Mix needs 0.4 units of Carrots, 0,4 units of Potatoes and 0,2 units of Water. Thus, Product Vegetable Soup needs 1,2 units of Water, 0.4 units of Carrots and 0.4 units of Potatoes. (Not a very exciting soup but it's just an example.)

 

The problem is that the ingredients come from two portals of different levels, one showing the ingredients connected to the product and one showing the ingredients connected to the recipe connected to the product. My current solution exports the data from the portals and then re-import it to another table, where I perform the necessary calculations. The user will never notice this process but it's a dirty solution that I'm not very proud of.

Link to comment
Share on other sites

I see what you're trying to do now.

 

Since portals don't automatically summarize like this, we'll need to use another technique to filter the relationship down to one of each.

 

Check out the attached demo. It uses a script to set a multi-key and several relational hops to filter out duplicates, showing only one portal line for each. Then another relationship is used to show the Sum() of the Recipes the Ingedient is used in (note the base table for that Sum() calc.)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use