Jump to content
Sign in to follow this  
MLink

Too Many Calculation Fields? (Or: my database hangs a lot)

Recommended Posts

Hi everyone,

I've been working on this database for a few months (never used FM before, all self-taught) and now I'm putting in all the functionality to provide a month-by-month breakdown of the financial side of things.

To do this I am using summary fields that read through entries in two other tables (Invoices and Expenses) to determine how much of each relates to each month. I then have set up a whole series of stacking calculations which determine how much of each was present at each month, taking into account the previous months. And so on and so forth.

All in all I've created about 70 calculation fields to handle all of those. There's probably an easier way, I'm sure. It all works pretty fine, except when I try to add the calculation fields for recognised revenue. I can add a few of them, but when I put in the formulas for every single month, the whole database lags out. So badly in fact, that I have to end the process in the Task Manager.

I can post it up if I clean it up enough first, but the basic premise I want to know is: is there a point at which too many calculation fields will kill the database, and have I reached it? Do any of you have experience with this sort of thing, and how did you solve it?

Even hints as to how to debug it would be greatly appreciated, as would all replies. Thanks in advance.

Share this post


Link to post
Share on other sites

The short answer is yes, especially if it's 3rd party hosted over the 'net, especially if you've got many calcs that reference different relationships.

70 calc fields sounds like too much.

Share this post


Link to post
Share on other sites

Hi DJ, thanks for the reply! Sorry, I would have said something sooner but I didn't get an email notification about it. Odd. It is all locally hosted though, so there's no net connection slowing it down.

Anyway I've made a few changes to the way it works to hack out a lot of dead wood, and I'm down to less than 50 fields. The reason there are so many is because it does a month by month breakdown. So for each month (12 months in total) there must be a field for:

- Revenue for that month (a summary field collated from another table)

- Expenses for that month (also a summary field collated from another table)

- Recognised Revenue for that month (this is the field that is causing problems, see below)

- Unrecognised Revenue for that month (the difference between revenue recognised to that point, and total revenue to that point)

The problem with Recognised Revenue is that it needs to be capped at a maximum equal to the unrecognised revenue for the previous month. So for this, I've been using a Min() function, specifically

Revenue Recognised for any given month = Min ( (Potential amount to recognise based on revenue and expected profit); (Unrecognised revenue for previous month)).

However as soon as I add this Min checking, the whole thing wigs out. I can only assume this is because it is somehow doing a circular calculation with the Unrecognised Revenue amounts for each month.

It's a very strange situation. I'll post up a more detailed explanation soon, but I have a few more possibilities to try before screeching at the moon.

Share this post


Link to post
Share on other sites

So for each month (12 months in total) there must be a field for:

No it's time to realize that it should be a "record", even-though FMI marketing have promised you that filemaker begins when you grow out of spreadsheets. You need to approach the entire thing relational if your vanity dictates to see this live. But the proper approach would very likely be a sub-summary report, ditch the spreadsheet'ish approaches!

--sd

Share this post


Link to post
Share on other sites

Well, I'm only going with this "spreadsheetish" approach because that's all I know how to do! See earlier where I wax lyrical about how I'm entirely self-taught. But your advice is well noted and I thank you.

How would I go about doing it with records? I would either need

a) To somehow create 12 entries in the month breakdown table, each with the same project id but for a different month, every time a new project was made, or;

B) Have 12 different monthly breakdown tables, and create a new entry in each one with the correct project id, every time a new project was created.

I wonder if there is a script to do this? I will have a play around but would appreciate any suggestions.

Share this post


Link to post
Share on other sites

If you haven't already, read up on Summary parts, sub-summary parts, and sorting by script. Most of the time you don't need data streaming live (which is kinda what unstored calcs are), you just need snapshots, maybe at certain intervals, but often just at a users whim. That's what the reporting functions of Filemaker do.

Share this post


Link to post
Share on other sites

determine how much of each relates to each month. I then have set up a whole series of stacking calculations which determine how much of each was present at each month, taking into account the previous months.

Perhaps you could make us a sample/template of the method you establish these figures in your present solution??

...and BTW your solution could perhaps benefit from you seeing this:

--sd

Share this post


Link to post
Share on other sites

Here is a short description of the problem and my model. I'd upload a file but there's too much data and I don't have time to remove stuff that I shouldn't be putting in the public domain.

There are three tables that are relevant here.

- Invoices, and

- Expenses, both of which link to the main table:

- Projects

A Project can have many Invoices and many Expenses, but each Invoice and Expense only related to one Project.

All of what I am going to discuss takes place in the Projects table.

There are four groups of fields in the Projects table that I am working with, and there are twelve instances (one for each month) of each field in these groups. So there are 48 fields in total.

The first two groups of fields relate to Invoices and Expenses.

Each Invoice, and each Expense, has a Date, and an Amount. Using this date, I have set up summary fields that use this Date to collect the total monetary amounts of both Invoices and Expenses for each month in the current calendar year. So, there are 24 fields to play with there.

- Expenses for Jan in Current Year

- Expenses for Feb in Current Year

[..etc..]

- Expenses for Nov in Current Year

- Expenses for Dec in Current Year

- Invoices for Jan in Current Year

- Invoices for Feb in Current Year

[..etc..]

- Invoices for Nov in Current Year

- Invoices for Dec in Current Year

You get the idea.

The third field is Recognised Revenue. Each Invoice generates Revenue for the Project, and Revenue must be Recognised by Expenses. I need to know how much Revenue is recognised in each month. The catch is that you can never recognise more Revenue than you currently have unrecognised. The formula for this is:

Min ( (Expenses for any Month / (1 - Expected Profit for the Project)); Amount of Unrecognised Revenue from Previous Month)

This means that it will always recognise only as much as is available. If the amount to be recognised is ever greater than the amount available to recognise, it will default to the cap. So we have 12 more fields:

- Revenue to Recognise for Jan in Current Year

- Revenue to Recognise for Feb in Current Year

- ... etc to Dec

The fourth and final field is Unrecognised Revenue. Once you have Recognised Revenue, you need to know how much is left. The formula for this is:

(Unrecognised Revenue for Previous Month + Revenue for this Month) - Revenue to be Recognised for this Month

Which gives us another 12 fields:

- Unrecognised Revenue for Jan in Current Year

- Unrecognised Revenue for Feb in Current Year

- ...etc to Dec

This seems simple enough. And it works. Everything EXCEPT the Revenue to Recognise fields.

Whenever I ask FileMaker to use the Min function (or any other function, I've rewritten the formula a few times) to calculate the amount of revenue to Recognise, the whole database crashes and wigs out, and I am forced to end the process. I can only assume I am somehow doing a circular reference that is making the whole thing loop on itself until death. Each month's Revenue to Recognise uses the Previous Month's Unrecognised Revenue, and so on, so theoretically the whole calculations should cascade down from January until all the fields have been completed. But instead I get the whole database hanging.

I am sure my calculation logic is correct, so I can only assume the error lies with the way I am implementing the solution.

As it stands, these 48 fields are all present inside the Projects table, and are live and calculating for every record in that table.

I have tried making a new model where there were 12 new tables, one for each month, and inside each of these tables there were 5 fields: Project ID, Invoices for this Month, Expenses for this Month, Revenue to Recognise for this Month, and Unrecognised Revenue for this Month. This STILL collapsed on me as soon as I added the logic for the Min function to recognise the correct amount of revenue.

I am utterly and completely stumped, after throwing myself at this for the last two weeks. There must be a way to handle this, the calculations involved are not that taxing. B)

Please, if any of you took the time to read this, let me know if I'm going about this completely* the wrong way. All thoughts and comments deeply appreciated.

Share this post


Link to post
Share on other sites

I haven't taken the tim to read the whole thread, but I agree with Soren that a relational database design would remove the need for the complicated and contrived calculation fields.

The Invoice table should relate to an Expense table.

"Each Invoice, and each Expense, has a Date, and an Amount."

OK so you have that....

"Using this date, I have set up summary fields that use this Date to collect the total monetary amounts of both Invoices and Expenses for each month in the current calendar year."

Ok I start to get worried here.

The idea is to do the reporting from the Expenses table. Add a calculation field to work out the month and year, then find the records for the desired time period, sort by the month/year field and display the results in a summary report.

Another thing: don't try to get *all* of the information out in one report. Sometimes it cannot be done. But it can be done with multiple reports, even if the "report" is really just four or five lines on a page.

Share this post


Link to post
Share on other sites

Hi Vaughan,

Thanks for the comment, and the advice. I am pretty much willing to try anything at this point to get it working.

The one problem is that it is really desirable to have this information available instantly and dynamically for any project that is viewed. If the users need to print out a report to see this information every time then they will get pretty frustrated.

It is not enough to find records for a desired period and then sort them, it would be ideal for the user to be able to simply go to the Finances tab in the layout and have this information presented to them straight away. This would be the way I would really like to do it, but I also completely understand if it turns out it has to be done with reports instead of in a regular layout.

I also can't see any reason for Expenses and Invoices to be linked, and I don't really understand how this would help. Can you please explain a bit further?

The real problem I have, the only problem that is holding me back, is that the calculation to determine how much revenue can be recognised seems to wig out the whole database. I can only assume this is because I am going at the whole thing the wrong way. Even if I were to make this into a report that was printed out, it would still wig out completely while calculating these amounts. The worst part is I'm not even sure if changing the database model would help with this.

Sorry if that didn't help very much, let me know and I'll elaborate more. Thanks again.

Share this post


Link to post
Share on other sites

Neither have I yet found the time to scrutinize your mission statement, but you need not print a summary report at all, turning into preview is all it takes.

But there is alternatives to it, you can do it this way:

http://www.kevinfrank.com/download/kf-fast-summary.zip

The worst part is I'm not even sure if changing the database model would help with this.

Don't confuse effort with method, you have taken the tool for being a spreadsheet, and have probably been struggling a lot to make the metaphor work as you would have expected it to, without humbling yourself into the recognition that normalization as method addresses the problems you've experienced exactly!

What kind of test have you made to learn if the fields you have in the tables actually belongs to it or actually should have been broken out??

--sd

Share this post


Link to post
Share on other sites

Ok lets take this one first:

Each Invoice, and each Expense, has a Date, and an Amount. Using this date, I have set up summary fields that use this Date to collect the total monetary amounts of both Invoices and Expenses for each month in the current calendar year. So, there are 24 fields to play with there.

Here is in my humble opinion an entity duplication, the Invoice and the Expense table has identical attributes, and should be merged into the one and same table, the "Type" is in the following upload a only toggled numberfield, but in real life is it a calc'field sniffing if itemlines exist a table away or not. This is the explanation for the way the relation is designed farthest to the right in relational graph.

But by and large is there only evaluated 6 aggregate functions max what ever pane should be selected, and these are very very fast to evaluate since the relations also strains records which could be candidates to summarize.

I have included a toggle in the summarizing panes to switch between the projects data and all data in the time span. This might be overkill for this purpose, since the calc' :

Min ( (Expenses for any Month / (1 - Expected Profit for the Project)); Amount of Unrecognised Revenue from Previous Month)

Makes the entire focus the project as such.

Now I havn't made any tinkering on this yet, but as such is just a slight change to the sandwich-layer table to consist of 13 records instead to carry December from the previous year over - this means that January then is record 2 and not record 1 as it is at present.

But I'm a little in doubt if the approach is sufficient when you deal with Unrecognized Revenues, since some of the Unrecognized Revenues could be carried over quite some time ... legislation in my neck of the woods, demands to keep records for the previous 5 years, this could suggest that these figures should be autoentered or a scripted record creation in the InvoicesOrExpenses table instead via a selfjoin, each time a new record is created. Otherwise will GetNthRecord( be quite heavy to update if these unrecognized matters are carried from long ago!

I will tomorrow give this a stab, but you need to explain the likelyness of these chained carry-over from several month ago, since it's probably what makes you solution slow as molasses ... the recursivity or chained dependency this issue courses.

It's probably solved in the vicinity of this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

--sd

projects.zip

Share this post


Link to post
Share on other sites

Ah! There's another catch to it, we tried with near immense measures to convince Pascal Dimopoulos in this thread:

http://fmforums.com/forum/showtopic.php?tid/185302/post/244892/#244892

.... that measuring things in month is daft:

The problem is not solving, but what to solve. Objectively, there is no correct answer to your question. You have to make some arbitrary assumptions, for example is Mar 31 to April 30 a full month? If yes, is Mar 30 to April 30 a full month? If not, then how can Mar 1 to April 1 be a full month?

We apparently never succeded - Since he later uploaded his apporach to:

http://www.briandunning.com/cf/799

This is unfortunately an attack on your "unrealized revenues", there is logically no such thing when based on months. However are individal dates between each transaction, probably the way to solve it. But what figure to display then, is it the last transaction before the end date of the month or?:

--sd

Share this post


Link to post
Share on other sites

Hi Soren,

First of all, thanks for the replies! I really appreciate it, and the time you've taken to respond.

Addressing some of your points: Expenses and Invoices have more attributes than I established up there - they are not identical and really do need to be separated. What I was trying to show was that for the purposes of this collection of data they both share some fundamental similarities.

I'm also afraid I don't see how measuring things by month is daft. This is the way financial systems have to work for the books to be accurate. As Expenses and Invoices have a date attached, this is the date that they occurred and regardless of how many days are in a month or how you measure a month, that month that that date belongs to is where they must be sorted.

Unrecognised revenue does exist and is a real concept that needs to be addressed in my solution. At the end of each year it is easy enough to store the unrecognised revenue because a simple calculation on the total for the year will be enough to generate the same result as if calculated individually for each month. The problem is that each month needs to be addressed individually throughout the year. At the end of each month, the accountant needs to be able to view this information to know how to adjust the bank ledgers appropriately. At the moment it is all done using Excel which is horribly clunky :

I'll attach two screenshots so you can see what I mean about recognised and unrecognised revenue.

The first screenshot, correct.jpg, shows how it should be. You can see that as revenue comes in in March through Invoices, and there are no Expenses, it goes straight into unrecognised revenue, and it carries on through to July, when an expense comes in. This allows us to recognise a portion of revenue, which is almost all that is available, leaving $0.04 unrecognised to the end of the year.

The second screenshot, incorrect.jpg, shows where my system collapses. In this screenshot we are carrying over unrecognised revenue from the previous year. As you can see in February, as soon as a large expense comes in, the formula recognised more revenue than is available, and puts us $81.75 into the negative, where it *should* cap out the recognised revenue at $12,453.25 as that is all that is available for the previous month. This later swings back into the positives, then the negatives again. Ideally, unrecognised revenue can *never* be negative as it should only be possible to recognise as much revenue as you have available.

These screenshots show the recognised revenue column without the capability to check against the amount of revenue currently available to recognise. As soon as I add that function, the Min() function described above, to make sure recognised revenue caps out when needed, the whole thing collapses.

Realistically I think I'm just going to have to bite the bullet and do it as a report. The only problem I see with this is how to have a field which, when viewed as a report, calculates recognised/unrecognised revenue for each of the 12 months. I guess I'll figure out a way!

correct.jpg

incorrect.jpg

Share this post


Link to post
Share on other sites

Ok It should easily be done if previous years realized and unrealized revenues are entered manually, but if should be based on all records from the previous years ... dear me it would scale badly, becasue it would include every record in the solution. I would work smoothly in the beginning, but then will it start plummeting.

Then to the comment's I made about month as categorizer, of course are we trying to make chunks of data into measures we know, but the aim is comparison of the figures from February and perhaps July, which in lucky years provides full 3 workdays more, while other comparisons of February and July only provides 1 workday's difference

...are we any the wiser by reading such figures, we could jump to false assumptions that the workforce behind Februarys figures not are putting enough effort in producing revenue or are they supposed to run faster in February???

If all these numbers are to be taken with the notorious "grain of salt" - for what is it then worth that we put a lot of efforts in making the calc's as exact as posible.

Expenses and Invoices have more attributes than I established up there - they are not identical and really do need to be separated.

Not entirely correct seen from a relational point of view, they have attributes in common right?, while others differ, true - this means that if it's an invoice is it usually contain itemlines which is a one2many relation, other issues could be broken out in a one2one relation such as the details regarding terms of payment if it's invoice.

Similar could a transaction record change role to become a recieved payment ... validations will prevent that a single transaction becomes a bit of both, a transaction record is like the single line in the ledger.

Realistically I think I'm just going to have to bite the bullet and do it as a report. The only problem I see with this is how to have a field which, when viewed as a report, calculates recognised/unrecognised revenue for each of the 12 months. I guess I'll figure out a way!

Yes the following statement prevents you indeed from doing so:

I also can't see any reason for Expenses and Invoices to be linked, and I don't really understand how this would help

How many tables can a summary report be made of, the other figures need to get in there some how, probably referenced via relations in my humble opinion, this is why I suggest the prevention of entity duplication.

Other tools might allow this more conveniently, but with filemaker this is one of the points where you need to squeeze the datamodel!

But basicly however is the move to a genuine summary report not daft at all. Only might it be convenient to run the FastSummary algorithm:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

....ahead of establishing the found set, to initiate each month first record with the previous month realized as well as unrealized revenues. This will be pretty fast to do!!!!

Anyhow, I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships. Anyhow, I just wanted to make sure readers were clear on the best approach to reporting.

--sd

Share this post


Link to post
Share on other sites

I think that up to a point, this could be simplified significantly by having an auxiliary table with 12 records - one for each month of the current year. That alone would cut the number of relationships and calculations by a factor of 12.

I say up to a point, because it seems there is a cascading calculation that needs to carry over its result from month to month. I confess I didn't entirely understand how this is supposed to work. If the count begins from a stored balance of the previous year, I suppose it's still workable - but I don't think we have enough specifics to offer a more detailed solution. Roughly I see it as each month record summarizing its own values, with summary fields in the auxiliary table doing the rest.

Edited by Guest
clarification

Share this post


Link to post
Share on other sites

auxiliary table with 12 records

Ehm?? take a look at the image, or the graph! But anyway a solution could be to make Edoshins algorithm scripted fill each projects latest record with these cascading info, yet anohter Ugo's method will point at the latest if Last( proves to slow things too much, otherwise would the present relations be working just perfectly for the purpose.

The reason why I've chopped the year into quarters was to render as little info as possible.

--sd

projects.jpg

Edited by Guest

Share this post


Link to post
Share on other sites

I know you have hinted at this before - but I thought it got lost in the debate over OP's business rules.

However, I believe we are barking up the wrong tree here (although it's a tree well-worth barking). I tried to make a simple file with a single table of 12 records. I defined the revenue/expenses as plain number fields, then tried to make the two calculations as described. I got a spinning beachball and had to force quit FMP.

I don't know if this is a bug or if there is some circular reference that I cannot see. In any case, I don't understand the logic behind these calculations (why is revenue always recognized at a month's delay?), and I think they deserve further scrutiny.

Share this post


Link to post
Share on other sites

Alright, I couldn't help it - I needed too, to know if I was barking up the wrong tree here, admitted the approach needs tidying up here and there, but the fastsummary algorithm works smoothly with this, allowing me to continue with the sandwich layer approach for the summaries.

I haven't yet come to the implementation of the formula of yours with Min( but as such are we a step further!

--sd

projectsSD2.zip

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.