Getting multivariable sums

[elipsett]

I recently upgraded my FileMaker from 8.5 to 11, and am hoping that I can simplify a few of the kludgier solutions I have. Considering that FMP is so powerful, it is strange that I can’t find a way to do what I need to be able to do more directly. More than likely, I can’t do it because I’m overlooking something obvious.

Let me outline the situation.

I am selling things. The core issue is that a single sales record contains the following data:

Order No.

A single item No.

Quantity sold

Type of sale (cash, credit, giveaway, for example)

Date of sale

etc.

A CustomerOrder contains various information, displaying ItemNo and Qty from the SalesRecord. When a CustomerOrder is created, it writes OrderNo, SaleType, SaleDate etc into the SalesRecord. Individual SalesRecords are displayed as line items in a portal in the CustomerOrder.

All that is fairly straightforward, and no particular need to change anything (but it can be changed if it helps resolve the following problem).

Using the SalesRecord database, I want to be able to see various statistics on sales. I would like to see a sheet listing ItemNo and sales for each month of the year, for example. I need quantities broken down by ItemNo, SaleDate and SaleType.

At present I am getting all these statistics through hardcoding, which means I have to hardcode a new set of calculation and display pages for every new item.

So, is there a programmatic way to handle this?

I want to be able to see a page showing ItemNo down the left side, and sale quantities for each month of the year in columns across the page. When I create a new ItemNo, FMP automatically takes care of calculating and displaying the statistics.

I think that’s enough information, but if I’ve forgotten something let me know and I’ll try to explain it in simplified form. The complete FMP system is obviously enormously more complicated, but I think this is all the pertinent parts.

Thank you for your time.

[imoree]

SubSummary reports is what comes to mind.. Or reporting in general . THe only issue is all tables must be related properly.

[bcooney]

"I want to be able to see a page showing ItemNo down the left side, and sale quantities for each month of the year in columns across the page."

That is a cross-tab report, and is doable, but is an advanced technique in FM.

FM thinks vertically. As imoree suggests, you should be able to create a sub-summary report that'll give you your stats.

Sub summary:

http://help.filemaker.com/app/answers/detail/a_id/6198/~/simple-example-of-a-subsummary-report

Cross-tab:

http://edoshin.typepad.com/bits_and_pieces/2006/12/crosstab_report-1.html

http://ist.mit.edu/sites/default/files/migration/usergroups/filemaker/fmug/pres/crosstab.pdf

[comment]

I'd also suggest:

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

[Ralph Schwegler]

i have something similar...

product (id and name)

sold (product id, year, amount in currency, quantity)

my "crosstab" doesn't work. any hint why?

Produkte Statistik v 0.91.fp7.zip

[comment]

See:

http://fmforums.com/forum/topic/60368-fm-bug-with-valuelistitems-function/page__view__findpost__p__285448

[Ralph Schwegler]

thank you. that was something i corrected.

but still: it doesn't work as it should...

probably a tiny thing, but which ?

Produkte Statistik 0.92.zip

[comment]

that was something i corrected.

I don't see that you did. Remove the period from the file's name, then look at the Produkt layout (I haven't examined the other layouts).

[Ralph Schwegler]

sorry about this - to use it i crop the name and remove the version number...

i clean up everything once more and report with a "clean" version. only trouble now (but i can - and will - live with that) is that there is no year stated if there was no sale of the first product that year.

great thanx to comment for helping me (us ?) on this issue

Produkte Statistik.zip

Edited November 17, 2011 by Ralph Schwegler

[comment]

only trouble now (but i can - and will - live with that) is that there is no year stated if there was no sale of the first product that year.

This could be solved by using another method to display the labels, for example a repeating calculation field =

GetValue ( ValueListItems ( ... ) ; Get ( CalculationRepetitionNumber ) )

[Ralph Schwegler]

This could be solved by using another method to display the labels, for example a repeating calculation field =

GetValue ( ValueListItems ( ... ) ; Get ( CalculationRepetitionNumber ) )

i'm sorry, i cannot understand how to make that. could you give me some more help on this please?

[comment]

Like this?

Produkte Statistik 2.zip

[Ralph Schwegler]

Like this?

well... did you forget something or did i miss it?

[comment]

Neither - the forum became unresponsive just as I posted, and the attachment (which was there) wasn't showing until I edited the message

[Ralph Schwegler]

great, i fetched it and will analyse that l8ter.

thank you very much