Sum of two columns and get the running balance of the sums
From the picture and the post title I think you got my problem right. Well, I'm having trouble getting the running balance, I can calculate the sum of two items but I can't figure out how to get the running balance of the sums. We have an app where we want to input a WITHDRAW amount and DEPOSIT amount and the balance which automatically sums up everything.
I tried creating another app(APP 2) where I tried to get @All of Balance from relation to APP 1 but it doesn't seem to be working. I've read all posts related to my question specially these 2 below but it doesn't seem to work
*Also, I tried to output the value of @All of Balance.length and it returned the value 1, doesn't seem to be right, right?
Angels of Podio Forum please help! :|
-John
-
Hi Adrien!
I just want to get the running balance of the sum of the deposit and withdraw fields, for example:
DEPOSIT | WITHDRAW | BALANCE
---------------------------------------------------300 | 0 | 300
400 | 0 | 700 ( 300 + 400 )
300 | 0 | 1000 ( 700 + 300 )
0 | 200 | 800 ( 7000 - 200 )
I know it's possible because it's just simple calculations, I just don't know how :(
I have tried the @Sum of Balance but it only outputs the Balance field value, even tried to output Sum of Withdraw and Deposit, they're not giving out the Sum at all
-
Hi John,
it's not a simple calculation. Cause each App 1 item must have an indirect or direct relation to all preceeding items (like in a spreadsheet with 3 columns where column 3 calculates the running balance). You can go 2 ways.
First way:
In App 1 add a relationship field which points to App 1 itself. Then reference in each item all items which have an older date. So each new item has one more relation than the previous one. Cumbersome to do that manually, but it can be done automatically by Globiflow (but keep in mind: Podio allows max 299 related items in an outgoing relationship field). Then in the calculation field "Balance":(@sum of Deposit - @sum of Withdraw) + (@deposit - @withdraw)
Second way:
Create App 2 with one item, reference each App 1 item through a relationship field in App 1. In the calculation field "For Balance-Check" in App 2:var date = @all of date;
var deposit = @all of deposit with nulls;
var withdraw = @all of withdraw with nulls;
var result = [];
for(var i = 0; i < date.length; i++){
DATE = moment(date[i]).format("YYYYMMDD");
DATE = Number(DATE) || 0;
DEPOSIT = Number(deposit[i][0]) || 0;
WITHDRAW = Number(WITHDRAW[i][0]) || 0;
BALANCE = DEPOSIT - WITHDRAW;
result.push(DATE + ";" + BALANCE);
};
result.sort().join("|")According to your screenshot in your last posting the result is a string: 20170212;300|20170213;-500|20170214;800
Cause in your screenshot some of the fields Deposit and Withdraw don't have a value you have to take the variable token "@all of deposit with nulls" at the end of the token list (not the "normal" @all of deposit).Then in the field Balance in App 1:
var str = @All of For Balance-Check.toString().split("|");
var date = moment(date).format("YYYYMMDD");
var date = Number(date) || 0;
var value = (@deposit - @withdraw) || 0;
var checkBalance = 0;
for(var i = 0; i < str.length; i++){
checkDate = str[i].split(";").shift();
checkDate = Number(checkDate) || 0;
checkValue = str[i].split(";").pop();
checkValue = Number(checkValue) || 0;
if(checkDate < date);
checkBalance += checkValue;
}};
running_balance = checkBalance + value;
running_balanceIn App 2 you also can create a nice report table in a calculation field (which will look similar to your table in your last posting).
Rainer
rg@delos-consulting.com -
Hi Rainer!
Wow! Thank you so much for the helpful input, really appreciate it!
I have tried the 2nd step, however there seems to be a slight malfunction.
I think it just adds the current value to itself. I tried outputting the Balance-check string and it just one for each item, shouldn't it output all the item's data combined like - 20170301;800|20170302;-300|20170303;500|20170304;222If we get that I think it would solve it, right?
Kind regards,
John
-
How are the relations? It seems you've related only 1 item from App 1 to one item in App 2. But all App 1 items which you want to inlcude in your running balance must be related to only 1 (!) App 2 item. In other words. You need only 1 item in App 2 which is referenced by all App 1 items.
-
What's happening is I created a flow on App 1 where whenever an App 1 item is created, an App 2 is created too. So yeah, a one to one relation is happening. I thought the code - " @all of date" would get all the existing dates on App 1 and connect it to my item on App 2, sorry.
So what I'm gonna do now is disable the flow and manually create an App 2 item where I will relate all items in App 1 on the Relation field, right?
Future question in case this works. Is there a way to automatically relate all existing items from one App to another App? Because I kind of want to automate everything and not create an App 2 item every time I want to sum everything up.
-
Hi John,
" @all of date" gets only those which are related to the item where the calc field is in.
So what I'm gonna do now is disable the flow and manually create an App 2 item where I will relate all items in App 1 on the Relation field, right?
Right.
Is there a way to automatically relate all existing items from one App to another App?
For existing items you can do that with an excel export/re-import. For automating everything (e.g. when a new app 1 item is created, add a relation to the app 2 item) you need Globiflow.
Rainer
rg@delos-consulting.com -
So I went to globiflow, created a trigger on Item Created on App 1( Banking App )
But I got lost on the update part. It doesn't seem to allow me to update on App 2( Test balances ) for the App 1 trigger.
So now, my million dollar question is - How can I append the relationship of my item( titled Sum ) on App 2( Test balances ) every time I create an Item on App 1?
Because I can already sum up everything, thanks to you of course! You're awesome! Just need to automatically sum it up by automatically adding a relationship to every new item on App 1 to my App 2 item.
Please sign in to leave a comment.
Comments
16 comments