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?

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

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.

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?

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.

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.

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