1 2 Previous Next 26 Replies Latest reply on Oct 15, 2009 10:32 AM by philmodjunk

    How to filter records to be displayed in the portal?

    LeoCA

      Title

      How to filter records to be displayed in the portal?

      Post

      We have Vendors and Invoices tables. A layout is designed to base on Vendors table and has Invoices as  portal tabs. Invoices has a status column. How can we show invoices for different status, each status has its own tab?  Thank you for your help!

       

      LeoCA

        • 1. Re: How to filter records to be displayed in the portal?
          Orlando
            

          Hi LeoCa

           

          How do you want to display the data in the portal, multiple static portals or a single dynamic portal, where you specify a value from a drop-down and the contents changes.

           

          The first option, a number of static portals, would simply involve you setting up a calculated field in your Vendors Table for each category and then creating a Table Occurrence for each category between the VendorID and Category, so one relationship would look like this

           

          VENDOR                      INVOICE_Category1

          _UniqueID       --=--      _VendorID 

          txtCategory_1  --=--       Category

           

           

          The second option, and preferred option as no extra calculation for each category, and a single Table Occurrence for Invoices, is to have a dynamic portal.

           

          This simply involves having an extra global in your Vendor table that will be populated with the category you are displaying in the protal and will be the second match in your portal between Vendor and Invoice

           

          So create a new field called gCategory in your Vendor table, change the storage of that field to be a global, in the field options under the Storage tab.

           

          Now modify the relationship in you Relationship diagram so the Vendor / Invoice relationship looks something like this:

           

           VENDOR                      INVOICE_Category1

          _UniqueID       --=--      _VendorID 

          gCategory       --=--       Category

           

          Now place the gCategory filled on your layout and make it a Pop-Up Menu with the same value list as the Category field in you Invoice and that should do the trick.

           

          You will need to make sure the field starts with a value, as if it is empty the portal will be empty.

           

          Let me know if either of these options are what you are looking for and if anything need further explanation. 

          • 2. Re: How to filter records to be displayed in the portal?
            LeoCA
              

            Thank you Orlando!

             

            Your response gives me other idea to make it work.  This is what I did:

             

            Add Status (Category) to Vendors as you suggested, but NOT global.

            Change relationship between Vendors and Invoices to vendorID and Status.

            Add Valuelist "Status" using source of Status on Invoice Table

            Specify Drop-down list using Status Valuelist for item Vendors::Status On the Layout

             

            Now, I can dynamically select status to get invoices.  I can not get all invoices (regardless status).  But, I can live with this.

             

            Thanks again.

             

            LeoCA

            • 3. Re: How to filter records to be displayed in the portal?
              Orlando
                

              If you wanted to go that one step further and display all, then you need to add in a calculated field to the mix, along with a new value list.

               

              What you need to do is first create a calculated field in Vendors, lets call it cStatusLink ( Calculated Text ), Status is your selection field in Vendors, and the calculation is:

               

              If ( IsEmpty ( Status ) or Status = "Show All" ; ValueListItems ( get ( fileName ) ; "Status" ) ; Status ) 

               

              Now duplicate you Status Value List, "StatusFilter", so you have all the same values but at the end input of the current list add

              "-
              Show All"

              This will give you a dividing line and the Show All at the bottom.

               

              Now modify the relationship of the portal so it is linked between VendorID and cStatusLink to Status.

               

              Now on your layout attach the new value list "StatusFilter" to your Status field in Vendors.

               

              This shoudl display all Invoices when the Status field in Vendors is either empty or has "Show All" selected.

               

              Let me know if this works or needs any extra explanation.

              • 4. Re: How to filter records to be displayed in the portal?
                trialuser1111
                  

                I need a little more explanation on this, since I'm still very new to FileMaker and have not done any work yet with calculations.  I was able to use the solution in this thread to get my portal results to filter (though not by a value list), but it happens across all my layouts, and I only need this filter in one layout.  I'm working with two tables ("Firm Data" and "Meeting History"); in Layout 1, I can now view the relevant language on a firm and see the meeting history of a particular client via the portal.  In Layout 2, I'd like to be able to see the meeting history for all clients with a particular firm.  How can I do this without a value list?  Ideally, I'd like the portal to return the full, un-filtered meeting history if the "client ID" field is left blank in Layout 2.

                 

                Thanks 

                 


                Orlando wrote:

                If you wanted to go that one step further and display all, then you need to add in a calculated field to the mix, along with a new value list.

                 

                What you need to do is first create a calculated field in Vendors, lets call it cStatusLink ( Calculated Text ), Status is your selection field in Vendors, and the calculation is:

                 

                If ( IsEmpty ( Status ) or Status = "Show All" ; ValueListItems ( get ( fileName ) ; "Status" ) ; Status ) 

                 

                Now duplicate you Status Value List, "StatusFilter", so you have all the same values but at the end input of the current list add

                "-
                Show All"

                This will give you a dividing line and the Show All at the bottom.

                 

                Now modify the relationship of the portal so it is linked between VendorID and cStatusLink to Status.

                 

                Now on your layout attach the new value list "StatusFilter" to your Status field in Vendors.

                 

                This shoudl display all Invoices when the Status field in Vendors is either empty or has "Show All" selected.

                 

                Let me know if this works or needs any extra explanation.


                 

                 


                • 5. Re: How to filter records to be displayed in the portal?
                  unc12
                    

                  Orlando,

                   

                  Thank you. This was very helpful.

                   

                  Unc12 

                  • 6. Re: How to filter records to be displayed in the portal?
                    dinora
                      

                    Hi Orlando

                    Can you explain the second option (dynamic portal) I understand the global field but how about the category field ?  Does this require to be a calculation?

                     

                    I have Suppliers and tours that join

                     

                    From the Suppliers table I have a portal which shows me those records that are allocated

                     

                    Supplier A

                     

                    Tour 1

                    Tour 2

                     

                     

                    A1

                    A2 ...

                     

                    And if I needed to allocate more I could as long as there are tours available.  Each tour operates in a different city

                     

                    So I want to be able before my portal loads to select the port first from a drop down arrow and then display the tours allocated to that port to that supplier? 

                     

                    Furthermore is there a way of creating tabs across and each tab is a city or port under each tab a portal displays the tours allocated to that supplier

                     

                    So I have Supplier A which works in Florida and in Florida we offer two tours in Miami and two tours in Orlando.

                     

                    So Can I from the Suppliers layout show a tab that says Tours - Under this tab another set of tabs that say

                     

                    Miami   Orlando

                     

                    If you click on Miami the portal shows those tours that are allocated to this supplier in Miami, and if you click Orlando then the same happens for Orlando, Florida

                     

                    Thanks in advance.

                     

                    Dinora

                    • 7. Re: How to filter records to be displayed in the portal?
                      Orlando
                        

                      Hi trialuser1111,

                       

                      Sorry for the delay in getting back to you. I am unsure of a few points and to get a better understanding of what you are trying to achieve could I ask a few questions,

                       

                      Is there a third table 'Clients' in with all this, or are you refering to a record in the Firm Data table as your clinets?

                      Also what table occurrence context is Layout 2 based on, and how is it currently linked to Meeting History? 

                       

                      Get back to me and I will see what I can do.

                      • 8. Re: How to filter records to be displayed in the portal?
                        trialuser1111
                          

                        Orlando,

                         

                        There is no third table called Clients.  Per the earlier posts in this thread, I created a Global field (just called "Global") in the Firm Data table which has a relationship with the "Client Ticker" field within the Meeting History table.  Layout 1 and Layout 2 both implement portal rows to display relevant meeting histories, so I suppose any solution would apply to both layouts--which is fine.  As I said, the ideal solution would be for all recent meetings to be displayed if the Global field is left blank, but if we needed to implement a drop-down menu with a "Show All" option that would be fine.  However, I would prefer not to create a value list of clients if it can be avoided.

                         

                        Thanks 

                        • 9. Re: How to filter records to be displayed in the portal?
                          Orlando
                            

                          Hi trialuser1111,

                           

                          Ok I think I have got it, I think the best thing for you is to have an additional calculation in both tables, Firm Data and Meeting History

                           

                          in Firm Data create a calculation called _ClientTickerKey with the calc

                           

                          If ( IsEmpty ( Global ) ; "All" ; Global )

                           

                          And in Metting History the calculation is again called _ClientTickerKey and should be 

                           

                          "All" & Client Ticker

                           

                          'Calculated result is' on both calculations should be set to 'Text'

                           

                          Now you need to create a new relationship between Firm Data and Meeting History and the link should be these two calculated fields, and then setup the portal on Layout 2 based on this new relationship and in the global is set only those clients meeting notes will display, otherwise all meeting notes will display.

                           

                          Let me know if I have got this right and it works, or if I am way off.

                          • 10. Re: How to filter records to be displayed in the portal?
                            Orlando
                              

                            Hi Dinora

                             

                            Are you still having trouble with this?

                             

                            What you are asking for is dynamic tabs going across, this is not that easy, if a supplier only ever had 5 cities they you could spend allot of time with portals to get this to work, but your best bet would be to have under your supplier table a portal listing all the cities that supplier operates in, and then you click on the line you require and in a second portal the list filters to all the tours for that city and supplier.

                             

                            And before you click on a city it could display all the tours for that supplier.

                             

                            If you are still trying to solve this and this sounds like the sort of thing you need then let me know and I can put together a quick demo file, might be easier than me writing it all out, but it is pretty much as my original post but with an extra layer.

                             

                            Let me know and I will get to work. 

                            • 11. Re: How to filter records to be displayed in the portal?
                              trialuser1111
                                

                              Orlando,

                               

                              Your solution worked perfectly!  The only step I had to add to your instructions was to remove the relationship between Global in Firm Data and Client Ticker in Meeting History.  This is great, thank you! 

                              • 12. Re: How to filter records to be displayed in the portal?
                                dinora
                                  

                                Thank you Orlando

                                 

                                I solved it already

                                 

                                From the suppliers table I select the supplier first then select the city and this displays only the portal rows relevant to the tours for that supplier and for that particular city.

                                 

                                I am all set

                                Thanks

                                • 13. Re: How to filter records to be displayed in the portal?
                                  Orlando
                                     Glad to hear both your solutions are working.
                                  • 14. Re: How to filter records to be displayed in the portal?
                                    petertoo
                                      

                                    Hello Orlando,

                                    I have one question regarding the method you previously mentioned.

                                     

                                    ------ excerpt from your previous reply -----

                                    Now duplicate you Status Value List, "StatusFilter", so you have all the same values but at the end input of the current list add

                                    "-
                                    Show All"

                                    --------------------------------------------

                                     

                                    I had one Value List created with the option "Use values from field" from Filemaker. I can duplicate this Value List. But I cannot find a way to add something ("show all") behind the Value List.

                                    It seems Filemaker only allows me to add the content behind value list if the value list is created with the option "Use custom values". Is it correct understanding? Thank you very much for your help. I am very new to Filemaker.

                                     

                                    Peter Chang

                                    1 2 Previous Next