SPO – People Picker – Users Manager & Forms Calculation.

Forums Dynamic Forms for SharePoint SPO – People Picker – Users Manager & Forms Calculation.

This topic contains 1 reply, has 2 voices, and was last updated by  Alexander Bautz 3 weeks, 1 day ago.

  • Author
    Posts
  • #18549

    Chad Taylor
    Participant

    Hi,

    Just freshly installed the Dynamic Forms for SharePoint on our SharePoint Online environment, all went as expected and just learning the new way of forms as we speak.

    I come from a Infopath environment to which we are tying to more away from and have a few questions about a Vacation Form/Leave Form I am trying to create.

    I have worked out how to create a rule to populate the current user of the form on form load, but simply cannot work out how to get the current users manager?. Infopath currently does this in our environment but I cannot work it out here. Plus I am having issue trying to calculate working days. (e.g – no weekends)

    My sticking points I am having trouble with..

    1. Get Current Users Manager into a people picker field on form load (name: Manager)
    2. Calculate working days using a Start Date & End Date (date picker) and place this into a text field (name: Total Days).
    3. Do the same as above but show the hours in a text field (name: total hours).

    Not sure if the above is possible but thought I would ask.

    Great product.

    Cheers

  • #18567

    Alexander Bautz
    Keymaster

    Hi,
    You can do it like this.

    1:
    Use this in “Set field value” in the rule:

    {userProfile:Manager}

    2 and 3:
    Call this function for example from a button or from a rule with the “The form is saved” trigger. Change “DateColumn1” and “DateColumn2” to match your start and end date fields and “BusinessDays” and “BusinessHours” to match your total days and hours field (all must be InternalNames). Also change the “bHourStart” and “bHourEnd” to set the business hours.

    Please note that this code was written to answer this question and has NOT been properly tested so you must run some tests to ensure it performs as it should.

    function calcBusinessDays(){
        var bHourStart = 8, bHourEnd = 16, format, start, end, days = 1, day, sHour = 0, eHour = 0, firstDayHours = null, lastDayHours = null, totalHours = 0, fullDayHours = bHourEnd - bHourStart;
        format = spjs.dffs.getRegionalSettings().DateFormat;
        start = spjs.dffs.strToDateObj("DateColumn1",format);
        end = spjs.dffs.strToDateObj("DateColumn2",format);
        if(start !== false && end !== false){
            // find start hour
            sHour = start.getHours();
            if(sHour < bHourStart){
                sHour = bHourStart;
            }else if(sHour > bHourEnd){
                sHour = bHourEnd;
            }
            // find end hour
            eHour = end.getHours();
            if(eHour < bHourStart){
                eHour = bHourStart;
            }else if(eHour > bHourEnd){
                eHour = bHourEnd;
            }
            if(start.toLocaleDateString() !== end.toLocaleDateString()){
                // Multiple business days
                lastDayHours = eHour - bHourStart;
                firstDayHours = bHourEnd - sHour;
            }else{
                // Only one business day
                firstDayHours = eHour - sHour;
                // Catch end time before start time
                if(firstDayHours < 0){
                    firstDayHours = 0;
                }
            }
            // Count business days
            while(start.toLocaleDateString() <= end.toLocaleDateString()){
                start.setDate(start.getDate()+1);
                day = start.getDay();
                // Only count business days - skip Saturday and Sunday
                if(day !== 0 && day !== 6){
                    days += 1;
                }
            }
            // Find total business hours
            totalHours = days * fullDayHours;
            // Subtract hours if first or last day are not full days
            if(firstDayHours !== null && firstDayHours < fullDayHours){
                totalHours -= fullDayHours - firstDayHours;
            }
            if(lastDayHours !== null && lastDayHours < fullDayHours){
                totalHours -= fullDayHours - lastDayHours;
            }
            // Set field value in business days and hours fields
            setFieldValue("BusinessDays",days);
            setFieldValue("BusinessHours",totalHours);
        }
    }

    Please let me know how this works out.

    Best regards,
    Alexander

    • This reply was modified 3 weeks, 1 day ago by  Alexander Bautz. Reason: Fixed typo

You must be logged in to reply to this topic.