How do I create a balance forward from one month to the next for billing statements? I have a private psychotherapy practice. I can create monthly statements for each of my clients. I can not seem to conceptualize how to create a way to bring amount due from previous month to current month.

any help or direction is greatly appreciated.

thanks

This is a "how do I replace the fuel filter on my car" question. It kind of depends on what model car you have. In this case, how you implement a balance forward depends upon how you have implemented you database file structure. A very general answer is to use a relationship. -bd

thanks bd. I realize I was being very general. Im not sure how much detail to include in this. I consider myself a beginner at this. Ive been working with FM for 3 years. This is really the first time Ive ever tried to create something like this.

Im using FMP 5 on a PC

I have a relational data base called "private practice". It has 2 main files,"client records" and "transactions". Client ID# defines the relationship between files. Client records contain the demographics, diagnosis, treatment plan, insurance info.,etc. Each Client's info is a record.

Transactions contains the money stuff, dates of service, type of service, debits, credits, etc. Each transaction entered is a record.

The problem I have is: in creating monthly billing statements I can not conceptualize how to create a balance forward for each separate client from one month to the next, say Jan to Feb. without having to display all records from the previous month.

I can sort all transaction records by client ID and dates of service, and I can display amount due in a GetSum field. Amount due is calculation of Summary fields (Debits - Credits)I think in order to get the amount due for Feb. I have to display all records from start date 1/1/2001 thru 2/28/2001, and for Mar. thru 3/30/2001. and so on. I dont want all these records showing up on each months statement.

Should I be writing a script to do this? It seems to me thats the way to go. I can write scripts to find, sort, display, etc. records for seperate months. but each month does not have the balance forwarded from the previous month.

thanks

Dick Levon

ps I did visit your website. thanks

First, you are probably creating your reports from the transactions files using subsummaries. To get a balance forward, we'll use relationships. First, create a few fields, I'll assume each transactions has a field "Date":

MonthYearIndex (calculation, text, indexed) = Year(Date) & Month(Date)

LastMonthIndex (calculation, text, indexed) =

Case(

Month(Date) = 1, (Year(Date) - 1) & "1",

Year(Date) & (Month(Date) - 1)

Create a self relationship from the transactions file to the transactions file called SelfByLastMonth:

Relationship with LastMonthIndex matching MonthYearIndex

Create a field (assuming the transaction $'s are a field called TransAmount):

LastMonthTotal (calculation, number) =

Sum(SelfByLastMonth::TransAmount)

This last month total field will always show the total of transactions for the previous month for the record you are on. It is not dependent upon preview mode or placement in a subsummary. If placed in a subsummary by month, it will show the previous months total.

Hope this is clear.

-bd

thanks db.

Ill give it a try

Its clear and appreciated.

Dick Levon

bd

Ive been working on the balance forward stuff. I appreciate the direction. Im not able to get the result I hoped for. Im following your directions exactly. In your last message to me you mentioned to create a field LastMonthTotal (calculation, number) = Sum(SelfByLastMonth::TransAmount)

Is "TransAmount" a summary field?

I have a Debit field, and Credit field, and Balance Due. Debit - Credit = Balance Due.

Is "TransAmount" a summary of Balance Due?

I think this might be the key, or not!

Thanks for your valued expertise!

Dick Levon

TransAmount is not a summary field. I assumed that in you transaction file you would use one field "TransAmount" for both credit and debit transaction amounts and just have two types of transactions "credit" and "debit". In this case all you need to do is add up all the transactions for the correct client for the correct period of time. These transactions are isolated by the relationship. The sum() function adds up all the TransAmount entries in the related records. If you have separate credit and debit fields the calculation becomes:

LastMonthTotal (calculation, number) =

Sum(SelfByLastMonth::Credit) - Sum(SelfByLastMonth: :Debit)

-bd

[This message has been edited by LiveOak (edited February 12, 2001).]

thanks bd.

Ill work with that.

Another problem is that when I create the field LastMonthIndex (calculation, text, indexed) = Case(Month(Dates) = 1, (Year(Dates) - 1) & "1", Year(Dates) & (Month(Dates) - 1) I get a message that I have more of ( than ). I think I have is correct?

thanks for your generous help.

Dick Levon

ps Im using a PC with Windows95.

[This message has been edited by Dick Levon (edited February 11, 2001).]

[This message has been edited by Dick Levon (edited February 11, 2001).]

I'm glad I'm not the only one that can't find the missing ")"!

You need one more ")" at the very end to close the Case( statement.

-bd

bd,

like my son says, "excellent!"

thanks,

have a good one.

[This message has been edited by Dick Levon (edited February 12, 2001).]

bd,

I created the fields and relationship and the result is the total of all clients and balance due from the first of the year to the present.

I want to show the balance forward for each separate client for each month.

Did I miss something?

thanks

any help is greatly appreciated

Dick Levon

You need an index that is a "client-month". For instance:

iClientMonth (calculation, text, ASCII index) =

ClientID & "-" & Month

for each credit or debit record. This would match an index in clients:

ClientID & "-" & Last Month'

If you use the "-", make sure to set the language of the index to ASCII. I use the "-" to make sure Client 11 Month 1 is not confused with Client 1 Month 11 (with ClientID & Month both are 111!).

-bd

bd,

thanks. I am definitely in way over my head, but that's how I prefer to learn. I'll be chewin' on this one for a while.

Your expertise is greatly appreciated!

Dick Levon

LiveOak & Dick Levon,

From I know this only show last month balance forward, how about if I find by this month, I want to show all past of the month balance forward?

Month 1 $1000

Month 2 $500

Month 3 $800

i.e - if I find by month 2, the balance forward only show $1000)

i.e - if I find by month 3, the balance forward only show $500(should show $1500)right?

What wrong I did?