3 Replies Latest reply on Aug 12, 2015 8:38 AM by philmodjunk

    Getting related records using of the List function

    NaturSalus

      Title

      Getting related records using of the List function

      Post

      Using the right relationship between the Dish and Diet tables I want to use the List function to extract by groups (1-Dessert, 2-Lunch, 3-Dessert) the different related Dish records and put them in different fields (Diet::Dessert, Diet::Lunch, Diet::Dinner) so that later on I can put together a diet divided in the following parts: Dessert, Lunch and Dinner.

       

       

      HOW IT WORKS

       

      1. Through scripting I associate the different Dish records to the current Diet record and they are listed in a portal to the Diet|Dish TO that has two fields:

       

             
      •      

        Dish::DishName

             
      •      
      •      

        Diet|Dish::DietPart

             

       

      The name of the dish (Dish::DishName) is added through scripting.

       

      The Diet|Dish::DietPart field is filled by the user and is used to put each dish into one of the following three categories:

       

             
      •      

        1-Dessert

             
      •      
      •      

        2-Lunch

             
      •      
      •      

        3-Dinner

             

       

      2. I have set up a relationship between Dish, Diet|Dish and Diet tables so that only the Dish record with Diet|Dish::DietPart = “1-Dessert” is considered.

       

      3. I have set up the Diet::Dessert field with the List function, based on the relationship mentioned at point 2, to list only the related Dish records that belong to the dessert category.

       

       

       

      SETTINGS

       

      These are my settings for listing the dishes of the dessert in the Diet::Dessert field:

       

      Tables

       

             
      •      

        Dish

             
      •      
      •      

        Diet

             
      •      
      •      

        Diet|Dish

             

       

      TOs

       

             
      •      

        Dish TO to the Dish table

             
      •      
      •      

        Diet|Dish TO to the Diet|Dish table

             
      •      
      •      

        Diet TO to the Diet table

             
      •      
      •      

        Diet|DishForDessert TO to the Diet|Dish table

             
      •      
      •      

        DishForDessert TO to the Dish table

             

       

       

      Fields

       

             
      •      

        Dish::__kp_Dish (number)

             
      •      
      •      

        Dish::DishName (text)

             

       

             
      •      

        Diet|Dish::__kp_DietDish (number)

             
      •      
      •      

        Diet|Dish::_kf_Dish (number)

             
      •      
      •      

        Diet|Dish::_kf_Diet (number)

             
      •      
      •      

        Diet|Dish::DietPart (text)

             
      •      
      •      

        Diet|Dish::DishName(text) Lookup from Diet|Dish to Dish

             

       

       

             
      •      

        Diet::__kp_Diet (number)

             
      •      
      •      

        Diet::DietName (text)

             
      •      
      •      

        Diet::gDessert (text; global; calculated value, gDessert = “1-Dessert”, calculation result must be Text)

             
      •      
      •      

        Diet::Dessert (calculation)

             

       

      Diet::Dessert (calculation):

      Substitute (

      List (Diet|DishForDessert::DishName ); ¶ ; "¶"

      )

       

      Calculation resut is: Text

       

       

      Relationships

       

      DishForDessert --< Diet|DishForDessert >-- Diet

       

       

      DishForDessert::__kp_Dish = Diet|DishForDessert::_kf_Dish

       

       

      Diet|DishForDessert::_kf_Diet =Diet::__kp_Diet

      AND

      Diet|DishForDessert::DietPart = Diet::gDessert

      Sort records by DishName

       

       

       

      With the explained setting I don't get any records in the Diet::Dessert field what am I doing wrong?

       

      Thanks

        • 1. Re: Getting related records using of the List function
          philmodjunk

          If you put a portal to Diet|DishForDessert on your Diet layout and include the Diet|DishForDessert::DishName, do you see any dish names? If you include the DishForDessert::Dishname in this same portal, do you see dish names in it?

          Do you see any records at all in this portal to the join table?

          Investigating those results will likely point you at the source of your trouble--which could be missing records in the join table, data that did not look up correctly or match fields that do not match due to wrong data or wrong data types. Note that your list function should be able to refer to DishForDessert::Dishname directly and not need to refer to the join table record's field that has to copy (look up) this data from another source.

          • 2. Re: Getting related records using of the List function
            NaturSalus

            Hello Phil,

            Many thanks for looking into my question.

            If you put a portal to Diet|DishForDessert on your Diet layout and include the Diet|DishForDessert::DishName, do you see any dish names?

            yes

            If you include the DishForDessert::Dishname in this same portal, do you see dish names in it?

             

            yes

             

            Do you see any records at all in this portal to the join table?

            yes

             

            Investigating those results will likely point you at the source of your trouble--which could be missing records in the join table, data that did not look up correctly or match fields that do not match due to wrong data or wrong data types.

            I was trying to get the desired results in Diet::Dessert, Diet::Lunch, and Diet::Dinner fields, based on the List function, after the relationships between the Dish and Diet tables have already been created through scripting, but they weren't getting any value.

            I guess, first I had to create all the fields (Diet::DessertDiet::Lunch, and Diet::Dinner), and then create a Diet record to see if it worked.

            Note that your list function should be able to refer to DishForDessert::Dishname directly and not need to refer to the join table record's field that has to copy (look up) this data from another source.

            You are right again, the List function gets the right values directly form the DishForDesert::Dishname field.

             

            Many thanks, one more time you solve my "problem".

             

             

            P.S. I wonder whether FMI is aware of your contribution to this forum and if they are generous enough to compensate you somehow.

             

             

             

             

             

            • 3. Re: Getting related records using of the List function
              philmodjunk

              Aware, yes, compensation no.

              This is one (a smaller) reason why I started up the Adventures In FileMaking series.

              Caulkins Consulting, Home of Adventures In FileMaking