Budget spreadsheet

[jepar]

I want a "budget spreadsheet" with

(simplified):

1. Columns as month (12)

2. Rows as line items (Opening, Receipt, Sales, Closing)

3. Pages as Account number & Year number

The problem is to have the closing value

of a month appear the opening value of

the next month without creating circular

references!

Closing=Opening+Receipt-Sales

Opening(Month)=Closing(Month-1)

It would be *nice* to use repeated fields.

Cheers

JF

[email protected]

[LiveOak]

If you really only want a spreadsheet, it might be best to use a spreadsheet program. If you want a display in spreadsheet format, you might want to tell us about your existing file structure, it might be possible to do what you wish with relationships. If you already have FM files, your question is kind of like "how do I add a window to my house?". We need to know a little about the house before we can give you an answer.

-bd

[jepar]

Thank you for your reply.

As you have noticed, I am still at the conceptual level. I am trying to approach the problem from a totally new angle.

I currently use a spreadsheet program with *a lot* of custom code to emulate a database. The stability and file size are horrible (there need to be one spreadsheet per page, and the formulas repeated on each one).

I would like to do the opposite, a spreadsheet in a database. Requirements are open, that is why I am writing so little.

Basically, the user needs to see:

- each column as a month.

- each row as a general ledger line statement.- each page as a combination of account/year.

For example, let's have

- B01 to B12 as begining of month value

- R01 to R12 as received inventory

- S01 to S12 as sales for the month

- E01 to E12 as closing inventory

The calculation are (where t is the month.):

E(t) = B(t) + R(t) - S(t)

B(t) = E(t-1)

I can create a database with the following fields:

- Year

- Account

- B (repeated, 12)

- R (repeated, 12)

- S (repeated, 12)

- E (repeated, 12)

It is very stable but I can't create the calculations!

[LiveOak]

I think you are worrying about what color to paint the interior trim of the house you are building before you have decided where to build it, what the floor plan with be, and what the structure should be!

Worry a little less about how you wish to display the data and tell us exactly what overall task you are trying to accomplish and exactly what data you need to store. The MOST important question for an FM file is "What is a record?" (i.e. what does each record represent). How to create a report and how to do a particular calculation may seem important, but they are just details compared to planning the overall structure of your system.

-bd

[jepar]

Thank you again for your answer.

What follows tries to explain my issue a bit further. This is written material and I have no way to know how it is perceived. Please excuse me in the case it sounds too dry: I am trying to give a lot of concise information. I appreciate the time you are spending on this.

Since you like to illustrate your point by analogies, I'll make one too!

My problem is to build an interface that suits the users. A few years ago, I was working in Africa and drilling water wells. I found out quickly that I can work all night to increase the yeild of a well by 5% but if it doesn't take into account all of the villager's needs, it will be useless. I found out that the location of the well was more important than it's yield because no-one would walk under the heavy sun to get the water, even if it was better.

I understand your point: what is a record? I see that all the time too, and sometimes it is not even funny.

I work with Oracle on Solaris and DB2 on AS400, designing databases, queries, views, reports "ad nauseam". If it was only a question of records, I would write this database on our back-end system. However, I am looking for a GUI environment that will will have very low latency. Otherwise, no-one will drink that water.

I am asking about the paint because I will not even start building a house if I can't get one the color I need. I want to us FM pro for the user interface it provides.

I could write this with Access, with a lot of VB code (and I haven't ruled that out yet). But I quite like how a problem can be solved elegantly with FM. However, FM is a challenge: you've got to have the right approach, because there is no code to fix, to patch your problems afterward.

So, here again the requirements:

A record is the smallest group of non-redundant information that has to be tied together.

Records will be identified by two indexes, the year and the account number. It could go as low as the month but I am pretty sure that this will have a negative impact on latency (12 times more joins).

The display is important, because it's how people think. Each column has to be a month: financial statements work with time as columns. There is no way around that.

The same is true for the rows. There are four rows: Opening, receipt, sales, closing. And the calculations have to carry acrross. It has the running-sum problem that a lot of queries have.

This is very challenging, and I haven't seen it done yet on FM.

Again, thank you for your time!