Calculation using using a conditional category field.
Answered
Hello, I'm no expert with the calculations field, but so far I managed to get it to sum all the expenses from another app. Would it be possible to make the sum condition to a specific category fields? Let me try to explain:
I have an expenses app with the following fields:
Money field named Value
Category field named Situation with the following options: Paid and Open.
Would be be possible for me to make my calculation field on the other app to use the category field from my expenses app as a conditional: As in only sum the Money field value if the category field is marked as open
I'm not sure I made myself quite clear, but any help is much appreciated. Thanks.
-
Wow! It works perfectly. Would it be possible for me to add a currency symbol before the number? My previous formula was like this:
"BRL"+(Sum of Valor).toFixed(2)
BRL = Currency Symbol and .toFixed(2) is the mandatory 2 decimal cases
Once again, thank you so much for your help! Cheers.
-
Hi Hamid, sorry to bother you again.
I just realized there may be something wrong with the code:
I just added a new expense (with the value of 438,05) and marked it as "Paid", still, it's being added (summed) to calculation field. So, instead of just showing the amount for the unpaid expense value of 1.9, I'm actually getting the total value of 439,95, even though that should not be the case.
The code is exactly the one you posted.
-
No problem. I've done this already, still it doesn't seem to be working. On the calculation's field, whether i change the final string of the code to:
cat.indexOf("Open") >= 0 ? val : 0
or
cat.indexOf("Paid") >= 0 ? val : 0
I'm getting the same result, even though there are 2 expenses added, and one is marked as paid and the other as open, as previously demonstrated.
Any idea what I might be doing wrong?
Thanks for the prompt feedback.
-
Hi, just some feedback from using the code as provided. It works great. But there seem's to be a little bug, nothing major. For every other item that don't have an expense reported, either as open or paid, The formula make shows a sum of 1. As if there were 1 unpaid money (whichever currency used).
-
They way I wanted to do it is to total up the sum if it were categorized with 'Order Fulfilled' or 'Order Placed'. I got it to work for me using this code. Hope it works for anyone else looking to do this. I am not a programer by any means so this may not be the best way or other use cases.
var val = @Order Total;
var cat1 = @Order Status;
var cat2 = @Order Status;
(cat1.indexOf("Order Fulfilled") >= 0 ? val : 0) + (cat2.indexOf("Order Placed") >= 0 ? val : 0) -
Hi Justin,
if the category field is single choice you don't need `.indexOf()` (only necessary for multi choice category fields) and the whole code could be a bit shorter:
var val = @Order Total;
var cat = @Order Status;
cat == "Order Fulfilled") || cat == "Order Placed" ? val : 0|| is used in Javascript for OR (&& is for AND)
Rainer
-
hi!
I've tried using the second script but it's not working for me:
var val = @all of Value; var cat = @all of Situation; var total = 0; for(var i = 0; i < val.length; i++){ if(cat[i] == "Open"){ total += val[i]; } }it says the script give and undefined result? Any thoughts on what might be wrong?
-
Hi Rainer (and all),
I was able to successfully use the formula below that you had provided for one condition, but I'd like to expand it to two conditions - for example, if a category field Approved = Yes and if another category field Paid = No. How can I modify it for two conditions? Thanks in advance for your help!
var val = @all of Value; var cat = @all of Situation; var total = 0; for(var i = 0; i < val.length; i++){ if(cat[i] == "Open"){ total += val[i]; } };
total || 0 -
Does anyone know if this can be adapted to sum values on related items, but only from the related items with dates greater than and less than two dates on item A?
Item A is an account with two dates, Start of This Month and End of This Month
Items B are transactions on the account and I want to have a field on the account that sums the value of only the transactions with a transaction date that falls within the current month (i.e. between those two dates on the account).
It seems this calculation you guys are working on, here, could easily be adapted though I have very limited knowledge of javascript.
Additionally, I'm looking to hire a full-time Podio developer who is proficient with javascript, Globiflow, and API development.
If anyone has a connection for me (could be yourself) please do share. :) -
Hi Ronson,
you're right, it can easily be adapted.
var start = @start date;
var end = @end date;
var valTrans = @all of transaction value;
var dateTrans = @all of transaction date;
var sum = 0;
for(var i = 0; i < dateTrans.length; i++){
var date = moment(dateTrans[i]);
if(date >= start && date <= end){
sum += Number(valTrans[i]) || 0;
}};
sumFor the full-time Podio developer please contact me: rg@delos-consulting.com
Rainer
Please sign in to leave a comment.
Comments
37 comments