Rules to set date field value to a calculated value

Forums Dynamic Forms for SharePoint Rules to set date field value to a calculated value

This topic contains 9 replies, has 2 voices, and was last updated by  Alexander Bautz 1 month, 2 weeks ago.

  • Author
    Posts
  • #18735

    Charlene
    Participant

    HELP!! I’m trying to set up rules to set a calculated date field value based on the input of an end date but need some help! I know how to calculate the dates in Excel, but don’t know JS well enough to incorporate into SharePoint! Can someone please help me with this? I added a list of dates that are calculated in Excel for reference. Do you also know how to move the tooltips to the left of the field instead of the right?

    End date inputted: 3/31/2018

    Note: All dates below should be on business days.

    PL Date: This should be at least 2 months before End date and on a Thursday = 1/25/2018
    GTM Date: This should be at least 1 month before End date and on a 2nd or 4th Friday = 2/23/2018
    Comm1 Date: This should be the next business day after GTM Date = 2/26/2018
    Comm2 Date: This should be the next business day after Comm1 Date = 2/27/2018

    THANK YOU!

  • #18767

    Alexander Bautz
    Keymaster

    Hi,
    I’ll try to help you out here, but I’m struggling a bit with the logic for the “GMT Date”. Is what determines if it should be the 2 or 4 which of the dates that are nearest to one month from the end date?

    Moving the tooltips should work with this in the custom css:

    .customTooltip{
        float:none!important;
        display:inline;
        margin-right:5px;
    }
  • #18771

    Charlene
    Participant

    Thanks Alexander! Yes, what determines the ‘GTM Date’ is the nearest Friday to to the 1 month before the end date. If that’s too hard to write in, it could be the following 2nd or 4th Friday’s after 1 month before the end date.

    Thank you so much!

  • #18823

    Alexander Bautz
    Keymaster

    Sorry for the delay. I’ve been busy with other support issues during the weekend and will get back to you later this week with the code snippet.

    Best regards,
    Alexander

    • #18837

      Charlene
      Participant

      No problem at all! Thank you so much!

  • #18878

    Alexander Bautz
    Keymaster

    Hi,
    I have finally found time to write up a function for you. I had to do a bit of thinking – I’ll admit that, and I’m not 100% sure this will work in all scenarios so you will have to test it properly.

    Add this code to the Custom JS in DFFS backend:

    function dffs_getDate(arg){
        var neg = arg.offset.months < 0 || arg.offset.days < 0, date = new Date(arg.date.valueOf()), targetMonth = date.getMonth()+arg.offset.months;
        // offset day
        if(arg.offset.days !== 0){
            date.setDate(date.getDate()+arg.offset.days);
        }
        // offset month
        if(arg.offset.months !== 0){
            if(neg){
                while(date.getMonth() !== targetMonth){
                    date.setDate(date.getDate()-1);
                }
            }else{
                while(date.getMonth() !== targetMonth){
                    date.setDate(date.getDate()+1);
                }
            }  
        }
        // nearest
        if(arg.key === "nearest"){
            while(date.getDay() !== arg.targetDay.day){
                if(neg){
                    date.setDate(date.getDate()-1);
                }else{
                    date.setDate(date.getDate()+1);
                }
            }
        }
        // nth
        if(arg.key === "nth"){
            while(date.getDay() !== arg.targetDay.day){
                if(neg){
                    date.setDate(date.getDate()-1);
                }else{
                    date.setDate(date.getDate()+1);
                }
            }
            var dateIndexObj = getDateIndexArr(new Date(date.valueOf()),arg.targetDay.day), loopTicker = 0;
            while(jQuery.inArray(String(dateIndexObj[date.toLocaleDateString()]),arg.targetDay.index) < 0){
                loopTicker += 1;
                if(loopTicker > 1000){
                    alert("Failed to calculate the date");
                    break;
                }
                if(date.getDay() !== arg.targetDay.day){
                    date.setDate(date.getDate() - (7 - date.getDay()));
                }else{
                    date.setDate(date.getDate() - 7);
                }
            }
        }
        // businessday
        if(arg.key === "businessday"){
            while(date.getDay() === 0 || date.getDay() === 6){
                if(neg){
                    date.setDate(date.getDate()-1);
                }else{
                    date.setDate(date.getDate()+1);
                }
            }
        }
        return date;
    }
    
    function getDateIndexArr(date,targetDay){
        var month = date.getMonth(), dArr = [], dObj = {};
        while(month === date.getMonth()){
            dArr.unshift(new Date(date.valueOf()).toLocaleDateString());
            date.setDate(date.getDate() - 7);
        }
        jQuery.each(dArr,function(i,d){
            dObj[d] = i+1;
        });
        return dObj;
    }

    Then use this format when calling the function:

    // ************ Days are 0-indexed - use these index numbers in "targetDays" ******************
    // 0 = Sunday
    // 1 = Monday
    // 2 = Tuesday
    // 3 = Wednesday
    // 4 = Thursday
    // 5 = Friday
    // 6 = Saturday
    
    var format = spjs.dffs.getRegionalSettings().DateFormat;
    var start = spjs.dffs.strToDateObj("DateColumn1",format);
    
    // PL Date
    var pl_date = dffs_getDate({
        "date":start,
        "key":"nearest",
        "offset":{
            "months":-2,
            "days":0},
        "targetDay":{
            "index":null,
            "day":4
        }
    });
    console.log("pl_date:"+pl_date);
    
    // GTMDate
    var gtm_date = dffs_getDate({
        "date":start,
        "key":"nth",
        "offset":{
            "months":-1,
            "days":0},
        "targetDay":{
            "index":["2","4"],
            "day":5
        }
    });
    console.log("gtm_date:"+gtm_date);
    
    // Comm1 Date
    var comm1_date = dffs_getDate({
        "date":gtm_date,
        "key":"businessday",
        "offset":{
            "months":0,
            "days":1},
        "targetDay":{
            "index":null,
            "day":null
        }
    });
    console.log("comm1_date:"+comm1_date);
    
    // Comm2 Date
    var comm2_date = dffs_getDate({
        "date":comm1_date,
        "key":"businessday",
        "offset":{
            "months":0,
            "days":1},
        "targetDay":{
            "index":null,
            "day":null
        }
    });
    console.log("comm2_date:"+comm2_date);

    Change the name of the date column from “DateColumn1” to whatever your field has as “FieldInternalName”.

    To set the date in a datepicker, use this format:

    setFieldValue("NameOfDateCol",spjs.utility.dateObjToFormattedString(pl_date,"MM/dd/yyyy")

    Let me know how this works out.

    Best regards,
    Alexander

    • #18904

      Charlene
      Participant

      Thank you so much Alexander! I tried including the 1st and 2nd code you provided above in Custom JS and changed the name of the DateColumn1 to my End Date.

      I was getting an error with the setFieldValue code you have above, so I inserted the following code after console.log instead.

      var pl_date2 = spjs.utility.dateObjToFormattedString(pl_date,"MM/dd/yyyy");
      setFieldValue("PLDateColumn",pl_date2);

      The problem I’m seeing is:
      1. The ‘EndDate’ is not being retrieved. It is pulling in today’s date even though I updated the DateColumn1 to the ‘EndDate’. I’d like this function to trigger when the EndDate is not null or updated and when PLDate, GTMDate, Comm1Date, Comm2Date are empty. Is that possible? Do I need to use rules or just include it all in the Custom JS? Perhaps add: if(“start” == “”){setFieldValue…};?

      2. The year represented is 1969 vs 2017. How do I fix this?

      THANK YOU!

      • This reply was modified 1 month, 3 weeks ago by  Charlene.
  • #18912

    Alexander Bautz
    Keymaster

    You can set a rule to trigger on change of the “EndDate” field, and wrap the code in a function so yo can call it from the rule.

    If your “EndDate” isn’t pulled in right you should try running these lines in the console (hit F12 > Console):

    var format = spjs.dffs.getRegionalSettings().DateFormat;
    var start = spjs.dffs.strToDateObj("EndDate",format);
    console.log(start);

    Is the start logged in the console correctly?

    Alexander

    • #18915

      Charlene
      Participant

      Wrapping the code in a function and calling it from the rule worked beautifully!!!!!! THANK YOU SOOOOOOO MUCH!!!

      And thank you so much for your patience with the troubleshooting! I am not very familiar with JavaScript so your notes for the console helped a TON!!

      And one thing I wanted to note was that I ended up creating 2 rules because there are 2 end dates I’m working with. When I tried cloning the 1st rule, the 2nd rule didn’t work, but when I created them as new rules, the 2nd rule started working. I’m not sure if that’s a bug with the cloning feature, but wanted to bring it up in case it is.

      Thanks again, Alexander!!!! Sending you a few beers for your help!!!

  • #18922

    Alexander Bautz
    Keymaster

    I’m glad you got it up and running – and thank you for the beer 🙂

    Have a nice weekend,
    Alexander

You must be logged in to reply to this topic.