• Registered: 2016-07-28
  • Posts: 1,026

Topic: Calculated field formula

Hi All,


I need a help with calculated field formula syntax.
calcField:
Running balance= ((startingBalance)+(AmountWin)+(PositiveSwap))-((NegativeSwap)+(Commission))


Please see the attached sample project file:

Post's attachments

Attachment icon CalcField.zip 41.45 kb, 303 downloads since 2019-05-12 

Adam
God... please help me become the person my dog thinks I am.

2 Reply by derek (edited by derek 2019-05-12 20:54:25)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,670

Re: Calculated field formula

Hi Adam,
Try doing it like this (see attached)
Looking at the inputs, I was surprised that journal.amountloss isn't included in the calculation - I took a guess that it should be (but if not, just remove it from the calculated field formulae. 
I also assumed that the 'amountwin' in your post is journal.amount?
Your calculated field is called cfrunnigbalance (not cfrunningbalance) but I haven't changed it in case you're using it elsewhere.
Lastly, with calculated fields, you need to do tablegrid refreshes as soon as the underlying data has changed, so I added that to the script.
Everything seems to add up but I'm not really sure what I'm checking - LOL!
Regards,
Derek.

Post's attachments

Attachment icon CalcField.zip 375.93 kb, 350 downloads since 2019-05-12 

  • Registered: 2016-07-28
  • Posts: 1,026

Re: Calculated field formula

Hi Derek,


Thank you very much for your kind help.....................


Account calc field is fine. Commission is an expense, my bad as it wasn't made clear, but that's OK, I corrected it.
However, journal one should be actual running balance, not just totals. Each line balance would be affecting the next line balance.

Adam
God... please help me become the person my dog thinks I am.

4 Reply by derek (edited by derek 2019-05-13 10:46:39)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,670

Re: Calculated field formula

Hi,
A running balance calculation at the journal line level is, essentially, the total of all journal transactions (pluses - minuses) up to that date.  But since you don't have a date field on the journal table , I don't see how you can perform the calculation.
One other point  - do you really need a starting balance on the account table?  Why not make it the first entry in the journal table (with a description of 'starting balance');  it would simplify the calculated field and all of the information could be displayed in form1.tgjournal.  Just a thought.
Derek.

  • Registered: 2016-07-28
  • Posts: 1,026

Re: Calculated field formula

Thanks a lot Derek...


Sorry, I forgot to include transaction date, which exist in actual project.


I understand, the running balance but translating it in to MVD syntax is not that easy for me. Using sum for journal calc field  on table settings gives the running balance on footer. I wanted to display on each row. You can add the date field and add the formula or just give the formula with assumed date field here would be great. If you cannot get around to do; no worries, I'm still and will always be grateful for your kind help.

Adam
God... please help me become the person my dog thinks I am.

6 Reply by derek (edited by derek 2019-05-13 15:41:05)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,670

Re: Calculated field formula

Hi Adam,
Form1.tgjournal should now be showing the running balance at the row level.  Form1.tgaccount shows the current balance as it did before.
You can enter journals for earlier dates (or same date but different times) than the most recent journal and the formula will automatically recalculate the running balance for each row.
Derek.

Post's attachments

Attachment icon CalcField2.zip 376.55 kb, 329 downloads since 2019-05-13 

  • Registered: 2016-07-28
  • Posts: 1,026

Re: Calculated field formula

Hi Derek,


Great stuff as always.... Thank you so much...........
Truly appreciated..........

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2016-07-28
  • Posts: 1,026

Re: Calculated field formula

Hi Master of calculated fields - Derek,


Added a new table and fields. As a result needed new calc fields. Tried but couldn't get it working.
Your kind help would be appreciated.


Please see the attached sample project:

Post's attachments

Attachment icon CalcField 3.zip 46.26 kb, 299 downloads since 2019-05-18 

Adam
God... please help me become the person my dog thinks I am.

9 Reply by derek (edited by derek 2019-05-19 18:15:40)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,670

Re: Calculated field formula

Hi Adam,
I'm rather confused as to what you're trying to achieve now.  Can you answer a couple of questions -
1.  Are you holding a starting balance against the account or is the starting balance the first entry in either the transactions or the journals tables?
2.  Why do you have separate fields for transaction date and transaction time on the transactions table  (the journal table uses 1 field (date/time)?
3.  Isn't a journal (positive or negative) just another transaction, in which case, why have 2 separate tables?
Question 3 is particularly important because if you split things over 2 tables, do you then want to see one running balance for journals and a second running balance for transactions - and if so, does the starting balance get included in the calculation for the journals running balance or for the transactions running balance (or both or neither)?  Surely you can only have 1 running balance.
Or do you want to see the running balance at any point in time using the starting balance + all of the activity from the journals table up to that point + all of the activity from the transactions table up to that point - so the running balance will always show the same whether you're looking at the 'journals' tablegrid or the 'transaction' tablegrid?   If so, I can imagine users constantly switching between the two trying to reconcile the amounts.
As always, I favour keeping things as simple as possible and, for what it's worth, I'd go with just two tables (account and transactions) and use transaction types of 'deposit', 'withdrawal', 'journal-in', 'journal out' and 'starting balance'.  Your transactions table will inevitably end up with fields that are relevant to only certain transaction types (but that's no different to how it is now with fields for withdrawals, for deposits etc) but at least it will be easier for everyone to work out what's going on..
Derek.

  • Registered: 2016-07-28
  • Posts: 1,026

Re: Calculated field formula

Hi Derek,
Thanks for the reply.


1. Starting balance is not required. Starting balance would be deposit on transactions section. Without deposit, user cannot add an entry to journals or withdrawal entry to transactions. I couldn't formulate it correctly.
2. No specific reason. Just for SQL queries as I don't know the script for both. It can be one field.
3. Journal entries slightly differ from transactions. However, in essence they both affect to account they belong to as additions or submissions which would make the running balance of the account.


Transaction on Transactions grid will be minimal, while transactions on journal is ongoing on daily basis. Think of journal entries as buying and selling activity, while deposits and withdrawals are providing capital for trading and taking out profit etc from the account.


Please fell free to change anything you see feet.

Adam
God... please help me become the person my dog thinks I am.
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,670

Re: Calculated field formula

Hi Adam,
I'd probably do it something like the attached (using just one table for all transactions) to simplify the calculation of the running balance and showing all the transactions on just one grid. 
In my example, I've only shown the gross running balance and not the net because I'm not sure what fields count as additions and what count as subtractions and what your exact formulae should be.  But the logic is the same and the syntax is there for you to amend the calculated fields as suits your requirement..
Hope this helps,
Derek.

Post's attachments

Attachment icon calfield4.zip 381.84 kb, 386 downloads since 2019-05-20 

  • Registered: 2016-07-28
  • Posts: 1,026

Re: Calculated field formula

Thank you very much Derek.............
Truly appreciated......................


In actual project, i have many more columns per grid, so I need to separate them. I'm trying to do calcs with script rather than calc field as I'm totally useless on calc fields.


Once again thank you very much for your kind help.

Adam
God... please help me become the person my dog thinks I am.