AnsweredAssumed Answered

Getting related records using of the List function

Question asked by NaturSalus on Aug 12, 2015
Latest reply on Aug 12, 2015 by philmodjunk

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

Outcomes