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-
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-
Example Ingredient Data-
|Turkey Chili||Ground Turkey|
|Turkey Chili||Kidney 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?