Calculate the sum of all transactions on a certain date

Comments

7 comments

  • Venom

    Will someone help?

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Venom,

    3 questions:
    1. Are the 2 apps connected via a relationship field?
    2. Do you want to see the results always in the same item in the second app (means: the app has one item only) or do you want/have items for each month?
    3. Do you have a Podio Premium Account and use PWA (Podio Workflow Automation)?

    0
    Comment actions Permalink
  • Venom

    1. Yes. The first application is called "Payment Planning" it has the field amount, date and the field "Instrument" - this is just a related application. Also in this application there is a category field - "Completed" "Cancelled"
    There are two calculation fields in the Tool app.
    In the first one, you need to calculate the amount of the transaction from the status "Completed" for the selected instrument for the current day.
    In the second calculation field, you need to calculate the sum of all transactions on the instrument for the month.
    2. Both calculation fields where the result should be obtained are in the "Tool" application
    3. Yes, we have Podio Premium and we use PWA. But I, since I am new to this, I think that we are not using it effectively.

    0
    Comment actions Permalink
  • Venom

    Hi,  Rainer

    Сan you help me

    0
    Comment actions Permalink
  • Venom

    help me please!

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Venom,
    the main challenge is that the Podio calculation field doesn't know "today" (explanation and solution see my comment here ; that's where you need PWA).
    After you have connected all items in the Tools app with the Timer app item (as described in my comment) you have the today's date available and you  can compare the dates of the Payment items to the current day and month. 
    For the current day you can add the code:

    var today = @all of date field in timer app.toString();
    today = moment(today).tz(your timezone here).format("YYYYMMDD");
    var payment_date = @all of date field in payment app;
    var payment_status = @all of category field in payment app;
    var payment_amount = @all of amount field in payment app;
    var sum = 0;
    for(var i = 0; i < payment_date.length; i++){
         var p_date = moment(payment_date[i]).tz(your timezone here).format("YYYYMMDD");
         if(p_date == today && payment_status == "Completed"){
            sum += payment_amount[i] || 0;
        }
    }
    sum

    For the current month you can copy the code and change the format() only a bit (just remove DD)

    var today = @all of date field in timer app.toString();
    today = moment(today).tz(your timezone here).format("YYYYMM");
    var payment_date = @all of date field in payment app;
    var payment_status = @all of category field in payment app;
    var payment_amount = @all of amount field in payment app;
    var sum = 0;
    for(var i = 0; i < payment_date.length; i++){
         var p_date = moment(payment_date[i]).tz(your timezone here).format("YYYYMM");
         if(p_date == today && payment_status == "Completed"){
            sum += payment_amount[i] || 0;
        }
    }
    sum

    The timezone must be in a format like ("Europe/Berlin") (that's my timezone) or ("America/New_York").

    If you have any problems with the code share screenshots of the calculations fields, please. 

    Rainer

    0
    Comment actions Permalink
  • Venom

     

    Good afternoon, Rainer.
    Thank you for your help. The formula is not working correctly. The condition is not being checked. I created a card and recorded a random number where the card was in the Created status. And the date on the card is March 23, 2023. But the formula still calculated it as an expense for today. I am attaching a screenshot.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk