Topic: Calculation Ledger Balance

Hello All,

I have attached a sample project where I am having an issue with the calculation field. As you can see Form 1 will show the balance due for all clients collectively, but when you complete a search by name or date the balance does not change. Is there a way to show the balance for each client individually? This way when I create my report I can show the client his payment history and balance.

Post's attachments

Attachment icon Sample Ledger Project.zip 335.42 kb, 172 downloads since 2021-08-22 

2 (edited by derek 2021-08-22 21:21:36)

Re: Calculation Ledger Balance

Hi,
You need to hold the calculation against the 'clients' table and not the 'payment' table.
Also I noticed that you have separate search buttons for your 'date' search and your 'clients' search.  Unless you have a specific reason for doing that, you only need to have one search button and you can hold both the date and the client search components on the same search action.
Writing your calculated field as you suggest (but held against the clients table) gives you the 'total' balance per client (which may be fine for what you want to do) but it doesn't show you the 'running' balance (ie the balance at the time of the transaction). 
This is where putting both the date and the client search components on the same 'search' button comes into play as you can search both for a specific client (ie Jennifer) and also for a specific range of dates (ie 11/08 to 20/08) and you can see that the 'running' balance is 23.5 but the 'total' balance shows 13.5 (because it includes transactions that fall outside of the selected date range).  In this instance, looking at the 'total' balance rather than the 'running' balance can be somewhat misleading.
All of which sounds way more complicated than it is - best just to have a look at the attachment - LOL!
Derek.

Post's attachments

Attachment icon Sample Ledger Project suggestion.zip 338.19 kb, 231 downloads since 2021-08-22 

Re: Calculation Ledger Balance

That works perfectly.. Thanks Derek

Re: Calculation Ledger Balance

in this example.
If i want to calculate the 'running' balance on the same day, how i do it?
Can you show me how.
Thanks Derek

Re: Calculation Ledger Balance

Hi,
Have a look at the attached as an example.
The script is just for cosmetic stuff (colouring the grid etc etc).
The actual calculation of the running balance is done purely by calculated field.
Derek.

Post's attachments

Attachment icon runningbalance 666.zip 341.4 kb, 174 downloads since 2021-10-09 

Re: Calculation Ledger Balance

derek wrote:

Hi,
Have a look at the attached as an example.
The script is just for cosmetic stuff (colouring the grid etc etc).
The actual calculation of the running balance is done purely by calculated field.
Derek.

Thanks Derek