INITIAL DESIGN SCHEMA SETUP QUESTION

[NikK100]

I am a novice.  I have a File Maker project I want to develop, but am struggling with the initial schema design/setup.

I have weekly cost reporting data in excel format.  The weekly excel file is made up of 11 columns and between 500 to 3000 lines of data, the first two columns are 1) unique account code and 2) descriptive name.  Four of the columns contain updated key numerical data each week, the other 5 columns can be derived from the data in the 4 key columns.

I want to do various row calculations based on the average of the specific key column.  I want to compare the average of the specific column/row (updated each week) to a matching columns/row budget number in the current week.

I cannot work out if each weeks data should be appended to the previous weeks, with a new/identifying week number or if the weekly data should be kept in separate related tables.

Any guidance gratefully appreciated.

[Ocean West]

Would you be able to share a sampling of columns and rows it would help finding you a solution.

 

[comment]

It is indeed difficult to understand the described situation - and even more difficult to understand what exactly is your question.

It sounds like you need to have 2 tables: a table of Accounts and a table of weekly data. You should definitely not create a separate table for each week - if that's what you mean by "if the weekly data should be kept in separate related tables.".

It's not clear if the weekly data has a column for identifying the week. If not, you will have to find a method to determine the week and add it to the imported data.

 

[NikK100]

Thank you both for your help.  It is very much appreciated.  I am attaching a spreadsheet with sample data as well as the beginnings of a report I’d like to generate.

I realize the excel contains a lot of ideas to be implemented, but they are just there to help with understanding the design schema.

SAMPLE DATA FOR PROJECT.xlsx

[comment]

It is difficult to understand what the data represents in real life. The terms Description 1, Description 2, Description 3 etc. are cryptic. They could represent different attributes of a single entity (e.g. Cost, Markup, Tax), or they could be just different values of the same kind. This difference is critical in the decision how to import the data.

20 hours ago, NikK100 said:

I realize the excel contains a lot of ideas to be implemented

I would prefer to see an example of the raw input you get. And a separate description of the output you expect to get.

 

[NikK100]

Sorry for any confusion.  The Data is RAW data, it has been sanitized for confidentiality.  The word "Description" is just a sanitized version of the descriptive name given to the column or row name.

 

eg Column - Description 1 = "Spend this Qtr"

Row description might be "Engine Widgets"

 

I appreciate the help as always.

 

[Ocean West]

At first glance to get the data to summarize once imported into FM and to sub-summarize it you will need to have a few more columns

Week (perhaps week of year or week of month)

The account number will need to be broken up to additional key fields first two digits and next two digits if your desire is to summarize by these. 

If the intent is to only summarize IF there is more then ONE of each sub set of accounts then this gets more complex as FM will summarize 1 item or 100 and for each sub account and that may appear confusing. 

It may be better to have a separate report table that is generated by a scrip that iterates thru the records and summarized on the desired break points and conditionally depending on the count of each part of the sub accounts. 

 

[NikK100]

Thank you all for your continued help.  I am experimenting with splitting the account codes and summarizing.  My next question, having all the data in one table will I be able to do calculations based on the average of column 1 and the corresponding account code of column 5 in the current week?