Get email from person and calculate datediff in hours

Forms SharePoint Online
1

Hi,
I have to get the email address from the selected user in the person field 1 (User) and write it in the text field 2 (Title).
Then I have to calculate the difference between date time field 3 (StartTime) and date time field 4 (EndTime) and write the result in hours in the number field 5 (Time_x0020_Spent).

forms
forms578×644 12.9 KB

Thanks in advance for support.

2

Dear @stefano.mazzi,
Here's an article on how to calculate difference between dates with JavaScript - Work with Date and Time fields in SharePoint forms using JS — SharePoint forms

As for getting an email, you can get it from a field like this:

fd.field('Email').value = fd.field('Person').value.EntityData.Email;

And if it doesn't work, try this:

var person = fd.field('Person').value;
if (person && person.Key){
    pnp.sp.profiles.getPropertiesFor(person.Key).then(function(result) {
        var props = result.UserProfileProperties;
        console.log(props);
        for (var i = 0; i < props.length; i++) {
            switch (props[i].Key) {
                case 'WorkEmail':
                    fd.field('Email').value = props[i].Value;
                    break;
            }
        }
    });
}
3

Hi @Nikita_Kurguzov,
about email works great this solution that you suggested:

fd.spRendered(function() {
function fields() {

    fd.field('Title').value = fd.field('User').value.EntityData.Email;
}

// Calling fields when User value changes
fd.field('User').$on('change',fields);
		
// Calling fields on form loading
fields();

});

While, to calculate in the difference in minutes between two dates, I tried to follow the article but without success.
My fields are:

Start Time (date time field)
End Time (date time field)
Time Spent (number field with 2 decimals)

I need to calculate the difference in minutes and write it in the Time Spent field.

Can you help me?
Thank you.

4

Dear @stefano.mazzi,
The code that you need is this, just make sure Internal Names match for the fields:

fd.spRendered(function() {
    require(['moment'], function(moment) {
        function diffMins() {
            var start = moment(fd.field('StartTime').value);
            var end = moment(fd.field('EndTime').value);
            if (start.isValid() && end.isValid()) {
                var diff = end.diff(start, 'minutes', false);
                fd.field('TimeSpent').value = diff;
            }
        }

        // Calling function when the user changes the date
        fd.field('StartTime').$on('change', diffMins);
        fd.field('EndTime').$on('change', diffMins);

        // Calling function on form loading
        diffMins();
    });
});
5

I tried but without success:

fd.spRendered(function() {
    require(['moment'], function(moment) {
        function diffMins() {
            var start = moment(fd.field('StartTime').value);
            var end = moment(fd.field('EndTime').value);
            if (start.isValid() && end.isValid()) {
                var diff = end.diff(start, 'minutes', false);
                fd.field('Time_x0020_Spent').value = diff;
            }
        }

        // Calling function when the user changes the date
        fd.field('StartTime').$on('change', diffMins);
        fd.field('EndTime').$on('change', diffMins);

        // Calling function on form loading
        diffMins();
    });
});

forms
forms582×625 8.91 KB

Maybe I must use this code at the top of my function?

requirejs.config({
paths: {
moment: "https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment-with-locales.min"
}
});

Thank you for your support.

6

Dear @stefano.mazzi,
Well, it doesn't seem like Time Spent is an editable field, it needs to be not Readonly in order to be able to set the value. You can hide it, but it needs to be editable.

7

forms_2
forms_2584×626 8.92 KB

Even if I set the field editable I can't set the value.

8

Dear @stefano.mazzi,
Check browser's console for errors.

9

Sorry @Nikita_Kurguzov , I forgot to put the code above the function.
This is the final function that's works well.

requirejs.config({
paths: {
moment: "https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment-with-locales.min"
}
});

fd.spRendered(function() {
    require(['moment'], function(moment) {
        function diffMins() {
            var start = moment(fd.field('StartTime').value);
            var end = moment(fd.field('EndTime').value);
            if (start.isValid() && end.isValid()) {
                var diff = end.diff(start, 'minutes', false);
                fd.field('Time_x0020_Spent').value = diff / 60;
            }
        }

        // Calling function when the user changes the date
        fd.field('StartTime').$on('change', diffMins);
        fd.field('EndTime').$on('change', diffMins);

        // Calling function on form loading
        diffMins();
    });
});
1 Like