Post

3 followers Follow
1
Avatar

Calculating hours from a referenced app in a different workspace

I have two workspaces for our students - one which holds their student record and main timetable, the other holds management items such as reports. I'm looking to calculate their attendance in the Management -> Reports app. 

 

I have the calculations working in the Student -> Student Record app, but as I want to set up 3 fields per month (planned hours, attended hours and attendance %), I really do not want to add 33 fields to the student record. I also need to be able to set up an app for each academic year so that I can keep the information separate for each year (e.g. 2018/19 attendance, 2019/20 attendance).

 

I've set up a relationship link through the Student -> Timetable app to the Management -> Reports app, it's just a field in the template, no entries have an actual linked item. There's also a link the other way. The calculation is set up the same way as it is in the Student Record app, but I'm just getting 0.00 in Reports for results when the Student app shows e.g. 32.50.

 

Can someone help me with what I'm doing wrong?

 

The calculation is:

 

var hours = @All of Planned hours (auto calc);
var start = moment("2019-10-01 00:00");
var end = moment("2019-10-31 23:59");
var sum = 0;

for (var i=0; i < hours.length; i++){
var datei = @All of TimetableDate[i].getTime()
if (start <= datei && end >= datei) {
sum += hours[i];
}
}
sum

 

The referenced fields in the calculation are via "Timetable via 19/20 attendance reports link (incoming)" - "19/20 attendance reports link" being the reference field I added to Timetable which links to the Reports app.

 

My Reports app has an item per student, which is linked to the Student Record app (reference field for the student name). I can pull through other information by using .join() - such as age, gender, area - hoping this means I can run reports on these fields in the Reports app in the same way that I can in the Student app.

 

If I can crack the logic of how to make these reports connect and run properly, I can then set up all 11 months of the academic year, and copy this process into a new app for next year etc.

 

Happy to provide screenshots of apps etc if required. Thanks!

Susan Warren

Please sign in to leave a comment.

6 comments

0
Avatar

Hoping to bump this for attention, including a screenshot of the calculation fields.

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

Hello Susan,

your code should work (I've changed it a bit so you can test this version): 

var hours = @All of Planned hours (auto calc);
var dates = @All of TimetableDate;
var start = moment("2019-10-01 00:00");
var end = moment("2019-10-31 23:59");
var sum = 0;

for (var i=0; i < hours.length; i++){
      var date = moment(dates[i]);
             if (start <= date && end >= date) {
                   sum += Number(hours[i]) || 0;
            }
}
sum

I don't understand this: " it's just a field in the template, no entries have an actual linked item. There's also a link the other way. " Is the Report item related to all Student items? And what do you mean by "There's also a link the other way. " ? Does that mean that you have a relationship field in the Report app too which points to the Student app? If so, you have 2 tokens for each field in the Students app  - incoming and outcoming. The "Incoming" token is listed first, but if the relationship field in Students is empty you won't get values. You need to look for the second token - the "Outgoing" token - further down in the token list and to select that one. 
You can simply check if you've selected the right token. Enter in the last line of your calc field: hours.length - if it shows 0 no item is related via the selected direction (Incoming/Outgoing).  

If you have selected the correct token and it still doesn't work:
Some questions:
1. What type of calculation field is Planned hours (auto calc) - text or number? 
2. And what type of field is TimetableDate - date or calculation (if calculation: is it type date or text , and if text: how does the date text look like?) 

Also you can do some checks: 
1. Check if all items in the Students app have a value in TimetableDate and Planned hours (auto calc). If not you need to use the (blue) field tokens: @all of .... with nulls. You can do a first check by entering hours.length as last line in your code, then enter dates.length - both numbers should be the same.
2. You can also check how Planned hours (auto calc) is returned. Add a new calculation field and enter moment (@all of Planned hours (auto calc)[0]).format("YYYY-MM-DD HH:mm").  Then remove that calculation field. 

Btw: You can shorten your code, you don't need the vars start and end, use a var month instead:
var month = "October 2019";
Than in the for loop: 
var date = moment(dates[i]).format("MMMM YYYY")
if(month == date){
...

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer, thank you for your reply.

I've adjusted the calculation for September to use the example you gave, and I get a preview of 0. 

Testing with dates.length and hours.length I get 1 for each, so that checks out, although the calculation still doesn't work. I'm using the incoming token.

The "it's just a field in the template" can be explained through screenshots: there is a relationship field in the Report template which is linked to the Timetable app, but the individual items in Reports do not have an attached item.

 "There's also a link the other way." - meaning that in the Timetable app I have created a relationship field to the Reports app, but again no item attached. I am happy to add to the Timetable app as I have one record per student in Reports, and each student has hundreds of Timetable items. I suspect that this is the necessary step!

Replying to your questions:
1. What type of calculation field is Planned hours (auto calc) - text or number? 

This is a number field - it's a field on the Timetable app item, as per screenshot - the calculation for the field is done through Globiflow. (ditto for Learning hours)


2. And what type of field is TimetableDate - date or calculation (if calculation: is it type date or text , and if text: how does the date text look like?) 

This is a date field with start and end time shown.

I will try adding the Report item to the Timetable items and see if that makes a difference, I suspect the relationship field is not enough if it is not actually linked to a specific item.

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

AHA!

Thank you Rainer - the adjustment and my sudden lightbulb moment have got it working. Now to update 3449 records... ;)

We have a timetable builder in Globiflow so I will update that to add the relationship on all new entries going foward, as well.

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

Rainer - I am looking to try something similar. I want to calculate total hours based on an Employee for the previous week. It keeps coming up 0, but my syntax looks correct.

 

var Timer = @Max of Today;
var PrevWeek = moment(Timer).subtract(1,"week").week()
var CompletedDate = moment(@Max of Completed Date).week;
var ActHours = @All of Actual Hours;
var sumHours = 0;

for(var i=0; i<CompletedDate.length;i++)
{if(CompletedDate == PrevWeek){
sumHours +=Number(ActHours[i]);
}};

sumHours

Steven Lantz 0 votes
Comment actions Permalink
0
Avatar

Hi Steven,

your mistake is here: var CompletedDate = moment(@Max of Completed Date).week;
must be: var CompletedDate = moment(@Max of Completed Date).week();
Parenthesis after week was missing. 

But be carefull: you only get the week number with .week() Means: If var PrevWeek = 7 it will match CompletedDate 7 2019 AND CompletedDate 7 2020 in the IF. I recommend to format the 2 vars you compare like this  (including year):
var CompletedDate = moment(@Max of Completed Date).format("WW GGGG");
var PrevWeek = moment(Timer).subtract(1,"week").format("WW GGGG");

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink