Today we were given a data set which had food nutrient information along with Food Details, Recipe information and measure for each food item.
My approach was to start with Recipe data and figure out the nutrient information and calorie details for each of the recipes. The problem was that the file had data per 100 gm of recipe and there was ingredient weight /per recipe . I wanted a dynamic way to select the portion size for each of the recipe/Ingredient selected. I wanted to give the users option to select more than one recipe at a time.
My first thought was to use Set actions. However figuring out how to store the portion sizes for each recipe ingredient dynamically was difficult. I created a separate excel with portion sizes(1-10) alone and cross joining with the recipe data. So for each recipe , I have a row with predefined portion .
Then I created a concatenated field [Recipe ingredient]+[Portion] and used this field to create Set . Below is a snap shot of how users can select the portion. So when user selects Apple and portion size 2,with the help of set actions, the value that get store in set will be ” Apple,golden delicious,unpeeled,raw2″. Clearing the selection should “Keep all value” in the set
Used the set in filter and created calculated field weight_portion as [Portion]*[Ingredient Weight (g)].
This solved the problem of dynamically assigning portions to ingredient/recipe.
In order to reset the values of set , I created a dummy field as ” Reset”. Drag it on to a new sheet. “Reset” will repeat for the number of ingredient selected in your set.
Adjust the size of field so that only one “Reset” word is visible. Create a set action to add values to set on clicking “Reset” and Remove all values from set on clearing the selection.
This will remove all values from Set and you can start selecting new recipe /portions again.