Prior Balance Field Possible?

[Corey H]

I'm going to give this a go--and give as much background as possible without boring you.

Table I-Client Data

Table II-Job Data (Includes charges and payments)

I run reports/invoices that have the current due and running balances for the found sets. If I run June's report and there is a [balance due] at the end of that month (or whatever timeframe is involved), then I would want to be able to include that [balance due] plus the new balance due in the July report--but without showing all the June records.

I had this working in FM7 but in a VERY round-about way with many many fields keeping track of a balance and lots of if/then statements in calculations that made running these reports take 3-4 minutes.

With FM9, I'm thinking this would be easier but I'm not sure how to go about it. Would this be a calculation field or a container field?

Two options I'm trying but don't know how to get to:

1) a calculation that gets the "amount due"(charges-payments) from each job; another field (container?) that looks up the "amount due" from the previous job; another field that adds them together. Problem is that Job# field won't be consecutive. Different clients will have random job#s in the table.

2)I'm thinking an additional table may be necessary with the fields above--but linking to something poses dillemas for me.

Does anyone have any experience with this type of thing? Is it possible without the LONG functions of 15 year if/then statements? Perhaps there's an FM plugin?

Thanks!

Corey

[Søren Dyhr]

Since the job data table is most detailed, or rather the many side of the relation, is it most convenient to make summary report there, and the way you avoid all the details is to remove the body part of your reporting, leaving only the summary fields and the category/breaker fields to remain on the layout.

that looks up the "amount due" from the previous job; another field that adds them together. Problem is that Job# field won't be consecutive.

Wouldn't it be better to solve it via running totals (summary fields), since calc's exploiting say GetNthRecord( is much much tougher to make dealings on found set only? ...CF'ing comes to mind here - and the impact on CPU use is unfortunately not neglectable!

--sd

[comment]

Isn't the previous balance the sum of all client's charges/payments whose date is before report start date?

[Corey H]

Correct--the prior balance field would be from numbers OUTSIDE of the report area. So the report covers "July" and I need the prior balance as of "June" somewhere in there. Does that make sense?

I had this going in FM7 but basically had a storage field for every month of every year starting in 2002 going through 2015 like this:

If (MonthYear="72008"; Payments; 0)--having set up a number for each month of each year.

Then, the prior balance field calc was like this below. Needless to say, even on a nice and fast Mac, it took 3-4 minutes to process this calculation. I'm thinking there must be a better way to do this. Are there other functions available in FM9 that could do this better?

If (MonthYear="12003";0;

If (MonthYear="22003";Teal Clients::Balance 103;

If (MonthYear="32003";Teal Clients::Balance 203;

If (MonthYear="42003";Teal Clients::Balance 303;

If (MonthYear="52003";Teal Clients::Balance 403;

If (MonthYear="62003";Teal Clients::Balance 503;

If (MonthYear="72003";Teal Clients::Balance 603;

If (MonthYear="82003";Teal Clients::Balance 703;

If (MonthYear="92003";Teal Clients::Balance 803;

If (MonthYear="102003";Teal Clients::Balance 903;

If (MonthYear="112003";Teal Clients::Balance 1003;

If (MonthYear="122003";Teal Clients::Balance 1103;

If (MonthYear="12004";Teal Clients::Balance 1203;

If (MonthYear="22004";Teal Clients::Balance 104;

If (MonthYear="32004";Teal Clients::Balance 204;

If (MonthYear="42004";Teal Clients::Balance 304;

If (MonthYear="52004";Teal Clients::Balance 404;

If (MonthYear="62004";Teal Clients::Balance 504;

If (MonthYear="72004";Teal Clients::Balance 604;

If (MonthYear="82004";Teal Clients::Balance 704;

If (MonthYear="92004";Teal Clients::Balance 804;

If (MonthYear="102004";Teal Clients::Balance 904;

If (MonthYear="112004";Teal Clients::Balance 1004;

If (MonthYear="122004";Teal Clients::Balance 1104;

If (MonthYear="12005";Teal Clients::Balance 1204;

If (MonthYear="22005";Teal Clients::Balance 105;

If (MonthYear="32005";Teal Clients::Balance 205;

If (MonthYear="42005";Teal Clients::Balance 305;

If (MonthYear="52005";Teal Clients::Balance 405;

If (MonthYear="62005";Teal Clients::Balance 505;

If (MonthYear="72005";Teal Clients::Balance 605;

If (MonthYear="82005";Teal Clients::Balance 705;

If (MonthYear="92005";Teal Clients::Balance 805;

If (MonthYear="102005";Teal Clients::Balance 905;

If (MonthYear="112005";Teal Clients::Balance 1005;

If (MonthYear="122005";Teal Clients::Balance 1105;

If (MonthYear="12006";Teal Clients::Balance 1205;

If (MonthYear="22006";Teal Clients::Balance 106;

If (MonthYear="32006";Teal Clients::Balance 206;

If (MonthYear="42006";Teal Clients::Balance 306;

If (MonthYear="52006";Teal Clients::Balance 406;

If (MonthYear="62006";Teal Clients::Balance 506;

If (MonthYear="72006";Teal Clients::Balance 606;

If (MonthYear="82006";Teal Clients::Balance 706;

If (MonthYear="92006";Teal Clients::Balance 806;

If (MonthYear="102006";Teal Clients::Balance 906;

If (MonthYear="112006";Teal Clients::Balance 1006;

If (MonthYear="122006";Teal Clients::Balance 1106;

If (MonthYear="12007";Teal Clients::Balance 1206;

If (MonthYear="22007";Teal Clients::Balance 107;

If (MonthYear="32007";Teal Clients::Balance 207;

If (MonthYear="42007";Teal Clients::Balance 307;

If (MonthYear="52007";Teal Clients::Balance 407;

If (MonthYear="62007";Teal Clients::Balance 507;

If (MonthYear="72007";Teal Clients::Balance 607;

If (MonthYear="82007";Teal Clients::Balance 707;

If (MonthYear="92007";Teal Clients::Balance 807;

If (MonthYear="102007";Teal Clients::Balance 907;

If (MonthYear="112007";Teal Clients::Balance 1007;

If (MonthYear="122007";Teal Clients::Balance 1107;

If (MonthYear="12008";Teal Clients::Balance 1207;

If (MonthYear="22008";Teal Clients::Balance 108;

If (MonthYear="32008";Teal Clients::Balance 208;

If (MonthYear="42008";Teal Clients::Balance 308;

If (MonthYear="52008";Teal Clients::Balance 408;

If (MonthYear="62008";Teal Clients::Balance 508;

If (MonthYear="72008";Teal Clients::Balance 608;

If (MonthYear="82008";Teal Clients::Balance 708;

If (MonthYear="92008";Teal Clients::Balance 808;

If (MonthYear="102008";Teal Clients::Balance 908;

If (MonthYear="112008";Teal Clients::Balance 1008;

If (MonthYear="122008";Teal Clients::Balance 1108;

If (MonthYear="12009";Teal Clients::Balance 1208;

If (MonthYear="22009";Teal Clients::Balance 109;

If (MonthYear="32009";Teal Clients::Balance 209;

If (MonthYear="42009";Teal Clients::Balance 309;

If (MonthYear="52009";Teal Clients::Balance 409;

If (MonthYear="62009";Teal Clients::Balance 509;

If (MonthYear="72009";Teal Clients::Balance 609;

If (MonthYear="82009";Teal Clients::Balance 709;

If (MonthYear="92009";Teal Clients::Balance 809;

If (MonthYear="102009";Teal Clients::Balance 909;

If (MonthYear="112009";Teal Clients::Balance 1009;

If (MonthYear="122009";Teal Clients::Balance 1109;

If (MonthYear="12010";Teal Clients::Balance 1209;

If (MonthYear="22010";Teal Clients::Balance 110;

If (MonthYear="32010";Teal Clients::Balance 210;

If (MonthYear="42010";Teal Clients::Balance 310;

If (MonthYear="52010";Teal Clients::Balance 410;

If (MonthYear="62010";Teal Clients::Balance 510;

If (MonthYear="72010";Teal Clients::Balance 610;

If (MonthYear="82010";Teal Clients::Balance 710;

If (MonthYear="92010";Teal Clients::Balance 810;

If (MonthYear="102010";Teal Clients::Balance 910;

If (MonthYear="112010";Teal Clients::Balance 1010;

If (MonthYear="122010";Teal Clients::Balance 1110;

If (MonthYear="12011";Teal Clients::Balance 1210;

If (MonthYear="22011";Teal Clients::Balance 111;

If (MonthYear="32011";Teal Clients::Balance 211;

If (MonthYear="42011";Teal Clients::Balance 311;

If (MonthYear="52011";Teal Clients::Balance 411;

If (MonthYear="62011";Teal Clients::Balance 511;

If (MonthYear="72011";Teal Clients::Balance 611;

If (MonthYear="82011";Teal Clients::Balance 711;

If (MonthYear="92011";Teal Clients::Balance 811;

If (MonthYear="102011";Teal Clients::Balance 911;

If (MonthYear="112011";Teal Clients::Balance 1011;

If (MonthYear="122011";Teal Clients::Balance 1111;

If (MonthYear="12012";Teal Clients::Balance 1211;

If (MonthYear="22012";Teal Clients::Balance 112;

If (MonthYear="32012";Teal Clients::Balance 212;

If (MonthYear="42012";Teal Clients::Balance 312;

If (MonthYear="52012";Teal Clients::Balance 412;

If (MonthYear="62012";Teal Clients::Balance 512;

If (MonthYear="72012";Teal Clients::Balance 612;

If (MonthYear="82012";Teal Clients::Balance 712;

If (MonthYear="92012";Teal Clients::Balance 812;

If (MonthYear="102012";Teal Clients::Balance 912;

If (MonthYear="112012";Teal Clients::Balance 1012;

If (MonthYear="122012";Teal Clients::Balance 1112;

If (MonthYear="12013";Teal Clients::Balance 1212;

If (MonthYear="22013";Teal Clients::Balance 113;

If (MonthYear="32013";Teal Clients::Balance 213;

If (MonthYear="42013";Teal Clients::Balance 313;

If (MonthYear="52013";Teal Clients::Balance 413;

If (MonthYear="62013";Teal Clients::Balance 513;

If (MonthYear="72013";Teal Clients::Balance 613;

If (MonthYear="82013";Teal Clients::Balance 713;

If (MonthYear="92013";Teal Clients::Balance 813;

If (MonthYear="102013";Teal Clients::Balance 913;

If (MonthYear="112013";Teal Clients::Balance 1013;

If (MonthYear="122013";Teal Clients::Balance 1113;

If (MonthYear="12014";Teal Clients::Balance 1213;

If (MonthYear="22014";Teal Clients::Balance 114;

If (MonthYear="32014";Teal Clients::Balance 214;

If (MonthYear="42014";Teal Clients::Balance 314;

If (MonthYear="52014";Teal Clients::Balance 414;

If (MonthYear="62014";Teal Clients::Balance 514;

If (MonthYear="72014";Teal Clients::Balance 614;

If (MonthYear="82014";Teal Clients::Balance 714;

If (MonthYear="92014";Teal Clients::Balance 814;

If (MonthYear="102014";Teal Clients::Balance 914;

If (MonthYear="112014";Teal Clients::Balance 1014;

If (MonthYear="122014";Teal Clients::Balance 1114;

If (MonthYear="12015";Teal Clients::Balance 1214;

If (MonthYear="22015";Teal Clients::Balance 115;

If (MonthYear="32015";Teal Clients::Balance 215;

If (MonthYear="42015";Teal Clients::Balance 315;

If (MonthYear="52015";Teal Clients::Balance 415;

If (MonthYear="62015";Teal Clients::Balance 515;

If (MonthYear="72015";Teal Clients::Balance 615;

If (MonthYear="82015";Teal Clients::Balance 715;

If (MonthYear="92015";Teal Clients::Balance 815;

If (MonthYear="102015";Teal Clients::Balance 915;

If (MonthYear="112015";Teal Clients::Balance 1015;

If (MonthYear="122015";Teal Clients::Balance 1115;

"You have entered dates outside the range of this database!" )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) ))))))))))))))))

[comment]

Whoa. That's not pretty.

Couldn't you put the report's start date in a global field of the Clients table, and define a relationship that shows all client's charges/payments whose date is less than this global? The previous balance would be simply the sum of all related records from this relationship. With many records, that too may take some time to compute, but it would be better than what you have now, I think.

A better solution would be to store the balance after each report - either as a number in the client's record, or in a separate table of Reports (one record for each client/month).

[Corey H]

Whoa. That's not pretty.

[color:red]You ain't kidding!

A better solution would be to store the balance after each report - either as a number in the client's record, or in a separate table of Reports (one record for each client/month).

This idea is something I'm trying--but not so sure how to go about it. I imagine a script would need to be written instead of a calculation?

The problem lies in that if the report isn't run every month for every client, then the number isn't stored. I'd need to figure a way to make sure the number is stored regardless of whether the report was run. But either way a separate table is going to be needed I think. Any suggestions on how to populate that table?

If I'm also reading you right, wouldn't that also mean that the new table would have to have a separate field for each month of each year also? Seems like it's getting back to the GIANT number of fields and for a lot of records again--which would slow things down a great deal--or would it?

Thanks for the help!

Corey

[comment]

wouldn't that also mean that the new table would have to have a separate field for each month of each year also?

No, of course not. The months are not important here. It can work the same way with any reporting period, fixed or flexible. The only thing that matters is when was the last report (of this client) produced. The next report takes that date as its starting date, and the closing balance as its opening balance. Just like a bank statement, really.

And yes, it needs to be scripted - and scripted carefully, so that you don't accidentally produce duplicate or overlapping reports.

[Corey H]

This is where it gets dicey--the reports aren't run on a regular basis, so one could be run for January, but not again until June--with a lot of activity in between. June's statement should reflect the balance of May, but if a report wasn't run for May (or April, March, February), then the only Prev. Bal. on record will be for January. Am I reading that right?

So my other thought was to have on each job# a field that looks up the balance at the time of each job and stores it with that job#. Then I could add/subtract in the reports. The dilemma comes in as to how do I look up something from a previous record to insert into a container (?) field on a later record? Does that make sense?

Thanks!

Corey

[comment]

June's statement should reflect the balance of May, but if a report wasn't run for May (or April, March, February), then the only Prev. Bal. on record will be for January. Am I reading that right?

Yes. If you want to work with stored balance, than the latest balance that is avaliable will be the one that was stored last. But I am puzzled why "June's statement should reflect the balance of May" if there was no statement for May. Don't the clients expect to see continuity in their statements?

my other thought was to have on each job# a field that looks up the balance at the time of each job and stores it with that job#.

I don't particularly like this method - it stores a lot of redundant data, and if a record is edited or deleted, there's a lot of house-keeping to do.

[Corey H]

Yes. If you want to work with stored balance, than the latest balance that is avaliable will be the one that was stored last. But I am puzzled why "June's statement should reflect the balance of May" if there was no statement for May. Don't the clients expect to see continuity in their statements?

Well--normally you'd think that the case--but it's a Psychotherapy practice billing db. The clients really only get reports when they need them or they're asked for.

I don't particularly like this method - it stores a lot of redundant data, and if a record is edited or deleted, there's a lot of house-keeping to do.

I recognize that this is redundant data, but I think getting this data would probably save the enormous amounts of time it takes to run these other LARGE equations and storage fields. The question still remains for me, though--how does one pull the data from a previous record into the current? Can't figure that out. Anyone?

Thanks!

Corey

Edited September 7, 2008 by Guest
Corrected quotes (LaRetta)

[comment]

The clients really only get reports when they need them or they're asked for.

If that's the case, why not have a relationship that shows client's previous month's items? That way you can have last month's balance available "live" in the client record.

how does one pull the data from a previous record into the current?

This can be done through a self-join relationship based on ClientID being the same and SerialID being less (related records need to be sorted by SerialID, descending).

[Corey H]

AH!! Yes--this is where I'm trying to go. Being not as experienced as I'd like to be, though, I'm not sure how to go about this. Would this mean a new table, a calc field or a container field?

[comment]

It would mean putting another occurrence of the items table on the relationship graph, and defining a new relationship between it and the clients table as:

Clients::ClientID = Items 2::ClientID

AND

Clients::gStartDate ≤ Items 2::Date

AND

Clients::gEndDate ≥ Items 2::Date

gStartDate and gEndDate would be set to the beginning and end of the previous month, respectively. A calculation field in the Clients table =

Sum ( Clients2::Amount )

is the previous month's balance.

However, this doesn't include the balance of the months before the previous month, so I don't know how useful it would be. Suppose it's June now, and I have an outstanding debt from January, and no activity in May - should my previous balance really be zero?

IMHO, you should include ALL previous months in this calculation, or - if this proves too slow - move to a stored balance method.

[Corey H]

So I took a couple of days to see if I could wrap my head around this better. One thing I was successful at doing was implementing a storage field that takes a snapshot of the balance at the time that job# is created. It works pretty well--but I see what you mean if there is a deletion of a record. For the most part it wouldn't be a problem, but it could get messy if someone decided to delete or readjust previous entries a great deal. So--I'm back to trying to do this as a calculation somewhere--also--the db has already been in use, so the snapshot idea would only work from this point forward unless I want to go in and re-enter each record (and I don't!).

You said above:

gStartDate and gEndDate would be set to the beginning and end of the previous month, respectively

How would I set those start/end dates to the prior periods? I'm unclear on that one.

Thanks!

Corey

[comment]

You are not giving me a starting point. How do you determine the CURRENT report period? Is there a field somewhere that holds this information?

[Corey H]

You are not giving me a starting point. How do you determine the CURRENT report period? Is there a field somewhere that holds this information?

No--the reports are run off scripts with Finds/Sorts so they are run simply off the Date field--which holds the date of service. That Date field is in the Job# record, which is linked to the client data (Client table) via a ClientID. Does that make sense?

THANKS!

[comment]

I'm afraid not. How does your script know which month you want to report on (and therefore which records to find)?

[Corey H]

I'm afraid not. How does your script know which month you want to report on (and therefore which records to find)?

Ah! I see what you're asking. The script enters find mode, goes to a screen where I input the dates of the report and the client's name. I have a couple different reports where I either find the Month and Year or I do the "1/1/08...1/31/08" range in the Date field. Either way--it's basically the same idea--a date range of a kind or another. Is that better? Thanks for the help!

[comment]

I see. It would be a lot easier if you entered this into a global field, because it needs to be re-used. You could enter any date within the reported month, and calculate the search criteria AND the boundaries for the previous month from this.

Otherwise you'll need to tell your script to do a Modify Last Find, so that you can grab the starting date and calculate the start/end dates of the previous month from that.