Statistics for accounting

[bombdesign]

Hi all

I have an order database with a relational line items file shown in a portal. Now, I need to pull out statistics from the line items e.g.: How many of product number X (about 400 different products) was sold under a given period, specified by the user. How much is the total sales sum of product X, total VAT etc. The result should be exportable to Excel.

I guess I have to search for and extract the product numbers for the given period somehow and then loop through them one at a time, but how do you do that or are there some smarter way.

TIA

Thomas

[CobaltSky]

From what you've said, the source data for the extracts you need will all reside in the line items file. If that is the case, the solution to your current dilemma would be to script a find and export which runs within the line items file.

If desired, you could add some summary fields in the line items file (total value of product solde, total VAT etc) and include them in the export field set specified for the script.

Finally, you could set up scripts within the main file which will call the scripts in line items remotely (as externals) so that users need not trouble about switching files or layouts, and the process can be seamless and automatic.

Having said all that, I must say that I've not encountered too many users who want to migrate their data from FMP into Excel - mostly it goes in the other direction - but I'm sure you have your reasons!

[JerrySalem]

Perhaps, what the original poster intends to say is that they want to essentially run a report, calculate some summary data (total count, total sales etc), then export the report to excel?

That we do all the time.

[CobaltSky]

Yep, That certainly seems to be the gist of what Thomas is saying.

[bombdesign]

Thanks for your reply CobaltSky.

Would this be a good way to go you think?

In the line items file script something like:

1) Search userspecified date period and first product number.

2) Sum up found data with statistics field (e.g. sum of total sales field)

3) Copy product number and statistics field and paste to a text field with

a delimiter separating the numbers and the next line.

4) Go to next record in product catalogue

5) Loop 1-4 for all product numbers.

6) Copy text field with data and export.

If that is a good way to go, I hope it is as easy to do in reality as it was in theory :-P

[CobaltSky]

Hi,

I think the process can be made a little easier than you are envisaging.

Step 2 can be made automatic by adding a summary field to the line items file which is defined as 'Total of Sales'.

Step 3 should not be necessary, as FileMaker will compile all the requested data from the found set automatically for you when you run the 'Export' script step.

Rather than looping through the records in the main file and going backwards and forwards to the line items file for each record, I suggest that you:

1. Loop through the found set to place all the key field values (separated by carriage returns) into a new global field in your products file,

2. Create a new relationship which links this global field to the key field in the line items file.

3. Perform a "Go to Related Records [ Show]" step based on the new relationship (created at step 2 above) to locate all the line items records in one hit.

4. Call a script in the line items file which sorts and exports all the found records.

Chances are you will find this slightly different approach easier to set up and more efficient to run.

[bombdesign]

Thanks for your suggestion CobaltSky. I will try it out as soon as I can. As you may have guessed, this is a little bit over my capabilities, so I might have to come back here for some more guidance.

I intend to export the results in the summary fields, not the actual records that make up the sum. This is so that the user can make pie-charts etc. in Excel which they are familiar with.

E.g.

Product no: 001

Amount: 5

Taxes total: 50

Sales total: 500

Product no: 002

Amount: 50

Taxes total: 500

Sales total: 5000

and so on...

Does this make a difference?

Some questions

In (Your) step 1: do you mean to place the sum or all individual records into that global field?

In (your) step 2: what would be the key field? or do I make a global field with a corresponding relation for each key field?

I feel a bit like Bambi on ice when scripting, however this forum surely adds some stability to my unstable treading.

[CobaltSky]

Hi Thomas,

A key field is the field which is used for matching records between two files in a relationship. Somtimes also referred to as a 'match field'.

The key field is all you'd need to collect at step one. None of the values need be collected at that stage. The purpose of collecting the key fields is so that you will be able to cleanly and efficiently export the data in one hit at step four.

Hopefully I've now also answered your question about step 2. The key field I'm referring to their is the one which is the target of your existing relationship to that file.

This is so that the user can make pie-charts etc. in Excel

I suspected that may be what you had in mind. Not wishing to confise the issue, but There are a few options for generating pie charts within FMPro which you might like to be aware of.

If you'd care to see, take a browse through the following three links:

oAzium Charts:

http://wmotion.com/all_charts.html

X2max - xmCHART:

http://www.x2max.com/english/products/xmCHART/info.html

ChartMaker Pro

http://www.briandunning.com/chartmaker/index.shtml

Mind you, each of the above requires scripting which is at least as complex as what you are contemplating at present. It may possibly produce a better outcome overall, but you'll have to weigh up all the issues (including cost) on this one.