Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 7515 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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

Posted

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

Posted

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

Posted

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

  • 2 weeks later...
Posted

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!

smile.gif

Thanks for your valued expertise!

Dick Levon

Posted

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).]

Posted

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. laugh.gif

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).]

Posted

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 smile.gif

any help is greatly appreciated

Dick Levon

Posted

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

Posted

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

smile.gif

  • 3 years later...
Posted

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?

This topic is 7515 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

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