Trying to calculate hours based on comparing text strings
I'm having issues with a calculation to pick up the number of hours that are booked for a specific activity (work placement) matched against a specific employer. The planned hours are from our Timetable app, and the total needs to show in the Report app. In both, the Employer is a relationship field, so I have created a calculation which pulls the Employer name into a text string to allow me to use it in calculations.
My calculation currently reads as below, with the following variables set:
@Planned hours = calculated field in the Timetable app
@Emp-Cal = text string of Employer in the Timetable app
@YPEmp1 = text string of Employer in the Report app
@Act-Cal = text string of Activity in the Timetable app
var hours = @All of Planned hours (auto calc) with nulls;
var emp = @All of Emp-Cal;
var employer = @YPEmp1;
var act = @All of Act-Cal;
var sum = 0;
for (var i = 0; i < hours.length; i++) {
if ((employer[i] == emp[i]) && (act[i] == "Work Placement")) {
sum += Number(hours[i]) || 0;
}
}
sum
I can calculate the total for all entries listed "Work Placement", but if I want to split it by Employer, I am getting incorrect totals. I have tried using Planned hours with and without nulls, and I've tried taking the [i] from either employer field in the if statement, which gives me a different wrong result.
I should also only see an answer if the employer matches - if there are entries in the Timetable but the Employer field in the Report is not filled in, it should show 0.
I'm sure that it's something simple that I'm missing, but I can't see what it is. If it's that I shouldn't be parsing the Employer name to a text string then I can't see how else to compare it.
I can total all Work Placement hours easily, but I need to have it split by Employer as there may be more than one placement, and we want to be able to show how many hours were booked for each employer (there will be a separate field on the Report app for Employer 2, Employer 3 etc, using the same basic calculation, tweaked accordingly).
-
Hey Susan,
just remove the [i] from here: emp[i]I assume the field @YPEmp1 is a text field. Let's say employer is John.
If you use emp[i] you never will get == "John" but for each iteration in the for-loop one character only.
If i = 0 ( = for the first item you get from the array hours) emp[i] = "J", for the second iteration emp[i] = "o" ... and so on.Rainer
-
Hi Rainer
Thanks for coming back to me on this. I have tried removing the [i] from emp[i] but I'm either getting zeroes or (on one record) getting hours where there is no employer name to match.
I have tried all of the following:
if ((employer[i] == emp[i])
if ((emp[i] == employer)
if ((employer[i] == emp)If the total should be 250.5 - then with the various options above I am seeing: 84.25, 166.25, or 0 - depending on which way round I change the [i] section. None of the changes give me the actual total of 250.5.
Both @YPEmp1 and @All of Emp-Cal are text fields (captured from a relationship field - in both apps the relationship field references the same "Employers" app so the referenced company name is the same record).
I just cannot figure out what I'm doing wrong with this calculation. It should simply compare the employer name in the report app, to the employer name in the timetable app, and total up all hours listed as "work placement" where the employer names match. If I didn't need to compare employer names it would be simple, to just total all "work placement" entries - but where someone might have two different placements, I need to split out the hours according to the employer name.
-
Sorry, it should have been: remove the [i] from here: employer[i]
Just to clarify:
1) @YPEmp1 is a text field in the report app.
2) @All of Planned hours (auto calc) with nulls; @All of Emp-Cal; @All of Act-Cal;
are all 3 fields in the same related timetable app,
3) All 3 referenced fields have always a value, in no item one of the fields can be empty.
4) There's only one relationship between Report and Timetable app.
5) In the field Act-Cal can be one type of work only, either "Work Placement" or another one, but never multiple types.If so, this should work:
var hours = @All of Planned hours (auto calc) with nulls;
var emp = @All of Emp-Cal;
var employer = @YPEmp1;
var act = @All of Act-Cal;
var sum = 0;
for (var i = 0; i < hours.length; i++) {
if (emp[i] == employer && act[i] == "Work Placement") {
sum += Number(hours[i]) || 0;
}
};
sumRainer
-
Hi Rainer
In answer to your questions:
1) @YPEmp1 is a text field in the report app.
Yes
2) @All of Planned hours (auto calc) with nulls; @All of Emp-Cal; @All of Act-Cal;
are all 3 fields in the same related timetable appYes
3) All 3 referenced fields have always a value, in no item one of the fields can be empty.
No - Emp-Cal might be empty as it is only completed for work placements, and Planned Hours may have a 0 (if for example the activity has been cancelled but we need to show it was set up). Act-Cal is always completed.
4) There's only one relationship between Report and Timetable app.
Yes - all Timetable items link to the Report app - matched by student name (all Timetable entries for Susan Smith link to the one Report item for Susan Smith)
5) In the field Act-Cal can be one type of work only, either "Work Placement" or another one, but never multiple typesYes - only one entry in the Act-Cal field is possible
I assume this means the issue lies with the fact that timetable entries may not have an employer in - would it make sense therefore to match Act-Cal first (if Act-Cal == Work placement, then check if Emp-Cal matches employer)? Would a nested if statement work? Something like this maybe:
var hours = @All of Planned hours (auto calc) with nulls;
var emp = @All of Emp-Cal;
var employer = @YPEmp1;
var act = @All of Act-Cal;
var sum = 0;
for (var i = 0; i < hours.length; i++) {
if (act[i] == "Work Placement") {
if (emp[i] == employer) {
sum += Number(hours[i]) || 0;
}
}
};
sumThanks
Susan
-
If Emp-Cal can be empty you need @all of emp-cal with nulls. I think that's the only issue.
var hours = @All of Planned hours (auto calc) with nulls;
var emp = @All of Emp-Cal with nulls;
var employer = @YPEmp1;
var act = @All of Act-Cal;
var sum = 0;
for (var i = 0; i < hours.length; i++) {
if (act[i] == "Work Placement" && emp[i] == employer) {
sum += Number(hours[i]) || 0;
}
};
sum
Please sign in to leave a comment.
Comments
6 comments