4 Replies Latest reply on Feb 21, 2012 7:03 AM by IreneVassos

    Need advice on the proper method to create specific comparison report.

    brettJames_

      The FileMaker application I am about to develop involves a lot of complex price comparison reporting.

       

      I am going to have multiple vendors price the same material list.

      I am going to need to show my user a report that lists all of the materials that were priced on the left as rows.

      and

      each Vendor (variable not fixed) who priced each material for the specific project listed horizontally up top as columns. When the report runs all vendors prices for each piece of material will be listed side by side to be compared.

       

      This is something the user achieves very easily in Excel. Possibly because they use a fixed Vendors as their column headers. (The Vendors they use are fixed. If they have a new Vendor for a project they just replace the name of one of the Vendors that they are not using).

      Then for all of the vendors who bid the project they plug a price in for each material.

       

      Attached is a small Example Spreadsheet of what their current "Vendor Price Comparison" spreadsheet looks like.

      From the knowledge I obtained from reading FileMaker Training Series it seems like the only way to display a report like this is using the Web Viewer and HTML tables.

      I am wondering if this is true or if anyone can give me any advice on different ways/methods to display the same results in a FileMaker application if it isn't (Fields, portals, etc.)

      It would be greatly appreciated.

        • 1. Re: Need advice on the proper method to create specific comparison report.
          comment

          brettJames_ wrote:

           

          This is something the user achieves very easily in Excel. Possibly because ...

           

          ... because Excel is a spreadsheet application and Filemaker is not. Filemaker is not flexible in the horizontal direction.

           

           

          brettJames_ wrote:

           

          From the knowledge I obtained from reading FileMaker Training Series it seems like the only way to display a report like this is using the Web Viewer and HTML tables.

           

          It's one way,  not the only way. It wouldn't even be my first choice. You could also have a script compile the entire report into a global field, or prepare a tab-separated row for each record to show. Either way you are looking at some pretty complex scripting.

          • 2. Re: Need advice on the proper method to create specific comparison report.
            LyndsayHowarth

            Hi Brett,

             

            HTML is certainly not the only methodology which can be used here... it just makes it a bit easier to layout.

             

            So... you have:

            a Materials Table

            a Vendors Table

            and a join table for Vendor prices on Materials

            - there may be others like Project, Quote and QuoteLineitems which I am not taking into account here.

             

            You then have a number of options... depending on which tables' viewpoint you want to display the data from (Materials or Vendor or the join table we'll call VendorPrices).

             

            From the Materials table, you could:

            Find the specific range of Materials, sort by the Type (Lumber, Metal, Fasteners)

            Then the columns to the far right could have global fields as the header for the column and a relationship by the current records material and global vendor# value to the VendorPrices record match could be shown... 6 columns would mean 6 relationships.

            Then the best Price could calculated by a another relationship by material only to find the min(price)... this could then be used as the basis for another relationship which matches the material and price to find the vendor name.

             

            From the Vendor Prices you could do it with subsummaries when sorted by Type, Material and Vendor with a SSpart for Vendor... you could pretty-much do the rest of the table by using a combination of calculation and summary fields... or with a series of self-relationships.

             

            This could also be achieved by using scripted variables and displayed in a global text field... or as you have already discovered, embedded in html and displayed in a web viewer as a table.

             

            AND... there are most likely many more options I have yet to consider because I don't know the full scenario and because it's late

             

            HTH

            - Lyndsay

            1 of 1 people found this helpful
            • 3. Re: Need advice on the proper method to create specific comparison report.
              LyndsayHowarth

              Also forgot to say for the materials table scenario... the global field for Vendor would be a popup with the Vendors values from the Vendors table... allowing you to change the vendor for the column.

               

              - Lyndsay

              1 of 1 people found this helpful
              • 4. Re: Need advice on the proper method to create specific comparison report.
                IreneVassos

                Hi brett_james,

                 

                You could save yourself a lot of coding if you look at CC Pivot from Cleveland Consulting. It can easily be integrated with FiuleMaker to create reports in the kind of pivot table format for reporting that you seem to be asking for -- Michael is right that FileMaker is not flexible in the horizontal direction. Check it out -- they've done all the hard work!

                 

                Irene