I need my Bank Balance to retain previous record amount.
Can I do this without a Script or is that the best way?
Do you want to store the most recent Bank Balance value in a secondary field in the same record when you edit the balance? Or do you want to reference the most recent Bank Balance of the previous record from the context of the current record? Something else?
Reference the most recent record that holds the last Bank Balance from the context of the current record.
If you have a "Created Timestamp" record, you can form a self-join from table occurrence "Register" to "Register2" such that
Register::Created Timestamp > Register2::Created Timestamp
Then a calculated field "LastBalance" or similar where
LastBalance = If( Register2::CreatedTimestamp = Max( Register2::CreatedTimestamp ) ; Register2::Balance ; 0 )
That should work I think.
If you have an ascending serial number of some kind, you could use that rather than the Timestamp field.
This is what I have.
Just to clarify: I want the $5.00 to be retained in Bank Balance until I write a check for maybe $1.00, giving me a Bank Balance of $4.00. That being retained until I write another and so on.
Go to the relationship graph and duplicate your transactions table. Use an X join to create a self-join relationship between the two transaction table occurrences. You can then use Last( self join::bank balance ) to lookup the previous value of bank balance and use it to calculate the next value.
There is a summary field type of Total ot (with an option to select Running Total).
Show the cumulative total for the current and all previous records. To restart the running total for each sorted group, also select Restart summary for each sorted group and select the field upon which the sort will be restarted from the field list.
While it is a 'calculation-on-the-fly' (a summary field that is calculated based on the found set of records), it may be all you need.
The disadvantage(s): all records must be found to be current, it has to be recalculated, the sort order of the records also may change the "balance" at any given time.
The advantage: If you forgot a check and must insert one, you do not need to go back and recalc/reset the values (auto-enter, perhaps).
Think about what is needed and what will work.
The information in this is too important to take a chance on "maybe it won't work".
I have tried about every combination I can think of and none of the possibilities so far, have gotten me results I need.
The fact that I am not having any luck with this doesn't necessarily mean your different answers won't work, I simply cannot get them to work.
I enter a deposit of say $10. I have a Balance of $10, "until" I enter another deposit. The balance looks at the second deposit and forgets about the first. After all that is what we are trying to overcome. ALSO: If I enter a Check amount after entering the first deposit of $10, I get the correct balance. I have BANK BALANCE minus the AMOUNT of the Check. Everything works until I add another deposit.
Have I done this correctly? I do not have the ACCT# connected between the CHECK REGISTER AND
CHECK REGISTER 3. I did have, didn't appear to make any difference so I removed it.
Perhaps I do wish to do this:
"Do you want to store the most recent Bank Balance value in a secondary field in the same record when you edit the balance?"
If I were doing this I would like the bank balance to be correct for each transaction. That would mean that the bank balance is not the same for each record. It is different for each record.
The attached file shows you one way in which you are able to achieve this.
I agree with your format.
I have over forty accounts with each having a unique Account Number.
My first question with this method. If I use the Deposit Ticket and or the Check Writing Table to enter information, that information is "Passive" in lieu of being entered manually. Will your Bank Transactions table populate?
Also have a General Ledger Table that I need to populate.
While awaiting your kind response, I will experiment with your idea.
My example shows you how to locate the previous record. If you include the Account number as an extra relationship key you'll filter the list to last record of that account. That will enable you to obtain the bank balance from that record. You can then use the bank balance in any way that you wish. I can't really answer your subsequent questions because they are too broad. The answer, broadly, is "yes" but it really depends on what you do and how you do it, so the answer could just as well be "no".
Of course it is great fun to reinvent the wheel but I do wonder why you don't run a dedicated program, such as gnuCash?
You will probably think I have flipped for sure. I actually have a Check writing software that doesn't do a few things I would like.
My wife shakes her head even time I start something like this. She thinks it would be a lot simpler if I just bought what I want to do. Now! That definitely would be no fun. lol
Did you use FileMaker to produce your file? I could type into your form but I couldn't replicate it.
A file with .fmp12 suffix is a filemaker database. The file I uploaded may be opened by v12 and up.
Yes, I knew it was the correct format etc. When I tried to open the "BankTransactions" it was "grayed out".
I got have it now.
Thank you so much,
I have done everything I can think of to make this work for me. Yours works perfectly, mine simply will not. Enclosing several pics.
Sure hate to bother you.
I can see two things.
1. In the first picture, the calculation has been commented out. FileMaker does this when it can't make something work, probably because the "previous transactions" table occurrence didn't exist when you copied the table in. Remove the comment markers from that calculation.
2. The relationship between Transactions and Previous Transactions has been set up wrongly because in my file the join symbol is the not equal to sign. Check the relationship join status and also ensure that the relationship is being sorted.
Retrieving data ...