Corey H

Hey There! I haven't had time to integrate your solution into the current database. When I look at it, it almost seems easier to start over with my DB and integrate your ideas into a new one. This DB is a bit cluttered with custom stuff from 2003 and on. The client asked for many things, then didn't want them, then wanted them back, then wanted them differently. So lots has changed and I think there's a lot of redundancy. As you're looking at this file, click on "Reports" at the top screen and run the "Monthly Client Billing Summary" for July 2008. You'll see what I'm getting at. Thanks for taking a look! Corey Psychotherapy_Billing.fp7.zip

Thanks for the file! This is fantastic! I'm looking into the file and will get back to you. I'm understanding a bit better how a global field can help in this, I think. I need to play with the file you posted and see what I have here. You're correct in that the running balance is also including the previous balance in the reports. I will make a clone of the file and add some dummy records to post here. I'll also try to include what you've taught me with your file. I'll try to get back in a day or so. Thank you SO much! Corey

No offense to anyone--and I do apologize if this is viewed as a double-post for some. I didn't consider this a double-post as I'm going about the Previous balance a different way. The other post in the Calculations forum was looking for a field. That isn't proving to work out. I have run into a dead end there. I have received a LOT of private messages about not double posting. Sorry! Now--is there any help anyone can give on scripting this idea? I'm at a dead end here and need some input. It is greatly appreciated if you can help! Corey

Well-it is a bit of a repeat...but this time I thought I'd try scripting instead of calculation fields. I'm not having any luck with calculations. It seems a script might be the way to go so thought I'd try the script section of the forums.

HI! Thanks for replying. To answer your questions here is more detail. The report gets all sessions in a date range (generally month of a year) for a specific client. I should end up with session records for that specific client for June, 2008, but don't need the detail of the previous unpaid records--just a total of any unpaid previous balance prior to June, but not including June. I would put this at the top of the report. The record details for June include all records--paid or not. So...here's a sample: Client Jones Summary for June Prior Balance: $150 Date Service Fee Payment Due 6/1/08 Session $50 $20 $180 6/7/08 Session $50 $50 $180 6/14/08 Session $50 $20 $210 Total for June: $150 $60 $210 Does this make sense? It's the PRIOR BALANCE number that I'm having trouble getting. Thanks! Corey

I've been trying this a number of ways, but what I'm ultimately after--a Prior Balance Field--keeps eluding me. I've tried large, messy calculations; extra tables with redundant information; fields with redundant information. Unfortunately none are working the way they need to. I have a script that finds records by a date range (i.e. June, 2008) and makes a report for that month. I need to include, however, any prior unpaid balance. I am not having any luck in fields, so I'm trying scripting. I'm thinking this is possible, but lack the scripting expertise to figure this out. Here are details: Psychotherapy Billing database includes: One table with Client Data, One table with Session data (charges, payments, etc.) 1) Script goes to a layout to find jobs by date range. 2) Script goes to a layout to constrain found jobs by client#. 3) Script sorts and goes to a report layout. From here, I'm thinking there must be a way to say something like this: find job < start date from above find with same client# (client name) and sum balance, put that number into a container field and show that container field on the report I'm rather limited in my scripting knowledge past the given commands in the script maker. Does anyone have some help for me that could help me clear this up? Much appreciated in advance. Thanks! Corey

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!

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!

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

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 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

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

[color:red]You ain't kidding! 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

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!" )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) )))))))))))))))))))) ))))))))))))))))

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