4 Replies Latest reply on Jun 25, 2010 2:52 PM by philmodjunk

    Portal conditions and totalling problem in Project Backup DB



      Portal conditions and totalling problem in Project Backup DB


      Hi guys,


      I've got a problem that I initially thought would be easy but is now making my head spin.


      I am trying to create a project backup database that tracks the location and status of projects on external hard drives.  As of now I've got two tables, "ProjectTable" and "HardDriveTable", and a layout for each.


      "ProjectTable" consists of:


        Agency Name

        Client Name

        Project Name

        Project Size

        Backup Drive 1 (Primary)

        Drive 1 Last BU Date

        Backup Drive 2 (Redundant)

        Drive 2 Last BU Date


      "HardDriveTable" consists of:


        Drive Name

        Total Capacity

        Used Space

        Free Space


      --and a portal that shows the Agency, Client, Project, Size, and Last BU of the included projects specified in "ProjectTable".


      I want to be able to add new project records as they come in the Projects Layout and add new hard drive records as needed in the HardDrive Layout.


      The problem I'm having is this:


      Each HardDrive record should be able to check each Project record to see if it is included in either backup slot, and if so display the relevant data in the portal.  The total size of the projects present in this portal should be subtracted from the Total Capacity of the drive to give Free Space.


      I've been able to filter the relevant project records in the portal by first creating the relationship between Project and HardDrive TO's:


        Backup Drive 1  X  Drive Name

      AND Backup Drive 2  X  Drive Name


      and then filtering the result in the portal with:


        ProjectTable::BU_Drive_1 = HardDriveTable::DriveName or ProjectTable::BU_Drive_2 = HardDriveTable::DriveName


      This gives me the correct result in the portal, but what I don't understand is why when removing the second match I get the same results.  Should values from Backup Drive 2 even come up as a result if it's not included in the range?


      The second problem I'm having is then totalling the sizes of the projects included in the portal, and then using that result to calculate the remaining drive space per hard drive record.  The result I'm getting right now is the total size of all projects combined.


      Does anybody have an explanation for the relationship issue or a solution to the portal total.  I am new to this so I'm not even sure if I'm approaching the design correctly.


      Oh, and I'm on OSX 11 Advanced.


      Any help would be appreciated.




        • 1. Re: Portal conditions and totalling problem in Project Backup DB

           Project:: Backup Drive 1  X  HardDrive:: Drive Name

          AND Project:: Backup Drive 2  X  HardDrive:: Drive Name


          You are showing the X operator in your relationships. This operator matches all values, it's a way to link to all records regardless of the key fields value so what you are reporting would be consistent with that. (either of the above pairs of fields will match all records.)


          For portal totals, it sounds like you've encountered the limitation to presentation level portal filtering (Filter expressions in portal setup for filemaker 11). You can either build that logic into your relationship or use a summary field defined in the portal table and refer to it in your expression to compute values based on the current portal's records.

          • 2. Re: Portal conditions and totalling problem in Project Backup DB

            Thanks for the help Phil.


            I was able to get a correct total by creating a field in the related table (Project) and adding that to the portal in the current table/layout (HardDrive).  My problem initially was that I was using a CALC field with a Sum expression, not a SUMMARY field.  I'm still not sure how to use a value from a filtered portal in a current table though.  I want to subtract the filtered total from a value entered in the current table's layout, to get a remainder.


            Are you saying it's possible to access this value with an expression, or that everything calculated in a filtered portal has to remain within the portal.


            You also mention building the logic into a relationship.  Is it better to look at this problem in this way as opposed to portals?

            • 3. Re: Portal conditions and totalling problem in Project Backup DB

              Filtering on the relationship level was your only option prior to filemaker 11.


              You can use a global text field, gDrive, defined in project table and formatted with a check box list of your drive names for this in a relationship:


              ProjectTable::gDrive = HardDriveTable:: DriveName


              If you select one drive name in this checkbox field, you'll see just those HardDrive records that match that drive name. If you select both, you'll see records for both...

              Now your sum calculations will work as advertised.




              • 4. Re: Portal conditions and totalling problem in Project Backup DB

                It took me a while to replicate something I'd read from filemaker about filtered portal subtotals.


                You can create a second portal to the same table occurrence with the same filter expression but set to show just one row. You can place your summary field from the portal record in this row. You can also define a calculation in the portal table that combines data from the related parent record with this summary field and place that field in such a one row portal. This then gives you an alternative approach to showing a portal subtotal (and calculations based on them) for filtered portals in filemaker 11.