Script report by date range, but need previous date range included

[Corey H]

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

[LaRetta]

Hi Corey,

We need a bit more clarification about the find criteria. Please say again what the final data should be:

[color:blue]I have a script that finds records by a date range (i.e. June, 2008)

Is this ALL session records for the month of June - even if paid?

[color:blue]I need to include, however, any prior unpaid balance.

So you will end up with all session records for June, 2008 plus any unpaid session records from prior months? Then the report should sort and sub-total by client?

It sounds to me like you would find all sessions for specific month/year. Then EXTEND your found set to also include outstanding balances (all). But the necessary details are still too abstract to pin down your needs. You wouldn't be using a container; rather a sub-summary report should do the trick. Can you EXTEND a bit yourself on what the final report should look like and also answer my questions above? :smile2:

[Corey H]

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

Edited September 7, 2008 by Guest

[comment]

Why are you repeating an entire thread?

http://fmforums.com/forum/showtopic.php?tid/196952/

[Corey H]

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.

[Corey H]

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

[LaRetta]

Hi Corey,

Are you hard-coding your balance into each Sessions record? What if someone makes a partial payment or pays with one check for more than one session? What happens if someone accidently overpays? How are payments tracked? They should be another table so you can apply multiple payments to one session, right? If this is a cash-only business then the 1-to-1 (one field for payment) will work. I don't know your structure and without a clear picture, I can only guess or make suggestions.

I don't understand what you listed here because none of the figures add up correctly (from what I can tell). Take first line 6/1/08 ... If the fee is $50 and they paid $20 why does it say due $180?

[color:gray]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

Also, you cannot have one record with this information:

[color:gray]Client Jones

Summary for June

Prior Balance: $150

... but you can cheat and restrict a new relationship (called Client Prior Balance) based upon what you enter into global fields (for your search date). If you search for August, it will use the 8/1/2008 date and filter the records to find all 'prior balances' before that date. This is then displayed in the session record in the leading part next to the client name. But again, this is all guesswork without knowing your structure and I'd prefer to see your file. But see if the attached file helps a bit.

As for posting a new thread about it, Comment is right. You should just continue the same thread. It's okay if the method changes (whether calculation or script); we're not that fussy. But I also understand why you thought you should start a new subject.

I hope this file gives you something to work with - it's far from pretty but then I'm not sure it will even help you.

LaRetta

Corey.zip

Edited September 9, 2008 by Guest
Updated file

[LaRetta]

I just realized that you were indicating having a running balance down the list of actual sessions. So I changed my file to add a running balance (in light grey) next to each session record's balance.

[Corey H]

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

[LaRetta]

Well I recognized that the running total I provided (and the solution in general) may be a kinda klutz, folks. In years of designing accounting programs (in more programs than FileMaker), I have never had the need to present information in this fashion and I am still concerned of the original underlying structure.

Corey, if you post a file back here, I hope you will include enough for me to understand your billing structure. Without enough information, I don't know if what I'm suggesting is the best for you and it also takes five times the effort for us to assist someone when we work a bit blind.

LaRetta :wink2:

Edited September 9, 2008 by Guest
Reworded a bit

[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