AnsweredAssumed Answered

I am trying to do two things at once that seem to be mutually exclusive-

Question asked by mikewolf1127 on Jun 7, 2017
Latest reply on Jun 7, 2017 by mikewolf1127

I am trying to do two things at once that seem to be mutually exclusive. In short I want to have a two field table relationship to pick the correct record, but ALSO use the second field as a conditional value list. But the relationship is precluding, and thus preventing, the full value list...

 

Here are my tables & fields-

 

RecipesFoodIngredient
RecipeFoodFood
ServingsF_UnitI_Unit
F_QtyI_Qty
F_CalI_Cal
Recipe

 

Recipes and Ingredients are linked via the 'Recipe' field.

Ingredients and Food are linked via the 'Food' field AND the 'Unit' field to get the correct nutritional info.

 

Example Food Data-

 

FoodF_UnitF_QtyF_Cal
Ground Turkeyoz8340
Ground Turkeylb1680
Kidney BeansC1200
Kidney Beanscan12350

 

Example Ingredient Data-

 

RecipeFoodI_UnitI_Cal
Turkey ChiliGround Turkey
Turkey ChiliKidney Beans

 

So when I am building a Recipe I have a portal showing the Ingredient table. I add in the Food name. I then want to limit the I_Unit to the F_Unit values available for that food in the Food table. I can do this by useing a conditional value list to limit the values of I_Unit for 'Ground Turkey' to 'oz' and 'lb', as well as limit the values of I_Unit for 'Kidney Beans' to 'C' and 'can'.

 

However, since previous to the conditional value list calculation there is only a single matched record due to the AND relationship between the Food and Ingredient tables, there is only one value in the conditional value list.

 

I somehow need to be able to FIRST have a conditional value list find the available values of the F_Unit field to offer up as possible values in the I_Unit field...

 

And THEN, once the F_Unit field is entered, find the correct match in the Food table based on both Food and F-Unit.

 

I hope that all makes sense.

 

Anyone have a solution?

Outcomes