6 Replies Latest reply on Nov 30, 2009 10:01 AM by philmodjunk

    Different Filters for Different Portals?



      Different Filters for Different Portals?




      I'm trying to make two different relationships between the same two tables; I have a parent table (Clients) and the child table (Billing).  In one portal (on a Client layout) I want to see their complete billing history, so I've just got that relationship as "bClient Serial" in Billing to "cClient Serial" in Clients.  In the other however, I'm wanting to just see their activity for one month at a time, so I'm trying to work with a filter for it.


      I've created a calc field in Billing that takes the date of the bills, creates a month name out of that date and then calculates the month/year into a single word (i.e., "November2009").  I then created in Clients a drop down menu of month names and one of years, and then a calc field to combine the two so that it will match the format of the calc field in Billings.  I then based my new relationship off of a second incidence of the Billing table, with the two criteria being:


      cClient Serial -- bClient Serial

      cMonthCalc -- bMonthCalc 


      So the idea is that when I select the month and the year, it will only show those bills that took place during that time period.


      Here's the problem:

      When I look at the results in the second portal (to just see this month's bills), it shows the most recent bill, shown multiple times, and when I make the MonthCalc field in Clients into a global field (like I guess I'm supposed to) the whole system stops working, and nothing shows up.


      What the bleep? 



        • 1. Re: Different Filters for Different Portals?

          It would be logical to make your Client field(s) global, so that as you browse through various Client Records the same critera apply - unless, of course, you want the flexibility to set a different year and month for each separate record.  To remove one level of error-possibilty I would test it using a single field (as opposed to a concatonation) of just 'November2009' directly.  You can create the concatonation after you prove the principle works.


          The characteristic you describe of the same record repeated in the portal suggests that you have not used EXACTLY the same relationship for portal relationship and the relationship from which you've picked the fields.


          Are the fields in both calculations set to return a result type = 'Text'?


          Your logic looks good to me.



          • 2. Re: Different Filters for Different Portals?

            Yup, I already tried just using a text field where I typed in the value, just in case it might have been some oddity with my calculations, but it gave the same result.


            Is it because I used a second Billing table?  When I tried to add on a second relationship to Clients between the two original tables (Clients and Billing) it told me I couldn't do that, so I said ok to their suggestion of creating a second billing table. I have to admit that's the part that's a bit confusing for me; if I create records in one of the Billing tables will it also show up in the other?


            If that's not the way to do it, how would I add a second relationship between the two original tables?



            • 3. Re: Different Filters for Different Portals?
                 Oh, something I do notice is that when the identical payments show up, it shows up the number of times that there are bills (I've got 5 test bills in the original Billings database, and there are 5 identical entries  in the new relationship portal).  I've tested it out, but the principle holds true regardless of how many bills are in there...
              • 4. Re: Different Filters for Different Portals?

                I think we have hit the problem.


                You only have two Tables in your file: 'Clients' and 'Billing'.   You have created a relationship between the two tables, say by linking ClientID to ClientID.  When you only had one relationship, FM knew which way you wanted to find a record in the 'Billing' table when you looked across to it from the 'Clients' table.  So it was happy for you to leave the names of those tables the same in the Relationships Graph.


                Once you create a second way to link to the Billings Table from Clients, Filemaker has to know which way you mean when you effectively say 'Show me the appropriate records that are in the Billings Table'.  It ask, "Well what do you mean by 'appropriate'?  Do you mean the ones connected by a common ClientID?  Or do you mean the ones connected by ClientID amd MonthYear?"


                You have to be able to tell FM which to use, so it makes you name the table at the end of each relationship with a unique name.  But they are not multiple tables, but called Teble 'Occurances'.  You will have two tables: 1 Client, 1 Billing.  But you will have 2 OCCURANCES of the Billing Table.  Each will contain exactly the same data.  The new name you chose (FM probably suggested 'Billing 2') is simply defining that as  the 'new' way you find matching records in the Billing Table.


                If that is what you have (you can see it in the Manage Database -> Relationships graph) then set the portal AND each field in it to be grabbed from the 'Billing 2' relationship shown at the top of the Specify  Portal and Specify Field dialogue as you drag it on to the layout and into the portal.

                • 5. Re: Different Filters for Different Portals?

                  That did it!  I had forgotten to change the fields in the portal to be from Billing 2 when I changed the portal itself to derive from that table.  Now it works like a charm.


                  Definitely one of the "doh!" moments. 


                  Thanks so much!



                  • 6. Re: Different Filters for Different Portals?

                    A suggestion for a "better" date calculation. If your month calcualtion is only needed for filtering a portal, ignore this post. If, however, you want to set up a summary report where you sort and group your records by month (and this is a very useful report to have for this type of DB), try this calculation instead.


                    Date(month(yourbillingdatefield), 1, year(yourbillingdatefield))


                    Set this calculation to return "date" instead of the default "number" setting.


                    Not only can you use this field as your "filter" key, but if you sort by it, will correctly group and sort your records by month in correct ascending or descending order--unlike the text based "month name" calculation you are currently using.


                    For an example of how to set up an auto-enter calculation on a date field for your matching global field, see this link:


                    report / summary total of time and datefields