Trying to calculate hours based on comparing text strings

Comments

6 comments

  • Rainer Grabowski

    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

    0
    Comment actions Permalink
  • Susan Warren

    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.

    0
    Comment actions Permalink
  • Rainer Grabowski

    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;
           }
    };
    sum

    Rainer

    0
    Comment actions Permalink
  • Susan Warren

    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 app

    Yes

    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 types

    Yes - 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;
    }
           }
    };
    sum

     

    Thanks

    Susan

    0
    Comment actions Permalink
  • Rainer Grabowski

    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
    0
    Comment actions Permalink
  • Susan Warren

    Hi Rainer

    That's it! You are such a star, I knew it had to be something fairly simple that I'd overlooked but I couldn't see what it was. Thank you! All sorted out now.

    Susan

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk