Help setting up a calculation fields for a report

[flutegirl]

Hi all,

I'm hoping someone can at least point me in the right direction on this problem I'm trying to solve, as I'm struggling to grasp the best way to do this. Sorry for the length of my first post here, but I figured I'd better explain this as best I can!

I am trying to create a report that summarizes sales and inventory values by any combination of product type, product brand, or supplier.

Currently, I have the following tables:

Sales (Each item has a single record, each record has fields for each month, so, for example, there are fields for Product Name, Jan 2012, Feb 2012, Mar 2012, etc).

Inventory (Again, each item has a single record, with fields for each month)

ReportFields (Table with single record for user entry, where they can determine which particular brand/type/supplier combination they want a report on)

A Cross-Reference table, which takes each Product Name and references that across a few other tables to populate the lookup fields - product type, brand, supplier. There is also a calculated "Match" field, which checks the entered data in ReportFields to determine if that product matches the requested criteria.

What I am having the problem with is figuring out how to structure the actual report, as I need summaries of the data based on the products, but I don't care to see the actual product info. However, I don't think an actual Filemaker Summary Report as a layout will give me what I want, as I need to see something like the following:

Inventory

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

2011 100 125 100 95 105 110 125 150 200 175 150 125

2012 120 135 110 85 90 100 115 140 180 155 140 115

Sales

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

2011 150 185 250 125 140 130 150 180 225 215 220 300

2012 180 140 170 150 125 150 115 140 220 200 300 345

I know that I could get a sum for any one month by creating a Calculation field through a relationship between the user-entered report criteria and the cross-reference table:

= Sum ( January 2010)

That does pull in the expected result based on the requested criteria, but I don't want to have to create 36 fields per bit of data (one field per month, 2010, 2011, 2012), as my report table will then have an insanely huge number of fields.

I was looking at possibly using a repeating field so I can just have three fields Inventory_2010 with 12 repetitions, Inventory_2011, etc. However, I found myself staring at the define fields window unsure of how to get the repetitions of the fields properly filled in so Inventory_2010[1] = Sum (January 2010), Inventory_2010[2] = Sum (February 2010), etc.

So my question is two-fold.

1) Is a repeating calculation field the best way to solve this problem?

2a) If so, can someone provide guidance on how to properly set up the calculation so the sums are properly entered in each repetition?

2b) If not, how would you solve this particular problem?

Any assistance would be appreciated.

[eos]

1) no

2a) n/a

2b) Whenever you have (or feel the need to add) an insane amount of similar fields, or are tempted by the cheap thrills of repeating fields, it's time to think of related tables.

You could factor out the 36 fields for sales and inventory fields each into one or two new tables. Each record here would hold the quantity for a unique combination of month & year, and depending on which route you choose, a flag for sales or inventory. (I'd choose the one-table solution.)

What I didn't see in your description is a transactions table for actual sales. Do you work with monthly totals only? OK, then all is well anyway.

But in case you record single item sales, then with a little bit of scripting you can have these fields automatically updated with every new or updated Sales records, which gives you up-to-date, real-time sales & inventory numbers. Since every Sales transaction knows its month, this works via a simple relationship.

The greatest advantage of this system - besides up-to-date numbers in real-time - is of course its expandability. Come 2013, you simply add 24 new fields (or 12 in each table, depending), or, if you record transactions, program the system so that if a sales transaction can't find its sales/stock-level counterparts to update, a new record is created automatically.

Generating a report like the one shown above is another matter … but with the described data structure in place, you could use a filtered or Cartesian relationship, then either use some portals with different initial rows, or List () the related fields and do some formatting with tabulators.

Or search the forum for crosstab.

[flutegirl]

1) no

Somehow, I thought that would be the answer.

For a bit of further clarification, I'm getting this data as an export from our production system (which is not in Filemaker), so do not have the transaction data - just the monthly summary by item.

If I understand your answer, the solution you would propose would store all of the sales/inventory data in a table such as:

Historical_Data

::ProductName

::Month

::Year

::Value

::DataType (Inventory or Sales)

I get that, and understand the benefits in terms of data storage. Unfortunately, my lack of training on databases is showing itself because I can't make the necessary logical connections between storing the data in a table like this and being able to extract the data I need to do the calculations and sums I need.

Specifically, I'll end up having the user enter one or more criteria for Brand, Vendor, Product Type, or a few other fields. I then need to sum for each month the Inventory value and Sales (our cost and the price the customer was charged) of all Products that match that criteria, and I'm just not sure how best to do that. Should I be creating another table that captures that data with a calculation field? Should I be using a script to do calculations and populate fields with that data?

I'll continue pondering this and trying a few ideas. Thanks for the input, so I can at least head in a proper direction.

[comment]

I'm getting this data as an export from our production system (which is not in Filemaker), so do not have the transaction data - just the monthly summary by item.

Do you have any control over the format of the received data? Ideally, each value would be in its own record. If that's not possible, a repeating calculation field just might be the best way to handle the data.

[flutegirl]

No, I don't have control over the format of the received data.

I have two exports to use:

1) Inventory data - this includes a lot of extraneous data I don't need, but I will pull from it two fields: Product Code and Current Inventory Value

2) Sales Data - this has a single line for each Product Code, and provides me with the gross sales value, gross profit, cost of goods sold, and a few other bits of data I probably won't use.

I'm finding that while I understand how to structure the database as eos suggests, I'm struggling at the point of trying to figure out what to do with the data once I populate the tables, how to get the summaries that I'm looking for, and how to present it in a reasonable form for the users.

I was hoping that my previous vague explanation would be helpful and get me pointed in the right direction, but it doesn't seem to be, so let me try to explain in more detail - if for no other reason, maybe I can understand what I'm trying to do a bit better.

I am attempting to create a report for the buyers at my company that shows them the historical sales and inventory data.

They need to be able to select any combination of:

Brand

Vendor

Buyer

Department

Category

Product Type

to be able to see a summary of all products that match those results.

For example, Buyer A may want to run a few reports:

Items for Vendor A

Items for Vendor A under Buyer A

Items for Vendor A in Category B

The way I'm currently doing this is with a huge Excel file. I have two sheets in the workbook - one for historic sales by product name (each row is for a single product code, and has columns defining the brand, vendor, buyer, department, category, and type, as well as an individual column for gross sales and cost of goods sold for every month for the past 3 years) and one for historic inventory by product name (again, each row has columns for brand, vendor, etc, and a column for the inventory value every month for the past three years.)

On the main sheet of the Excel workbook, I have provided space for the buyer to enter the Brand, Vendor, etc, they want to review. The two historic sheets have a column which is a match calculation, and marks any row where the Brand, Vendor, etc matches what is selected on the main page. Then I have formulas on the main page which look for lines with that match field marked and do a sumif() function to subtotal all matched lines and display it. There are also additional calculations to take the summary data of gross sales and cogs to determine profit, turns, GMROI, change over last year, etc. Probably about 10 calculations based on the three values I store in those two other sheets.

The problem with the Excel file is that it takes about a minute to update each time the criteria is updated. It is also very difficult to update. The entire building process of the Excel file made me feel like I was using a spreadsheet to do a database's job, but I'm starting to question that now, because I can't seem to figure out how to get the same functionality in the database, even though I know that the database would be better able to store and update the data.

I'm afraid I may have locked myself into thinking about this as a spreadsheet, which is sending me down the wrong path to get this into a database, but I'm at a loss for how to move forward to pull the data into a recognizable form.

Maybe I'm wrong and a spreadsheet is the proper medium for this particular report?

[comment]

The problem with your data structure is that it can be summarized, but it cannot be broken up - at least not easily. For example, you could find all sales records of product X - say that's 3 records, one for each year. Each record has 12 monthly columns, so in worst case you could use 12 summary fields to sum them up*. However, producing a report showing only sales in July and August is virtually impossible.

***

(*) There is a more efficient method, but that's not the point now