Summarize with Pivot Table like view

[laguna92651]

I need to summarize records, in both a report and browse view, that are of the form below. I need to basically do an Excel like Pivot Table of the data.

 

Record Form:

Article No  Product Code   Style        Size   UnpostedSales  Yds  Cost
DM13012    DM13012-XS      T Shirt        XS              3                  15     25

 

The Table view or List view would summarize the data and show the sizes as columns. The number beneath the XS column is the total number of DM13012-XS's in the data set, 23. The Article No consists of a family of Product Codes.

 

Article No. DM13012, has the following family of Product Codes.

 

DM13012-XS

DM13012-S

DM13012-M

DM13012-L

DM13012-XL

 

Summary View desired:

Article No   Style          Color            XS    S    M      L    XL   No. Units Mfg.
DM13011   T SHIRT     BLACK            23   12   20    15      8           78
DM13012   T SHIRT     CHARCAOL    11     5     9    27    28           80
DM13013   T SHIRT    TAUPE             15   21   16    16    23           91
DM13014   T SHIRT     OCHRE           15   29   26    21    27         118
DM13015   T SHIRT     WHITE            27   23   24      5     27        106
DM13016   T SHIRT     MINT               24    5   19     13    15           76
DM13017   T SHIRT     STRIPE            8   11     7     17      5           48
DM13041   TANK         BLACK            11   28   10    24      9           82
DM13042   TANK         CHARCAOL    27   30   21    13    10         101
DM13044   TANK         OCHRE           27    9   19      6     24           85
DM13045   TANK         WHITE            25   18   15    23      9           90
DM13047   TANK         STRIPE            5   20   30    24      9            88

 

Thank you

[bcooney]

You have not received replies because cross-tab reports are not natively supported in FM. You may wish to review this blog: http://www.teamdf.com/weetbicks/a-lightweight-cross-tabular-data-entry-method/64/

or this one:

http://www.clevelandconsulting.com/cc_pivot/

basically, google "cross-tab filemaker".

[LaRetta]

You might wish to check this out also. :-)

http://fmforums.com/forum/topic/71906-getting-more-out-of-filtered-portals-3unique-values/

[LaRetta]

Found it!  There were two produced at the almost same time.  This is the crosstab one ...

 

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

[laguna92651]

Thanks LaRetta, just got around to working with the Cross Tab demo file you referenced, just what I was looking for. Got it implemented with a little frustration. For my application of the cross tab I added the ability to create 5 Child records by the Parent. The Add Article button creates a new parent line. Once an Article No is entered in the parent a Set Script Trigger OnObjectExit creates 5 child records with the correct product codes and pricing information.

 

My problem is this, if you look at the attached screenshot, the Parent portal on the left has two lines per row (this is the invoice lines). The second line is showing the per unit price of Product code, gotten from the child records on the right using the cross tab approach given the CrossTab demo file. The price for all 5 Product Codes will always be the same. If the price should change from the "standard price" I would like to make one entry in the parent record and have that price update all of the children records, Currently I'm having to update the repetitive Unit Price fields individually, in the left parent portal. The child data can be seen on the right side, this will not be showing on the final invoice layout. The quantities fields under (XS, S, M, L, XL) and the UnitPrice on the second line of the row are repetitive fields that are handled similarity to the CrossTab demo file

 

I want to end up with one line per row on the parent portal being able to change the price in one place:

 

ArticleNo     XS  S  M  L  XL  Price  SumOfQty  ExtendedPrice

DM13011     2   1   3  4   2    $100       12              $1200

 

The ExtendedPrice is Price*SumOfQty (where is SumOfQty is the sum of the quantities under the sizes, XS, S, M, L, XL).

 

I also need to "pull back" or calculate the sum of the ExtendedPrice of the 5 child records.

Thanks

[LaRetta]

I wouldn't have a clue without seeing the file, sorry, but maybe others have ideas on it.

[laguna92651]

Tried to up load file with both uploaders, but get error no file selected. The file is 9M, is there another way to upload the file.

[Lee Smith]

If you tried to attach a file, it didn't make it. The process is rather easy, if you follow these steps.

Step 1
Find the file on your hard drive, and zip it.

Step 2
Do not use the Quick Reply, BUT instead, use the More Reply Option 

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Choose the file, and then Attach This File, and then click Add Reply or (Submit Modify Post.)

 

[laguna92651]

Okay, thanks Lee. It didn't like that the file wasn't zipped.

 

The layout in questions is CrossTabEntry, should open up right away.

INVOICESCrossTabEntryPost.zip