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.
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?
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.
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.
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.
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.
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 ??? )
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"
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.
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?
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.
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?
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