Newbies Barnum Posted April 30, 2006 Newbies Posted April 30, 2006 I recently purchased FileMaker Pro Advanced to see if I could rewrite my existing (Helix Rade) database on this product. I have no real computer education so I'm struggling along with the help of a Missing Manual book. I've been getting along well enough with the most of the basic stuff but I've hit a snag writing calculations. I just don't seem to get the syntax right to make FileMaker happy. Here's where I'm stuck: In the checkbook relation of my database all of the debits have a check # that is > or = to 1 All deposits have a check # = 0 I have two bank accounts so there is a ledger for each. I need a running total to show my checkbook balance for each account on their respective ledger layouts. How do I calculate that? I've tried a few variations of this in a calculation field, but they don't work: If ( Check # ≥ 1 and Act. ID #=44) // "too few parameters in this function" Sum ( Check Amt. ) End If I also must calculate the balance of all of the checks where "Cleared Date" is not empty so that I have bank statement balance for each account. Can anyone help me?
mz123 Posted May 1, 2006 Posted May 1, 2006 Try this: If ( Check # ≥ 1 and Act. ID #=44; Sum ( Check Amt. ); "") ----- The if statements are setup as follows: If( BooleanStatement ; TrueResult; FalseResult) So.. an example would be... If( 1=2; "One equals two"; "One does not equal two") The result would be the text "One does not equal two" I think that should work for you! Let me know Martha
comment Posted May 1, 2006 Posted May 1, 2006 I believe you can make this much simpler by using a summary field defined as Total of Amount (running total). Go to a list view, find the the desired transactions (i.e. the correct account and only cleared checks, etc.) and that should be it.
K1200 Posted May 1, 2006 Posted May 1, 2006 I would like to add something to this question. I agree that the summary field (running total) is an easy way to get the bottom line result, but is there a way to cause it to provide a line-by-line running total? For example, in portal rows where the result would appear like a check register: Starting Balance = 200.00 Check ... Balance 10.00 .... 190.00 20.00 .... 170.00 25.00 .... 145.00 The results I've had whenever displaying a running total in a portal is that the field always shows the end result (e.g., 145.00) on every row. Any suggestions?
comment Posted May 1, 2006 Posted May 1, 2006 Well, summary fields weren't really meant to be used in a portal. Nevertheless, it should work, esp. in version 8. See the file attached to my post here.
K1200 Posted May 1, 2006 Posted May 1, 2006 Your example helped me get to the root of my problem. I had defined a global calculation field to hold the differnce of two running totals (summary fields). I changed from Global to Unstored and it shows the correct row-by-row results. Then I added back in a global calculation field (=running balance) to retain the bottom-line result. Thanks for your post.
Newbies Barnum Posted May 4, 2006 Author Newbies Posted May 4, 2006 Martha, I tried your solution and it worked great, I can't thank you enough! I adapted the script to filter out the cleared debits by simply adding a little to it: If ( Check # ≥ 1 and Act. ID #=44 and not IsEmpty ( Cleared ); Sum ( Check Amt. ); "") and so on for credits and summaries to give my bank statement and checkbook balance. I'd be ashamed to tell you how many different ways I buggered up this simple little script. It was very kind of you to help. Thanks, Nick
Recommended Posts
This topic is 7069 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