Conditionally sum up the costs



  • Kent Watson

    Hi Albert depending on how you are storing your PO number it may be easy to do with a standard report. What type of field are you storing the PO number as?

    Comment actions Permalink
  • Rainer Grabowski

    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 = 1


    1. 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)
    2. Get Podio View from App PO request / view "Last Month"
    3. create variable "Sum": 0
    4. for each (Ref) PO quest
    5. IF/Sanity Check: [(Ref Request) PO Number] != "PO001" && [(Ref Request) Approved?] == "Yes"
    6. create variable "Sum": [(variable) Sum] + [(Ref Request) Total]
    7. end if
    8. continue
    9. Search for items in App PO Request where Title equal to [(variable) month]
    10. IF/Sanity Check: [(Ref Request) Podio Item != null
    11. Update collected from App "Report" in number/money field: [(variable) Sum]
    12. end if
    13. IF/Sanity Check: [(Ref Request) Podio Item == null
    14. create item in App Report
      Title = Value: [(variable) month]
      number = Value: [(variable) Sum]
    15. 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.
    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++){
    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.


    Comment actions Permalink
  • Albert Zhang

    OMG Rainer, it's so kind of you to provide these great solutions! Yes, you're right PO Number is a text field. I've got to try the Globiflow one. It takes time for me to get used to Globiflow, but I can feel it's powerful functions! Thank you very much Rainer, have a good weekend!

    Comment actions Permalink
  • Albert Zhang

    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.

    Comment actions Permalink
  • Albert Zhang

    Hi Rainer,

    And in option A, at the 11th step, "Update collected Request(s) in App", I can't find the App "Report" from the drop list, there is only "Ref (Request) from PO Reqest" available to select.

    Comment actions Permalink
  • Rainer Grabowski

    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.)


    Yes for "Yes".

    "Ref (Request) from PO Reqest"  is the right one to select.


    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk