Highlighted
Absent Member.
Absent Member.
369 views

Dirty little trick: Add variable duration to a date field

Don't you hate that in DB/UI rules you can add only a fixed timespan to a date field? Have you wish to been able to add a custom duration to a date field?

Well, If you use SP18 and don't mind creating a new custom field, you can!

The whole concept is that in reality "dates" are stored internaly as double in your database. Create a custom double field and give it a name (eg: DateToDouble)

Now create UI/DB rule. At the actions select a DATE field and set it to a fixed date or to another date field. But BEFORE pressing the "Add to List" button, replace the date field by TYPING the name of the custom double field.

The rule below will

The following UI rule will set planned finish according to Priority.

When a value has changed
where NOT (Priority (*) is empty)
## SET DATE_TO_DOUBLE (Update Data) DateToDouble set to ( 0.0416666667 * ( ( 60.0 - [Priority Ordering]) / 10.0));
Actual Duration set to [DateToDouble]; DateToDouble set to (Current date); DateToDouble set to ( [Actual Duration] + [DateToDouble]);
Planned Start set to [DateToDouble]

Explanation
The priorities we have are
Ordering | Priority
10 | Top
20 | High
30 | Medium
40 | Low
50 | None

The first sets DateToDouble to xx hours where
xx=1,2,3,4,5 (for Top, High, Medium, Low, None)

The second sets a duration field (in the example Actual Duration) to the value stored ad DateToDouble

The third sets DateToDouble to Current Date and the fourth adds the calculated (from the first step) duration to DateToDouble

The fifth converts DateToDouble to a DATE field (in the example Planned Duration)

It works and I'm a happy man.

Now with one rule I can have as many escallations as I wish!

Till now one rule for each value of a code (for example Priority) was needed.

Ok, this can be done using an external program but don't you love when Service Desk does things by itself ? 🙂
menes fhtagn
0 Likes
8 Replies
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

George, you've got waaaaay too much time on your hands, or a naturally convoluted mind!

Regardless, this gem will be one of those I print off to keep for future reference.

Thanks for the tip & keep 'em coming!

Terry
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

hi!!!


Cooollll and great tip... Thanx TON....

Yes....i love to have this done by our darling SD 😉

-Ashly
http://www.geocities.com/helponhpopenview
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

I'm trying to modify this dirty little trick for our environment, but does this only work with SP18? We have SP17 and I can't seem to type anything in the Value field of the Update Data screen. It seems they've changed the date functions quite a bit since the last time I used SD and now I'm all confused. Any help would be greatly appreciated!!

Thanks,
Colleen
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

Hello Coleen,

This trick won't work in any service pack prior to SP18 because it requires the creation of a custom double field.

The "type" trick works on every sp but this is not enough. You need double precision number to store a duration because dates are stored as double on the db level.
menes fhtagn
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

is this trick fixed in sp24? can`t edit by typing. 😞
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

No, it's not patched. It works on SP25
menes fhtagn
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

May be I do something wrong? Look to the screenshot, please: green zone (Function outline) is not editable. Red labels are translated back to english.
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Dirty little trick: Add variable duration to a date field

The function is always read only. It merely displays the actions. You have to compose it.
menes fhtagn
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.