You could do this by calculation or by relationships for the size.... or...
If your summary is sorted by Article number, you can have relationship by article number and XS and another for article number and S etc. you would then display a summary field for XS::Total S::Total etc.
Alternatively you can create a calculation GetSummary(XSTotal, Size) based on another calc each of the sizes. (Tedious)
IF there is only one record for each of the sizes AND always one of each record, you could do it with only one self relationshp on the Article Number then the Article size sorted then display separate instances of the same portal for each of the sizes starting at a different row number on the portal. You would use row 1 for L and 2 for M and 3 for S then 4 for XL and finally 5 for XS.
It would also be possible to gather all the information via a calculation used in a script and save it to a variable which is then set to a field or added as a merge-field or merge-variable on a layout. You would end up with tab-delimited text which would display nicely in a letter or such providing you set up appropriate tab-stops.
You could also embed the data in an HTML calculation and display it in a web-viewer.
I hope this all makes sense. We got too many loud things in our house for Xmas ;-)
I hear ya (couldn't resist the pun), I'll sit down and go over your suggestions.
I had just found that article before your post, by you, and was going to give it a read. How does your approach, Lyndsay's suggestions and the article by Kevin Frank compare for what I want to do?
"Outer Joins in FileMaker 12, part 1"
I had also posted a question on basically doing the data entry, invoice, that woud generate the data for the reporting I want, would your approach address that also? I haven't read it yet, but will do so shortly. I am new to FM so any additional insights or detail would be helpful as I work my way through the materials. Thanks Bev and Lyndsay!
The summary fields are in the child records and called into "filtered portals", so you can get the "pivot" you need. It's just another option. I might also try an ExecuteSQL query to get the data. Formatting it is much more difficult than the other methods, however.
Beverly, that worked great, it was just what I was looking for. I'll be doing alot of cross tabs moving forward. Actually very simple to implement.
I might be a little late to the party, but you could also look at our product, CCPivot. There is a free version and may be a simpler way to get where you are trying to go.
Sorry for the shameless plug, but since it is a free product and on topic I figure it added to the discussion...
Court Bowman, CEO
Cleveland Consulting, Inc.
Visit us on the web at http://www.clevelandconsulting.com
I implemented your approach to summarize the data in the format I wanted, it works great. I have been struggling with totaling the columns in the report.
The 1x1 portals are in the 0, 2, 4, 6 and 8 column headings. I'm trying to get a sum of each of those columns, the totals units column summed fine, that column is not on 1x1 portals. The totals are in a Trailing Grand Summary part. The 1x1 portals are in the body. What would be the approach to do the column totals.
The parent table is Articles and the child table is Products, the columns 0, 2 ... represent the number of sales for a product for a given size (0, 2, 4, 6 & 8)
Does your total units work if you have a different found set? Is it a summary field in the Articles table? Where are your other total fields located and how are they summarized? Perhaps an ExecuteSQL (based on the found set) is what is needed here?
I've tried it many different ways. Initially I put a summary field in the child, Products table, it seemed that was the approach you did in your demo file.
I also tried a summary field in the Articles table, parent, I first had to do a calculation field to go get the sales data from the products table, child.
The total fields are located in the Trailing Grand Summary
From the child, Products table I tried doing totals with:
I use a summary, sUnpostedSales = Total of UnpostedSales [Summary field]
From the parent, Articles table I tried doing the totals with:
cUnpostedSales = Sum(Products::UnpostedSales) [Calculation field]
sUnpostedSales=Total of cUnpostedSales [Summary field]
How would I implement the totals with an ExecuteSQL statement?