Post

3 followers Follow
0
Avatar

Simple Calculation with Category Check from Another App

I'm not familiar with JavaScript, but I am trying to build a calculation for employee time-off requests. I have an app where they make the request. If the request is approved (a category), I'd like this calculation to subtract the number of days in the request from their yearly allocated amount. What I have works extremely well if there is only one request per staff member - but once a second request is submitted, even if both are approved, it displays the "Else" statement. Below is what I have:

 

var cat= @All of Approval
if (cat == "Approved")

{

@Year's Personal Days -@Sum of Personal Days;

} else {

@Year's Personal Days ;

}

 

Any suggestions?

Jared Belcher

Please sign in to leave a comment.

7 comments

0
Avatar

Try this:

var approved = @All of Approval with nulls
var personaldays = @All of Personal Days with nulls
var total = 0;

for(var i = 0; i < approved.length ; i++)
{if (approved[i][0] == "Approved"){total +=personaldays[i][0]||0}}

@Year's Personal Days-total

Andrew Wardle 0 votes
Comment actions Permalink
0
Avatar

Thanks, Andrew! So I plugged in the calculation and it says "Cannot read property of '0' undefined. I've posted a screenshot. Any suggestions?

Jared Belcher 0 votes
Comment actions Permalink
0
Avatar

Hi Jared,

some questions: 
1. Is the field "Personal Days" in the same app as the field "Approval"?
2. Is the field "Approved" a single choice or a multi choice category field?
3. Do the vars "approved" and "personaldays" have the same "length" (number of items pulled)? You can check that by writing in the last line of the calculation field: approved.length, then remove that with: personaldays.length. Both returned numbers (in the preview) should be the same.  But before you enter that, add in the empty line between var total and for: /*
and in the line after "total;": */ (that deacitvates the code so that the error msg. disappears). 
If they have the same length:
4. For checking in which line the error occurs please do this: Remove personaldays.length and /* and */. Then in the line 
total +=personaldays[i][0]||0 
replace personaldays[i][0]||0 with 1.Does the error msg. still appear?
5. You can also try this: Change the if () to if(String(approved[i][0]) == "Approved") and the total line to total += Number(personaldays[i][0]) || 0
Does that help?

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Rainer, 

That is all super helpful, thank you so much for your time on this. It still doesn't work, but perhaps the answers to your questions will help reveal a reason why:

1. The number of personal days requested is in the same app as the approval field. That app is called "Time Off." The total personal days available is in the same app as this calculation, and that field is called "Year's Personal Days."

2. Single choice.

3. Yes, both said "0"

4. Yes, tried that and the error persisted 

5. Tried that, and the error persisted. 

 

I've attached an updated screenshot with the (hopefully correct) recommendations you made.

Any thoughts?

Jared Belcher 0 votes
Comment actions Permalink
0
Avatar

Hello Jared,

you wrote: 3. Yes, both said "0"

That means: In this item (where you created the calculation) no items are related from where you can pull data. So the notification in the preview is correct. 

Try it in an item which has related requests. 
If the item where you get the error notification has related requests:
Do you have a relationship from the request app to the employee app and also a relationship from employees to requests? 
If so you picked the wrong variable token from the drop down list. If you have vice versa relationships there are 2 tokens for the same @all of field, one for incoming relations and one for outgoing relations (you see that in "via" line). Be sure to pick the right token. 

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Rainer, I can't tell you how much help you have been. I think that token comment was really what did it. Using your code, I was able to find a solution. It's probably not exactly right, but it seems to be working very well after a lot of testing. Would love any refinement recommendations if you have them, but for others who are looking for a similar solution, this worked well for me.

 

var approved = @All of Approval with nulls

var personaldays = @All of Personal Days with nulls
var total = @Year's Personal Days ;

for(var i = 0; i < approved.length ; i++)
{
if (approved[i][0] == "Approved")
{
total +=-personaldays[i][0]||0;
}

}
total;

Jared Belcher 0 votes
Comment actions Permalink
0
Avatar

Hey Jared,

glad to help. Your code is correct, the only thing I would change is

total +=-personaldays[i][0]||0;
to 
total +=-Number(personaldays[i][0]) || 0; 

It's a double safetey check using Number(). Also add at the end of the first two lines after "... with nulls" a semicolon. The code works without the semicolon in Podio, but normaly you should end each var with a semicolon. 

Good luck with your next calculation codes!

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink