For ledgers, I prefer to have a debit field, a credit field, and a calculation field, cBal defined as Debit - Credit. There are alternative methods that use a single amount field with positive and negative values in a single field. If you use that method, refer to that field in place of cBal in the following answers:
Option 1: Define a table of accounts, one record for each account that you link by account ID to your transactions in the transactions table. A calculation field defined as Sum ( Transactions::cBal ) will then return the current balance for that account.
Option 2: Define a summary field in Transactions as the running total of cBal. Select the "restart summary for each sorted group" option and select your Account ID field as the "group" field. In a list or table view layout, if you sort your transaction records by Account ID, you'll see a running total for your current balance that restarts with each account in the found set. (And your found set can be records for one account or multiple accounts).
Option 1 and 2 are not mutually exclusive.
2) I'm not sure that I understand the question. Are you validating the amount entered to ensure that it is consistent with the type of transaction or are you looking up "standard amounts" for specific transaction types? Both are possible.
See this thread for a more detailed look at setting up a "bookkeeping ledger" in FileMaker: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12
Thanks for that file Phil. Looks like it will be most helpful.
The idea behind the Category type is I want to have only one data entry form to enter transactions.
So I will have: TransactionID, Date, To/From Account, Category, SubCategory, Amount, notes etc etc, ActualAmount.
The amount entered will always be positive but I want the Actual Amount to invert to negative if the Category Type is an expense.
This is how the IF expression looks in Access: ActualAmount: Iif([CategoryType]="Expense",[Amount]*-1,[Amount]) .
The same expression and syntax works in FIleMaker as well, just remove the  and replace commas with semi-colons. You can define ActualAmount as a calculation field with a number result type. In Access this column would be defined as part of the data source SQL query, in FileMaker, you define it as an actual field in Manage | Database | Fields.
thanks again Phil but how can i get FM to lookup the Type column though?
Sample Data from Categories table.
CategoryID CategoryName CategoryType Notes
1 Wages Income
2 Bills Expense
So if i'm entering a transaction and i select Wages category, FM needs to know that this is an income cat so do nothing as far as the amount entered is concerned. how do i achieve this?
If you have a table of category types and names. If you link this table by CategoryName or CategoryID to a field in your ledger, there are auto-enter field options that can copy over the categoryType when you select the Category from a value list. If you link by CategoryName, format the CategoryName field in your ledger with this value list, but you can also use a CategoryID field for both relationship and value list such that you select a category by name from the value list, but the value list enters the ID--producing the same results you might get from an Access combo box.
Thanks Phil. I got the CategoryType to work the way I want but I just can't get the If statement to read the field correctly.
i have: If (CategoryType ="Expense" ;Amount *-1 ; Amount) but this always returns a positive amount for some reason.
What data type is the CategoryType field. Check this in Manage | Database | Fields. I'm guessing that CategoryType is a number field instead of text. And if you use a value list to select categories in this table, make sure that "Expense" is the actual value entered into the field for Expense entries. If you have a value list that enters a number code for each category while displaying the names, then your if step needs to check for the number values instead of the quoted text string: "Expense".
Thanks Phil. I've actually removed the numbered 'ID' field from my tables (kept it in transactions though) to resolve any lookup problems i might experience.
In the transactions table, i have the Category as a lookup to the Category table:
on the transactions layout I have the Category using a value list:
(i'm using both columns because some categories can be both an expense and an income type. EG: cash)
and then somehow i have a text box retrieving the correct categorytype:
it works but i don't know how!
but i can't get the IF function to work looking at the categorytype field.
I don't fully follow your description. "I'm using both columns"???
And "text boxes" do not exist in FileMaker. They are layout field objects and each refers to a field defined in a table unlike a VB type "form" or "report" in Access where you can create such a text box that is not bound to any database field.
And in FileMaker, a number field can store non-numeric text, but if you do so, it won't evaluate correctly in a calculation.
Thus, as I have already suggested, you need to check what data type was specified for the field you are using in your calculation. This is something that you would check in Manage | Database | Fields.
And since you describe using "both columns", I have to wonder if the field you have formatted with a value list is the same field you are referencing in this calculation.
Right, got it sorted and working the way i want it Phil. Thanks for the advice.
now for the account balance summary...! Will let you know how i go.
Hey Phil, got the balance thing sorted. I was concentrating on the running balance when i didn't really need to. Not yet anyway...
Anyhoo, I have a new problem now. i have a table of Bills related to Transactions by BillID. What I want to do is:
From my Bills layout I want to open a Bill payment (Transaction) layout and copy the BillID, Payee and amount onto this second layout.
How could I go about this?
So from your Bills layout based on the Bills table, you want to create a new record in Transactions linked by BillID to the current record on your Bills layout.
Set Variable [$BillID ; Bills::__pkBIllID ]
Go to layout [ "Transactions" ( Transactions ) ]
Set Field [Transactions::_fkBillID ; $BillID ]
But use your field and table occurrence names in place of mine.
Thanks again Phil. Problem though:
The new transaction record shows the data from the Bills layout (as per Script) but this data won't save to the transactions table.
I add the date and the account the payment is coming from and these are saved but the 'copied' data isn't. I added a Commit button to try and work around this but it doesn't work.
I don't know what you mean by "this data won't save to the transactions table". Can you describe what you mean in much more detail? This data is most definitely "saved" or you wouldn't be able to see it on the screen, so something is creating the illusion that it is not.
I add the date and the account the payment is coming from and these are saved but the 'copied' data isn't.
Into what are you adding that data? What field, from what table and on what layout?
Best guess is that the data set in fields by the script is in one table and the fields where you are adding this additional data are from a different table, but that's just a guess at this point...