2 Replies Latest reply on Jun 7, 2017 4:17 PM by mikewolf1127

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

    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?