Conditionally sum up the costs
Hello everyone,
This picture is the overview of our app, called PO request. We want to add up the approved total cost for the items by month. Also, the items with a PO Number of "PO001" should be excluded from the calculation. I have Globiflow, but I don't know how to run the calculation. Can anyone help me? Thank you!
-
I assume PO Number is a text field. So you can't filter for it and can't use a standard report. This means, you need another app (a Report app).
You have 2 options:
Option A)- Create a report app with a title field (text) for the month name and a number field or a money field.
- In the PO Request app create a View "Last Month" with filter "Date Requested" = last month
Then in Globilflow create a flow "By date or day" (at the top of list with workspace names).
Trigger: Every day at 00:15
Filter: If day of month = 1Actions:
- create variable, name it "month" and enter: date("F Y", strtotime([Current Date (Y-m-d)])
(everything in square brackets is a token you have to select from the token list) - Get Podio View from App PO request / view "Last Month"
- create variable "Sum": 0
- for each (Ref) PO quest
- IF/Sanity Check: [(Ref Request) PO Number] != "PO001" && [(Ref Request) Approved?] == "Yes"
- create variable "Sum": [(variable) Sum] + [(Ref Request) Total]
- end if
- continue
- Search for items in App PO Request where Title equal to [(variable) month]
- IF/Sanity Check: [(Ref Request) Podio Item != null
- Update collected from App "Report" in number/money field: [(variable) Sum]
- end if
- IF/Sanity Check: [(Ref Request) Podio Item == null
- create item in App Report
Title = Value: [(variable) month]
number = Value: [(variable) Sum] - end if
This way on each 1. day of a month you create an item for the previous month. For starting you need to gather the data for all previous months for the extisting PO Requests. This can be done with a modifyed flow.
Option B)
- Create a report app with a title field (text) and a calculation field and create only one item, name it "PO Request Report"
- In the PO Request app add a relationship field which points to the Report app
Then create a Manual triggered flow in Globiflow in the App PO Request.
Action:
Update this Request in the relationship field = search item in App Report where title equal to PO Request Report.Save the flow and start it manually by clicking "Run" (in the dropdown field besides the "Edit" button). This links all exiting Requests with the Report.
Then modify your flow. Change the trigger to: When item created.
Now when a new Request is created this Request will automatically be linked to the Report.Next Step: In the calculation field in the Report app enter a code like this:
var approved = @All of Approved?;
var po = @All of Po Number;
var date = @All of Date Requested;
var total = @All of Total;
var months = [];
for(var i = 0; i < date.length; i++){
months.push(moment(date[i]).format("YYYY_MM_MMM");
};
monthsUnique = months.filter(function(item, pos) {
return months.indexOf(item) == pos;
});
var list = [];
for(var i = 0; i < monthsUnique.length; i++){
var sum = 0;
for(var j = 0; j < date.length; i++){
if(monthsUnique[i] == moment(date[j]).format("YYYY_MM_MMM") && approved[j] == "Yes" && po != "PO0001"){
sum += +total[j];
}}
list.push(monthsUnique[i] + " | " + sum);
};" Month | Sum \n" +
" --- | --- \n" +
list.sort().join("\n").replace(/_.._/g," ")This will create a nice table with 2 columns (Month and sum) for all existing months with the newest months at the top( first row).
The problem in your case is that in many items the field PO Number, the field Total and/or the field Approved? is empty. This will lead to wrong results. But a) Podio is working on a solution for the handling of empty fields in for loops and b) you can avoid it by some workarounds or by your app design.Rainer
rg@delos-consulting.com -
Hi Rainer,
Are option A and B two separated options? Or I have to combine them together?
For the option B, I can't save the script. Podio says:"
Script syntax error: Unexpected token ; "
In the script, since the "Approved" field is a category field, can we use "Yes" in the Script? Thank you.
-
Hi Albert,
A and B are separated options.
For the error notification: Sorry, I've forgotten a bracket at the end in this line (2 brackets at the end.)
months.push(moment(date[i]).format("YYYY_MM_MMM"));Yes for "Yes".
"Ref (Request) from PO Reqest" is the right one to select.
Rainer
Please sign in to leave a comment.
Comments
6 comments