3 Replies Latest reply on Jan 26, 2011 8:41 AM by philmodjunk

    Create portals with categories



      Create portals with categories


      I'm dealing with the following situation

      For Every Date and NAme I have to report some information base on a database by categories.

      To report one categorie I used the solution given by Filemaker in order to found the first duplicated record of a categorie via a relantionship and then create a sum of that value, like the following example, each categorie y summarized by the field with the name check at the end.

       like the image of the table

      Then I create a relation for every categorie based on the Date, name, and the check field to show via a portal the information by categories

        portals are shown in the image

      To show one category in the database I have to create one relationship and 4 fields. To show the data in my report database i have to create another relationship. Considering having 3 categories the schema doesn't look pretty good because it means that I have to create 12 additionals fields and six relationships. I don't like to create to many fields and relationships, I want to simplified my solution via calculations, portal filters, but I don't see any solution.




        • 1. Re: Create portals with categories

          What version of FileMaker are you using?

          What purpose is served by a relationship for your reports? (There may be an alternative here.)

          Do You need to see all these different portals at once or would it work for you to have one portal where you can see different categories and totals by selecting a category (and possibly other data) in some value list formatted fields?

          • 2. Re: Create portals with categories

            Hi I'm using FMPRO11

            The purpose of the relationship and showing the information in categories is because as a personal taste i prefer to see the information by browsing the records instead as a result of search and sort.

            I would prefer to have one portal and beeing able to filter them by selecting the category, for example a value list with CAT1, CAT2, CAT3

            thanks for your interest

            • 3. Re: Create portals with categories

              I asked about which version of FileMaker and about a value list controlled portal because there's a feature new to FileMaker 11 that makes this easier to set up than in older versions. (Where you can still do it, BTW, but takes a slightly different approach.)

              Let's call your tables and the basic portal relationship by these names:

              MainTO::PrimaryKey = PortalTO::ForeignKey

              Define the following fields in Main. In multi-user systems, global storage is a good field option for each of these so that different users can interact with this portal at the same time without interfering with each other: (starting the field names with g is a naming convention for identifying global fields.)

              gDate1, gDate2 (date)  (Note: date field in your portal table must be of type date for this to work.)
              gCat1, gCat2, gCat3 (Text)

              On a layout based on Main, place a portal to PortalTO on it. In portal setup... click the portal filter option and enter this expression:

              If ( Not IsEmpty ( gDate1 ) and Not IsEmpty ( gDate2 ) ; ( MainTO::gDate1 < PortalTo::Date ) And (MainTO::gDate2 > PortalTO::Date ) ; True ) AND
              If ( Not IsEmpty ( gCat1 ) ; Main::gCat1 = PortalTo::Cat1 ; True ) AND
              If ( Not IsEmpty ( gCat2 ) ; Main::gCat2 = PortalTo::Cat2 ; True ) AND
              If ( Not IsEmpty ( gCat3 ) ; Main::gCat3 = PortalTo::Cat3 ; True )

              This gives you an "all in one" portal. The logic of this expression is set up so that if any element ( a date range or one of the categories ) is left empty, that part of the expression then does not filter out any records. If all the fields are left blank, you see all the related records. If you want to use a separate portal for each category, you can simplify the above expression in each portal to only use the date fields and just one category field.

              Place the gDate1 and gDate2 fields on this layout and format them with drop down calendars if you want.
              Place gCat1, gCat2, and gCat3 on the layout and format them with value lists that list their respective catgory values.

              Now write a single step script: Refresh Window [ Flush Cached Join results ]

              On each of these fields, set a script trigger to run this single step script to refresh the portal any time you enter a value in one of these filter control fields. For fields where the user can type in data, (Date fields or drop down lists), OnObjectExit works well.
              For fields where data can only be entered with a single mouse click (Checkboxes, radio buttons, pop up menus), OnObjectModify is a better choice.

              Note: Many other filter expressions can be used. If you need  a "portal total", you can define a summary field in the related portal table and then place it inside a 1 row portal of its own with the same exact filter expression in order to display a portal total that will update with each filtering option.