SPO – People Picker – Users Manager & Forms Calculation.

[Chad Taylor]

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

[Alexander Bautz]

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 7 years, 1 month ago by Alexander Bautz. Reason: Fixed typo

[Author]

Posts