Maciek Grischke

Forum Replies Created

Viewing 15 posts - 1 through 15 (of 49 total)
  • Author
    Posts
  • in reply to: vLookup, currentUser and rules #27723
    Maciek Grischke
    Participant

    Hi Alexander,

    here’s what I want to do.

    There are two lists:
    – Employees and
    – Holiday Requests.

    The Employees list contains their AL entitlement, balance in hours.

    When the employee requests a holiday via Holiday Request list, I want the NewForm to display their AL balance pulled from the Employees list.

    So, if Tony opens a NewForm in Holiday Requests list, I would like the form to locate this employee via their name in the Employees list, pull the AL allowance information and display it in the NewForm. Next, if the AL balance is less than their request, i.e., Tony is requesting to take 50 hours of holiday, but his balance is only 45 hours, in this case, I want the rule to kick in and display a warning message and hide Save button etc.

    At the moment the form allows them to submit their holiday request and I have a Flow to check their AL balance and if their balance is not enough, the Flow will reject their request and send them an email. The Flow locates their record in the Employees list via their name. There are two name columns in the Employees list: User field and Text field. In Flow, I use the “Get Items” action with OData filter and OData filter doesn’t work with user fields, hence I also use a text field to store their Display Name.

    Hope this makes sense.

    in reply to: Hide Gear Icon on New Experience #25913
    Maciek Grischke
    Participant

    Thanks Alexander.

    I tried it, but I am getting this error:

    jquery-3.4.1.min.js:2 POST https://xxx.sharepoint.com/_api/Web/Lists/GetById('53eea4ac-f648-4360-8895-508a2940dd54') 415
    send	@	jquery-3.4.1.min.js:2
    ajax	@	jquery-3.4.1.min.js:2
    toggleVisible	@	SiteContentsSettings.aspx:709
    onclick	@	SiteContentsSettings.aspx:1
    SiteContentsSettings.aspx:726 
    {readyState: 4, getResponseHeader: ƒ, getAllResponseHeaders: ƒ, setRequestHeader: ƒ, overrideMimeType: ƒ, …}
    abort: ƒ (e)
    always: ƒ ()
    catch: ƒ (e)
    done: ƒ ()
    fail: ƒ ()
    getAllResponseHeaders: ƒ ()
    getResponseHeader: ƒ (e)
    overrideMimeType: ƒ (e)
    pipe: ƒ ()
    progress: ƒ ()
    promise: ƒ (e)
    readyState: 4
    responseJSON: {error: {…}}
    responseText: "{"error":{"code":"-1, Microsoft.SharePoint.Client.ClientServiceException","message":{"lang":"en-US","value":"The HTTP header Content-Type is missing or its value is invalid."}}}"
    setRequestHeader: ƒ (e,t)
    state: ƒ ()
    status: 415
    statusCode: ƒ (e)
    statusText: "error"
    then: ƒ (t,n,r)
    __proto__: Object
    

    I am a site admin / global admin so I assumed I have enough rights?

    Also, what I have used so far via scriptlink is:

    addCSS("#applist {display:none !important}");
    function addCSS(css){
    var head = document.getElementsByTagName("head")[0];
    var s = document.createElement("style");
    s.setAttribute("type", "text/css");
    if (s.styleSheet) {
    s.styleSheet.cssText = css;
    } else {
    s.appendChild(document.createTextNode(css));
    };
    head.appendChild(s);
    }
    

    which hides everything from the view and if I need to show the list, I paste this in the console:

    addCSS("#applist {display:block !important}");
    
    in reply to: Generate directions URL from two postcode fields #25705
    Maciek Grischke
    Participant

    Ok I got it working using this code:

    function calcMileage() {
        var startPostcode = getFieldValue("StartPostcode");
        var endPostcode = getFieldValue("EndPostcode");
        var roundTrip = getFieldValue("RoundTrip");
        var origin1 = startPostcode;
    // var origin2 = 'Greenwich, England';
        var destinationA = endPostcode;
    // var destinationB = new google.maps.LatLng(50.087692, 14.421150);
    
        var service = new google.maps.DistanceMatrixService();
        service.getDistanceMatrix(
          {
        origins: [origin1],
        destinations: [destinationA],
        travelMode: 'DRIVING',
    //    transitOptions: TransitOptions,
    //    drivingOptions: DrivingOptions,
        unitSystem: google.maps.UnitSystem.IMPERIAL,
        avoidHighways: false,
        avoidTolls: false,
      }, callback);
      }
    
    function callback(response, status) {
            var roundTrip = getFieldValue("RoundTrip");
      if (status == 'OK') {
        var origins = response.originAddresses;
        var destinations = response.destinationAddresses;
    
        for (var i = 0; i < origins.length; i++) {
          var results = response.rows[i].elements;
          for (var j = 0; j < results.length; j++) {
            var element = results[j];
            var distance = element.distance.text;
            var duration = element.duration.text;
            var from = origins[i];
            var to = destinations[j];
            //console.log('distance');
            //console.log(distance);
            var miles = distance.split(" ");
            var miles2 = miles[0];
            //console.log(miles2);
            //alert(miles2);
    //setFieldValue("Miles",miles2);
            if (roundTrip === true) {
            var miles3 = parseFloat(miles) * 2;
            setFieldValue("Miles",miles3);
    //        alert(miles3);
            } else {
                setFieldValue("Miles",miles2);
                }
          }
        }
      }
    }
    

    but the above requires

    [script async defer src=https://maps.googleapis.com/maps/api/js?key=API_KEY">https://maps.googleapis.com/maps/api/js?key=API_KEY
    [/script]
    

    to be loaded first. I used square brackets for script tags so they don’t get deleted.

    I included this bit in the NewForm “Script Editor” web part.

    Alexander, is this the correct way? It works great, but I want to avoid API to be called every too many times.

    in reply to: Generate directions URL from two postcode fields #25701
    Maciek Grischke
    Participant

    Hi Alexander,

    I updated my reply a few times and I got it working now with my new API.

    Do you think there’s a way to paste the miles into a field after entering both postcodes? 😀

    I’m just investigating JS tutorials online. I’m sure there’s a way.

    in reply to: Generate directions URL from two postcode fields #25694
    Maciek Grischke
    Participant

    That’s amazing, thanks.

    It’s all good.

    Do you think there’s a way to paste the miles into a field after entering both postcodes? 😀

    in reply to: Calculation in rules #25656
    Maciek Grischke
    Participant

    How can I disable the manual input and force users to only use a “date picker”?
    I’ve tried with “read-only” option, but it disables the field entirely. There’s no date picker button.

    in reply to: DFFS sum multiple fields in a totals field #25654
    Maciek Grischke
    Participant

    Thanks for this, it works great in all browsers.

    In relation to those red messages, I think they’re not related to the form itself. I think it relates to some broken scriptlinks or other JS files I previously used on that test site.

    in reply to: Count weekdays & weekends between two dates #25647
    Maciek Grischke
    Participant

    I’ve managed to do it without Moment.js and here’s how:

    //Count weekdays and weekends
     function onCallDays() {
        var onCallStartDate = getFieldValue("OnCallStart");
        var onCallEndDate = getFieldValue("OnCallEnd");
    // the following 4 lines convert UK date format MM/DD/YYYY (my sharepoint region) to US date format DD/MM/YYYY needed for JS to work in my case
        var startDate2 = onCallStartDate.split("/");
        var endDate2 = onCallEndDate.split("/");
        var startDate3 = startDate2[1] + "/" + startDate2[0] + "/" +startDate2[2];
        var endDate3 = endDate2[1] + "/" + endDate2[0] + "/" +endDate2[2];
    // the next two lines are first and second date
        const date1 = new Date(startDate3);
        const date2 = new Date(endDate3);
    // this line retrieves a day number ie Sunday is 0, Saturday is 6, this line is not required, I used it for console.log
        var dateN = date1.getDay();
    // the next two line count the difference in days between two dates
        const diffTime = Math.abs(date2.getTime() - date1.getTime());
        const daysDiff = Math.ceil(diffTime / (1000 * 60 * 60 * 24))+0;
        var weekDays = 0;
        var weekEnds = 0;
        var dateTn = 0;
    // this is where counting begins
        for (var i=0; i<=daysDiff; i++) {
          var dateT = date1.setDate(date1.getDate() + dateTn);
          var dateR = new Date(dateT);
          var dayN = dateR.getDay();
          if (dayN>0 && dayN<6) {
          weekDays++;
        }
        if (dayN===0 || dayN==6) {
           weekEnds++;
        }
        dateTn = 1;
     }
    setFieldValue("OnCall",weekDays);
    setFieldValue("OnCallWknd",weekEnds);
    }
    

    Alexander, it works, but if you think there’s a more efficient way to do this, please correct me.

    The examples I’ve seen online were much bigger, but perhaps they included “holidays”, which I didn’t need.

    in reply to: Calculate time difference in hours or minutes #25636
    Maciek Grischke
    Participant

    Here’s the screenshot(s):
    cal

    This is a “calendar” list.

    in reply to: Setting field value rule for Date/Time field tyoe #25619
    Maciek Grischke
    Participant

    I couldn’t get the date format “01/06/2019 11:00” to do what I wanted it to do hence all the extra steps.

    Thanks for the one line solution 😛

    in reply to: Setting field value rule for Date/Time field tyoe #25604
    Maciek Grischke
    Participant

    I got this working:

    //get Day End Date and Time
    function getEndDate() {
     var endDate = getFieldValue("EndDate");
     var endDate2 = endDate.split("/");
     var endDate3 = endDate2[1] + "/" + endDate2[0] + "/" +endDate2[2];
     var MyEndDate = endDate3.split(" ");
     var Time = MyEndDate[1];
     var MyEndDate2 = MyEndDate[0];
     var MyEndDate3 = MyEndDate2.split("/");
     var MyEndDate4 = MyEndDate3[2]+"-"+MyEndDate3[0]+"-"+MyEndDate3[1]+"T"+Time+"Z";
     var MyEndDate5 = new Date(MyEndDate4);
     MyEndDate5.setHours(MyEndDate5.getHours() - 1);
     spjs.utility.setDateFieldFromDateObject("SleepStart",MyEndDate5,0);
    // alert(MyDate5);
    }
    
    //get Day Start Date and Time
    function getStartDate() {
     var startDate = getFieldValue("StartDate");
     var startDate2 = startDate.split("/");
     var startDate3 = startDate2[1] + "/" + startDate2[0] + "/" +startDate2[2];
     var MyStartDate = startDate3.split(" ");
     var Time = MyStartDate[1];
     var MyStartDate2 = MyStartDate[0];
     var MyStartDate3 = MyStartDate2.split("/");
     var MyStartDate4 = MyStartDate3[2]+"-"+MyStartDate3[0]+"-"+MyStartDate3[1]+"T"+Time+"Z";
     var MyStartDate5 = new Date(MyStartDate4);
     MyStartDate5.setHours(MyStartDate5.getHours() - 1);
     spjs.utility.setDateFieldFromDateObject("SleepEnd",MyStartDate5,1);
    // alert(MyDate5);
    }
    

    I probably don’t need all of the date/time conversion.
    What do you think? 🙂

    in reply to: Setting field value rule for Date/Time field tyoe #25600
    Maciek Grischke
    Participant

    Hi Alexander,

    The day and night shifts are different for different people.

    7:30/22:00 was just an example.

    What I meant was, if the employee selects a ‘time in’ 9:00 and ‘time out’ 20:00 for a day shift, the same time (in this case 20:00-9:00) is copied to the night shift (if “night” option is set to “yes”) to make it easier for users.

    Please note, the second date for the night shift would be +1 day.

    in reply to: Setting field value rule for Date/Time field tyoe #25593
    Maciek Grischke
    Participant

    Hi Alexander,

    How can I use the above to set dates from an existing date field?

    I have 4 date fields and here’s what I want to do. Select shift start and end manually:

    Day:
    StartDate: 01/06/2019 7:30
    EndDate: 01/06/2019 22:00

    and then if option “night” is selected, fill in the following dates automatically:

    Night:
    NightStartDate 01/06/2019 22:00
    NightEndDate 02/06/2019 7:30 (next day)

    in reply to: Calculate time difference in hours or minutes #25591
    Maciek Grischke
    Participant

    Thanks Alexander, this works great in standard lists, but not calendar.

    I get “invalid date” and NaN as the result.

    Dates in Calendar are set as Event columns. Not sure what the difference is, because the calculated column can calculate dates no problem.

    in reply to: Calculate time difference in hours or minutes #25579
    Maciek Grischke
    Participant

    Ok, I found a way to do this, but maybe there’s a better way?

    var startDate = "03/06/2019 20:15";
    var endDate = "03/06/2019 22:00";
    //console.log("startDate");
    //console.log(startDate);
    var startDate2 = startDate.split("/");
    var endDate2 = endDate.split("/");
    var startDate3 = startDate2[1] + "/" + startDate2[0] + "/" +startDate2[2];
    var endDate3 = endDate2[1] + "/" + endDate2[0] + "/" +endDate2[2];
    //console.log("startDate3");
    //console.log(startDate3);
    var startDate4 = new Date(startDate3);
    var endDate4 = new Date(endDate3);
    //console.log("startDate4");
    //console.log(Date.parse(startDate4));
    //console.log("endDate");
    //console.log(Date.parse(endDate4));
    var calc = (endDate4 - startDate4)/1000/60/60;
    console.log(calc);
    

    I just need help getting this to work in NewForm when Date and Time is selected 😉
    I’d like to enter the total hours in “Total” field.

Viewing 15 posts - 1 through 15 (of 49 total)