SPO – People Picker – Users Manager & Forms Calculation.

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

Viewing 1 reply thread
  • 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 2 years, 1 month ago by  Alexander Bautz. Reason: Fixed typo
Viewing 1 reply thread
  • You must be logged in to reply to this topic.