AnsweredAssumed Answered

Problem with filtering portal with drop down list

Question asked by petertoo on Aug 21, 2009

Title

Problem with filtering portal with drop down list

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:新細明體;} 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]  -->

Hello,

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.

 

Training                                                                                                                

[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       

                                                                                                      

Register   

[id][training_id][employee_id]

1           1               1

2           1               2

3           2               2

4           2               3

 

Employee

[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!

 

Peter

Outcomes