3 Replies Latest reply on Jul 5, 2017 8:12 AM by philmodjunk

    How to display results of multiple office locations

    santonivich

      Hello,

       

      I am new to FileMaker but I am familiar with database work mostly having worked with PHP/MySQL. I'm curious about how I should approach the following.

      • I've created a database/layout that is keeping track of expenses. I have fields for "office", "vendor", "date", and "amount". No issues with entering the data. It works really slick.
      • I enter expense amounts for (4) different offices. I enter in the name of the vendor, which office the expense belongs too, and the amount.
      • I'd like to create a layout that displays (4) different columns...one for each office. I'd then like to list the vendor and amount under the office and finally have a total of each column.

       

      What is the best way of approaching this? Thanks for the assistance.

        • 1. Re: How to display results of multiple office locations
          bigtom

          Two ways that will work, but there are more.

           

          1. Summary field in filtered portal for each office.
          2. Filtered portals and field or global variable set using ExecuteSQL ( "SELECT SUM....")
          • 2. Re: How to display results of multiple office locations
            santonivich

            Thank you - progress!

             

            I used the filtered portal as you suggested. The only issue is that I have 9 records. I can only see data in the filtered portal if I am on the record associated with that "office". For example, record 1 contains data for "New York" for the filtered portals for the other offices are empty.

             

            I'd like data for all offices displayed at the same time on one layout.

            • 3. Re: How to display results of multiple office locations
              philmodjunk

              I can only see data in the filtered portal if I am on the record associated with that "office". For example, record 1 contains data for "New York" for the filtered portals for the other offices are empty.

               

              I'd like data for all offices displayed at the same time on one layout.

               

              Then you need to either use a different relationship, different portal filter expression or both.

               

              If you'd like assistance with that, I suggest describing your current setup so that someone can then tell you how to modify it to get what you want.

               

              You might also research "cross tab" and "pivot table" here in this forum to see a number of discussions on this subject. There are actually quite a few different creative approaches to solving this issue that others have already developed from which you might choose an option.