13 Replies Latest reply on Jun 21, 2012 3:11 PM by philmodjunk

    Excel style spreadsheet using multiple layouts

    CraigFiore

      Title

      Excel style spreadsheet using multiple layouts

      Post

      Here is my situation. I am looking to make a report in an Excel type spreadsheet. I hope I can explain this clearly.

      I have one layout called "Work Orders". This layout has all my basic job info. Including a "JobID" field. This field is an unique number given to each job.

      I have a second layout called "Expense Log". This layout tracks all the expense for each job. I have a portal in "Work Orders" to view the totals of each category. (See Pic).

      I would like to set up a layout that will allow me to view multipul jobs expenses. I would like to set up a report sorted by "Owners" then "Porperty Name" then finally by "JobID" Then going horizionaly, Id like to have the column titels be: Admin Fees, Labor, Material Cost, Non-Material Cost, Sub Contractor, and Equipment. These field I just listed are in the "Expense Log" and are in a field called "Catagories" with a drop down.

      I hope this is enough infomation. Any help is greatful.

      Craig

      Screen_shot_2012-06-21_at_10.38.51_AM.png

        • 1. Re: Excel style spreadsheet using multiple layouts
          philmodjunk

          Remember that suggestion I made in your last thread for a "horizontal portal"? It works for this. It's the go to method for cross tab reports.

          You use a list view layout so that each row is either an individual record or a group of records represented by a sub summary layout part. The one row portals--each can use either a relationship or a portal filter to select for specific data--then organize data into columns for you.

          • 2. Re: Excel style spreadsheet using multiple layouts
            CraigFiore

            Im a little confused but I will try it. Would I make this new layout base on the "Work Order" DB or the "Expenses Log" DB?

            • 3. Re: Excel style spreadsheet using multiple layouts
              philmodjunk

              If you want the rows of the portal you show here to be columns, base it on work orders and use one row, filtered portals to arrange your data into columns.

              The portal filter for your Admin Fees column would be: Expenses::category = "Admin Fees".

              You can set up one such portal and test it. When you have it working, you can make duplicates of it and just update the portal filter to get different categories.

              • 4. Re: Excel style spreadsheet using multiple layouts
                CraigFiore

                Thanks Phil. I got it to work. The only thing I am haveing porblems is in the Trailing Grand Summary. I cant seem to make field that will total and avarage the columns.

                • 5. Re: Excel style spreadsheet using multiple layouts
                  philmodjunk

                  That will be a challenge. I suggest defining a summary field in the portal's table and using filtered one row portals to display it. You'll need a relationship for the portal that either matches to the correct total set of records or you may need to use a relationship based with the X operator to match to all records and then you use a more complex filter expression to filter out records that should not be part of the total.

                  • 6. Re: Excel style spreadsheet using multiple layouts
                    CraigFiore

                    I tried seting up a Summary field in the portal table but I could not get it to add the column up. Do you mean set up a relationship between "Work Orders" and "Expenses Log"? if so, what field do I use as the relationship, "JobID"? Could I get a more detailed explaintion, please.

                     

                    Thank you,

                    • 7. Re: Excel style spreadsheet using multiple layouts
                      philmodjunk

                      I need a better understanding of your report design requirements and the relationship you already have in place for the portals that are used in the rest of the report.

                      Is this report a list of all work orders or only a selected group of work orders?

                      The challenge here is that the expense records to be totaled have to be of the correct category but also only those that link to the work order records that are listed in your report and I don't know the criteria you might need to use for that. (all records, all records in a given date range, or ??? )

                      • 8. Re: Excel style spreadsheet using multiple layouts
                        CraigFiore

                        It usually is a select group of work orders. I may search by Company, Date Range, Job Type, etc. Its always the found set in "Work Orders"

                        • 9. Re: Excel style spreadsheet using multiple layouts
                          philmodjunk

                          That will definitely complicate matters as we need the total for the found set...

                          I can think of two approaches--both take a bit of doing and one may eliminate the filtered portals used in the rest of your layout.

                          Option 1) use a script to create a list of WorkOrder ID's in a global field. Use the global field to link to your expense records in a new relationship. Use a portal filter to limit records to desired column and then a summary field will display the correc total. This script can be performed by an OnModeExit (find) trigger so that it automatically takes place after you perform a find on the layout or you can set up a means where a script performs the find, sorts the records switches to this layout and also builds this list.

                          Option 2) set up a new relationship to expenses for every expense column of your report. Define calculation fields that always = specific category names and include them in the relationships so that each relationship matches only to expense records of a specific category. Now you can define a calculation field that returns this value from expenses and a summary field in the Work Orders table can total it up. (This is how we did cross tab reports before we had filtered portals...) Since you have to add these calc fields for each expense category that returns the expense amount, you can put them on your layout in place of the filtered portals.

                          • 10. Re: Excel style spreadsheet using multiple layouts
                            CraigFiore

                            I like Option #2.

                            But I have a question. Are you saying, create new fields in "Work Order" and "Expense Log" called Admin Fees, Labor, Material Cost, etc. Then create a relationship between "Work Order" and "Expense Log" connectiong those two DB by all the new fields?

                            • 11. Re: Excel style spreadsheet using multiple layouts
                              philmodjunk

                              Option 2 requires adding two calculation fields for every expense category and one new relationship for each category. Then you add a summary field for each category...

                              The result is a much more complex relationship graph, A lot more fields in your work orders table, but you'll no longer need a bunch of filtered portals and you won't need a script to set up a key that will match your found set to expense records in a relationship.

                              If you have FileMaker 12, there's probably a third alternative that would use fields that use the ExecuteSQL function to produce the desired totals.

                              • 12. Re: Excel style spreadsheet using multiple layouts
                                CraigFiore

                                I dont have FMP12 and pardon me for being so specific but Im still a bit of a novice. I'd like to take this step by step.

                                1. Create a field called "Admin Fees" in "Work Order" w/ a calcuation of "Always="Admin Fees"

                                2. Create a field called "Admin Fees" in "Expense Log" w/ a calcuation of "Always="Admin Fees"

                                3. Create a relationship between Work Orders and Expense Log Linking both "Admin Fees" fields.

                                4. Create a Summary field to total one of the "Admins Fees" field. (im not sure to use the Work Order of Expense Log)

                                5. Repete for every catagory.

                                Is that right?

                                Now do I still need the filtered portal in the body of the layout?

                                • 13. Re: Excel style spreadsheet using multiple layouts
                                  philmodjunk

                                  1 is correct, but not step 2.

                                  You'd create a field named something like constAdminFees and the complete calculation for it is "Admin Fees" -- just straight quoted text so that every record in Workorders has this same specific value and it exactly matches the same value as selected in the category field in expenses.

                                  Then the relationship you'd set up would be:

                                  WorkOrders::WO_ID = Expenses_AdminFees::WO_ID AND
                                  WorkOrders::constADminfees = Expenses_AdminFees::Category

                                  Then you need to add another calculation field, cAdminFeeAmt where you select the expense amount field from Expenses_AdminFees as its only term.

                                  Finally, you can define a summary field to compute the total fo cAdminFeeAmt.

                                  Repeat this process for every expense category