0 Replies Latest reply on Aug 21, 2009 8:30 AM by petertoo

    Problem with filtering portal with drop down list



      Problem with filtering portal with drop down list


      <!--  [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:新細明體;} p      {mso-margin-top-alt:auto;      margin-right:0in;      mso-margin-bottom-alt:auto;      margin-left:0in;      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]  -->


      I am new to Filemaker. Below is the question I have. Appreciate if someone can help. (the content is quite long. thanks for your patient!).


      I have a layout called “Employee training”. Let's say this layout contains one portal and one drop down list (for example). Layout content look like as below (with demo data)


      >>>>>>>>>>>>>>>  "Employee training" layout top  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<


      -- Portal top --

                   [Training name][Training description][Registered employee]..

      Row 1->   Training A           Presentation skill       Employee A, Employee B

      Row 2->   Training B            Reporting skill          Employee B, Employee C

      -- Portal bottom --



      <drop down list>

      Option 1-> Show All

      Option 2 -> Employee A

      Option 3-> Employee B

      Option 4-> Employee C


      >>>>>>>>>>>>>>  "Employee training" layout bottom <<<<<<<<<<<<<<<<<<<<<<<<<<<<<


      Initially three tables created for this layout.

      Training (id, training name, training description,Registered employee,..).

      Register (id, training_id, employee_id)

      Employee (id, employee name,...).

      And 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.

      Tables may contain data look like this for our demo layout.



      [id][Training name][Training description]  [Registered employee] ...         

      1        Training A       Presentation skill    Employee A, Employee B      

      2        Training B       Reporting skill         Employee B, Employee C       




      1           1               1

      2           1               2

      3           2               2

      4           2               3



      [id][employee name]..

      1     Employee A

      2     Employee B

      3     Employee C


      Portal created base on a training self-join table. [Registered employee] is a calculation field in Training table. [Registered employee] = Substitue(List(Employee::Employee name);"¶";","). The purpose is to make a comma list for the employees who have registered for the training. (for example: [Registered employee] in row 1 is “Employee A, Employee B”.


      Now, the problem pop up, I would like to see portal can be filtered by drop down list. For example: if Employee A is selected in drop down list, only row 1 (in above demo portal/layout) should be displayed. But I have no luck on this.


      Below is how I created my drop down list.

      1) Created a value list (use custom values -- purpose is to have "Show All" option) and named it “Employee_VL”. Input value:

      'Show All


      Employee A

      Employee B

      Employee C'


      2) Created a text global field “gEmployee” in Employee table.

      3) Created a calculation field “calEmployee” in Employee table. And let calEmployee =

      If (gEmployee = “Show All”; ValueListItems(Get(FileName); ”Employee_VL”); gEmployee)

      4) Created another “Employee” table occurrence and named it “Employee_table2”.

      5) Create relationship


      Employee                Employee_table2

          id                 x            id

      calEmployee     =     Employee name


      6) In layout mode of "Employee training" layout, created a drop down list field. Set display values from “Employee_VL” and set display data from Employee::gEmployee.


      7) Go back to browse mode, test it out but with no luck.


      Any idea on how to make this drop down list work? Thank you in advance!