Dick Levon Posted January 27, 2001 Posted January 27, 2001 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
LiveOak Posted January 27, 2001 Posted January 27, 2001 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
Dick Levon Posted January 28, 2001 Author Posted January 28, 2001 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
LiveOak Posted January 28, 2001 Posted January 28, 2001 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
Dick Levon Posted January 28, 2001 Author Posted January 28, 2001 thanks db. Ill give it a try Its clear and appreciated. Dick Levon
Dick Levon Posted February 10, 2001 Author Posted February 10, 2001 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
LiveOak Posted February 10, 2001 Posted February 10, 2001 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).]
Dick Levon Posted February 11, 2001 Author Posted February 11, 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).]
LiveOak Posted February 11, 2001 Posted 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
Dick Levon Posted February 12, 2001 Author Posted February 12, 2001 bd, like my son says, "excellent!" thanks, have a good one. [This message has been edited by Dick Levon (edited February 12, 2001).]
Dick Levon Posted February 12, 2001 Author Posted 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
LiveOak Posted February 12, 2001 Posted February 12, 2001 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
Dick Levon Posted February 17, 2001 Author Posted February 17, 2001 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
Questions King Posted April 14, 2004 Posted April 14, 2004 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?
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now