Post

4 followers Follow
0
Avatar

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

https://help.podio.com/hc/en-us/community/posts/203573268-Summing-up-columns-in-multiple-items-and-keeping-a-running-totalhttps://help.podio.com/hc/en-us/community/posts/207465898-Help-with-IF-statement-and-SUM-formula

*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

John Penyah

Please sign in to leave a comment.

16 comments

0
Avatar

Not sure I understand, but have you tried with just `@Sum of Balance` (vs. a for loop that seems to sum `@All of Balance` values).

Adrien BLAISE 0 votes
0
Avatar

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

John Penyah 0 votes
0
Avatar

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_balance

In 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

 

Rainer Grabowski 0 votes
0
Avatar

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;222

If we get that I think it would solve it, right?

 

Kind regards,

John

John Penyah 0 votes
0
Avatar

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.

Rainer Grabowski 0 votes
0
Avatar

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.

John Penyah 0 votes
1
Avatar

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

Rainer Grabowski 1 vote
0
Avatar

I see, thank you so much Rainer!

Can you shed me some light on how to do it with globiflow, is this going to the right direction

John Penyah 0 votes
0
Avatar

Your appoach is wrong. The flow in App 1 must be:

  • Trigger: When a new item is created in App 1

Actions:

  • Update item
    (Reference field which points to app 2) =  search for item in App 2 where title equal to the item title 
Rainer Grabowski 0 votes
1
Avatar

 

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.

 

John Penyah 1 vote
0
Avatar

For clarification: In which app is the relationship field which connects both apps and what's the name of this relationship field?

Rainer Grabowski 0 votes
0
Avatar

I actually have 2 Relationship fields setup. One from App 1 relating to App 2 and one from App 2 relating to app 1. Both fields are named "Relationship" the default name

John Penyah 0 votes
1
Avatar

I always try to avoid vice versa relations, in my opinion it's a bad app setup and leads to irritations. I would recommend to remove the relationship field in app 2.

The Flow in App 1 should look like this: 

Rainer Grabowski 1 vote
0
Avatar

Hi Rainer!

Sorry for the late reply, I got it to work already, thank you very much for the help! Much appreciated (y)

John Penyah 0 votes
0
Avatar

Can anyone (Rainer?) tell me how to create a sum total row at the end of the attached markdown table for the columns "Montant" and "Paiement"? 

Much appreciated :)

 

Richard Hedger (Niton) 0 votes
0
Avatar

Hi Richard,

can you please post your code as text? It's easier for me to modify the code than explaining it.

Rainer 

Rainer Grabowski 0 votes