4 Replies Latest reply on Aug 12, 2009 6:53 AM by petertoo

    Problem with calculation field to display multiple rows' data from join table

    petertoo

      Title

      Problem with calculation field to display multiple rows' data from join table

      Post

      <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:UseFELayout/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:新細明體;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-alt:PMingLiU;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} @font-face      {font-family:&quot;\@新細明體&quot;;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:新細明體;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->

      Hello,

      I am very new to Filemaker, below is the question I have, appricate if anyone can help. 

       

      I have three tables. Training (id, course name, course description,..), Register (id, training_id, employee_id) , and Employee(id, employee name,..). The tables relationship are as following:

       

      Training             Register        Employee

       

         id             =    Training_id

                              Employee_id =       id

       

      One training can be registered by many employees, and one employee can register many trainings.

       

      Now, in training layout, I created employee drop-down list in a portal for creating multiple data entries into register table. However, since drop-down list use Employee::id as value, the users can only see employee's id after making selection (employee name can be displayed with second field function enable in the drop-down list when making selection), which is not user friendly. To work around this, I would like to create a field to display all registered employees in a string format below the drop-down list portal. For examples, if users selected employee A, B and D in the portal (three rows), this field should display string value as “employee A, employee B, employee D”. I want to use a calculation filed to accomplish this but it seemed calculation field only able to display one value at a time. (calculation field) Training:cal_register_employee  = employee:: name.

       

      Can anyone give me a hint on this issue? Thank you in advance.

        • 1. Re: Problem with calculation field to display multiple rows' data from join table
          philmodjunk
            

          Why not put the employee's name in the portal?

           

          Your portal rows can look like this:

           

          [Emp ID] [Emp Name][Training ID].....

           

          You can place the employee name from your employee table in your register portal. You can also place fields from your training table here also. Your relationships will match up the correct values from your related tables for you when you select an Employee ID.

           

          Another option:

           

          Make your Employee ID drop down a pop-up menu instead of a drop down list. Set up your value list to hide the first (emp ID) column. Now, you'll see the employee's name displayed even though your are actually selecting and storing the employee number.

          • 2. Re: Problem with calculation field to display multiple rows' data from join table
            petertoo
               <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:UseFELayout/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:Wingdings;      panose-1:5 0 0 0 0 0 0 0 0 0;      mso-font-charset:2;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:0 268435456 0 0 -2147483648 0;} @font-face      {font-family:新細明體;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-alt:PMingLiU;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} @font-face      {font-family:&quot;\@新細明體&quot;;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:新細明體;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->

            Hello,

            Thank you for your help. Your second option (pop-up menu) works fine to resolve cannot display employee name issue after making employee selection. However, still want to know if a calculation field is feasible.

             

            Below is how my training layout (many fields from training table, some from register table such as employee_id) looks like. The purpose of this layout is to create data for training table and register table. Whenever user press “New Record” button in the browse mode, s/he will be able to create a new training info base on fields as below.

             

            ***************************************** top of training layout *******************************************

            *    [Training ID] --> auto generate

            *    [Course name] --> input by user

            *    [Course description] --> input by user

            *      ……

            *

            *    Portal goes here (set default portal number of rows to 3 and show vertical scroll bar. Currrent plan in each row to display employee name only)

            *    row #1 [Register:: Employee_id]  --> user can select employee from employee pop-up menu

            *    row #2 [Register:: Employee_id]  --> user can select employee from employee pop-up menu

            *    row #3 [Register:: Employee_id]  --> user can select employee from employee pop-up menu

            *    user can add as many rows as s/he wants since this is a portal

            *

            *    Here I want to have a text field (wonder if a calculation field can achieve this?) to list all employees' name whose employee_id are stored in register table for the current training_id that we want to create data.

            *    [cal_register_employee] --> calculation field to display all employees' name in a list

            *

            ***************************************** bottom of training layout **********************************************

             

            With changing drop down list to pop-up menu, I know user can now see the employee name after making a selection. And it might be duplicate to create a field to <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:UseFELayout/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:新細明體;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-alt:PMingLiU;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} @font-face      {font-family:&quot;\@新細明體&quot;;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:新細明體;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->concatenate <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:UseFELayout/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:新細明體;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-alt:PMingLiU;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} @font-face      {font-family:&quot;\@新細明體&quot;;      panose-1:2 2 3 0 0 0 0 0 0 0;      mso-font-charset:136;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 137232384 22 0 1048577 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:新細明體;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->all employees' name (who want to register for this training) into a list. However, this list might be needed in other portal with other layout.

             

            Appricate and thank you again for your time.

             

            Peter

             

             

            • 3. Re: Problem with calculation field to display multiple rows' data from join table
              comment_1
                 Try the List() function (version 8.5 and higher).
              • 4. Re: Problem with calculation field to display multiple rows' data from join table
                petertoo
                  

                Thank you! :)

                My probelm solved by ..

                [cal_register_employee] = Substitue(List(Employee::Employee name);"¶";",")

                 

                Peter