1 Reply Latest reply on Apr 29, 2010 11:24 AM by philmodjunk

    Portal filtering problem



      Portal filtering problem




      I have several tables feeding into one form through the use of portals. The main form is Accounts, the sub tables are invoices, expenses etc.

      In the Accounts table I have Field "month" and in the sub tables I have Field "date" and then Field "Month", which is a calculation month = month(date).


      The tables are all linked through the Field "Month"


      What I am trying to achieve is for the accounts form, through a different tab for each table, to display a portal with one months transactions. Although there is data in the expenses table, this is not showing up through the Accounts form in the relevant month (or any month for that matter!).


      In the portal filter I have Expenses::Month = Accounts::Month and I am thinking that this might be part of the issue. The expenses::month is text from a drop down menu and the Accounts::month is a calculation month(date).


      I have tried all sorts and frankly have now confused myself a little (a lot) and still can't come to a workable solution.


      I am using Pro11 on Windows 7 and I am 3 days into playing with Filemaker, so very new.


      Please let me know if there is not enough information here to help and I will provide more. 


      Thanks in advance

        • 1. Re: Portal filtering problem

          The initial problem.


          Expenses::month has data such as "January", "February", etc.


          Accounts::Month is a calculaition with 1, 2, ...12  (the number of the month)


          That won't match.

          You could match values by changing your calculation from Month() to MonthName() but I wouldn't recomend it. If you do that records with January 2009 dates and records with January 2010 dates will both appear in your portal when you select "January" in your drop down.


          One option is to filter on two pairs of fields, one for the month and one for the year. That may make your drop downs easier to set up.


          Another approach is to use this calculation on both sides of your relationship: DateField - Day (dateField) + 1 and specify "Date" as your return type in Accounts::Month.


          Make Expenses::Month a date field and specify this same basic calculation, but use "self" in place of "datefield" as an auto-entered calculation. You can format the field to only display the month and year and give it a calendar pop up. When you select any day of a given month, the auto-enter calculation converts it into the first of the month and that will match the calculation fields. (Make sure "Do not replace existing value..." check box is cleared).