Round up/down datetime field with BPM or Formula

We have a requirement that I am really struggling with so all help welcome. Using the BPM I am adding SLA targets using Business Hours to our Tasks. This has been nice and easy to achieve using Business Rules and Processes adding onto the Datetime Start field we are using.

We have a need whereby if a Task is received after a certain time then rather than be 4 business hours it will be 14:00 the following day. I can add the hours but utilising a suite of Business rules to work out how many Business Hours to add.

The bit that I cannot figure out is how to round up/down to the nearest hour.

Has anyone been able to achieve this or similar please?

Thanks

*No code changes please. I would like to achieve if at all possible without impacting the core code.

Parents
  • hello Pete

    below is an idea of rounding down to the hour by retrieving hours digits from the datetime field value via conversion to string following conversion to a number - that may help to build back the necessary DateTime value by concatenating datetime components (day, month, year, hour, 00 minutes)
    I hope this helps.

    however, I'd note that having no coding skills I would configure calculating DateTime Start with Logic Builder tool for Sugar - with a few clicks and no-code - on Task is created/assigned (or whatever other logic triggers Datetime Start calculation) and deploy configuring to SUgar via Module Loader

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • Thanks for the suggestion Dmytro. 

    I was going down this path, however the issue I came across was how to get the deconstructed elements back into a datetime format so that the BPM Business Hours logic could work on it as it only works on datetime fields.

  • Oh, seems that I'm also unable to find a way of getting a DateTime field value from the number of datetime components with SugarLogic formulas of CalcFields... 
    again, I would use the Logic Builder to solve the whole task with no-coding in Sugar

    however, some of my experiments with Business Center and Business Rules have led to the results that looks fruitful

    I used the rounded down hour number for Task createdate and set up this Business Rule:





    Where MyBusinessCenter1 is that Business Center:

    If a Task is created before noon, that should BPM sets DateTimeStart to the datetime in 4 business hours (according to MyBusinessCenter1)  from createdate. For tasks created after noon time, the DateTimeStart should be at 14:00 of the upcoming working day  (according to MyBusinessCenter1)

    Note that, Business Center timezone, my real time zone and the time zone of the user profile in Sugar are equal in my test

    Please let me know if the trick worked
    If not, I'll try configure your task's solution with Logic Builder 

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • Thanks again Dmytro.

    What you have suggested does go a long way to getting to what I am trying to achieve and I had tried this previously too. It does indeed work perfectly for being able to get the overall offset correct but it is the rounding out of the minutes that I cannot figure out an answer for that  not as elegant as I am hoping to be able to achieve.

    Where this is still not quite ideal is where we have an example situation of make credit card payments. The deadline to process these by the Bank is 4PM.

    Therefore, anything raised before 12:00 would have a Due Date of 16:00 (4bh) that day and anything after would be 16:00 the next business day.

    Using the above works great for doing the overall offset, however if a task is raised at 11:42 the Business logic would add 4 Business Hours and set the due date for 15:42.

    I have been trying to apply similar logic to the minutes

    However it keeps rounding it up to the nearest 15.

  • I don't know about BPM (I am still working on Pro an we don't have that) but using toString on a date in sugar logic is a bit dangerous as it returns the format that the user has chosen and the date translated to the user's time zone. 

    For example a DB date/time stamp of

    2020-04-17 18:21:43

    will return

    toString($date_entered) = 04/17/2020 01:30pm

    or

    toString($date_entered) = 2020-04-17 13.30

    depending on my profile settings.

    If you don't care about that and you're sure you will stick to one format you can build some logic based on hour and minutes.

    For example if your DB datetime = 2020-04-17 18:21:43 and your user is set to that same format then

    whole date =  toString($date_entered)
    2020-04-17 13.30


    date  = subStr(toString($date_entered),0,10)
    date = 2020-04-17

    hour =  subStr(toString($date_entered),12,2)
    hour = 13


    minute = subStr(toString($date_entered),15,2)
    minute = 30

    then you can build a series of ifElse statements to check if it's, for example, past 08:59 make it the next day at 14:00

    if the hour is greater than 8:

    greaterThan(number(subStr(toString($date_entered),11,2)),8)


    then return "14.00"

    else (the hour is less than 8)

    if the minutes are more than 29

    greaterThan(number(subStr(toString($date_entered),15,2)),29)

    then round up to the next hour by converting the hour to a number and adding one

    number(subStr(toString($date_entered),12,2))+1

    you will need to check if the result is greater than 24 (you'll need to do more work if it is)

    greaterThan(number(subStr(toString($date_entered),12,2))+1, 24)

    .... etc...

    you get the gist.

    I'll be honest with you, if I was doing this I would certainly not do it with sugar logic, I'd rather do it with a logic hook and even then it would be a bit of a pain.

    If you don't want to do any programming 's Logic Builder (which is a third party tool he sells that generates it's own logic code, not a sugar logic hook) may be one way to go. I can't speak to Logic Builder specifically, I gave it only a cursory look since I prefer to develop my own logic hooks within the sugar framework.

    FrancescaS

Reply
  • I don't know about BPM (I am still working on Pro an we don't have that) but using toString on a date in sugar logic is a bit dangerous as it returns the format that the user has chosen and the date translated to the user's time zone. 

    For example a DB date/time stamp of

    2020-04-17 18:21:43

    will return

    toString($date_entered) = 04/17/2020 01:30pm

    or

    toString($date_entered) = 2020-04-17 13.30

    depending on my profile settings.

    If you don't care about that and you're sure you will stick to one format you can build some logic based on hour and minutes.

    For example if your DB datetime = 2020-04-17 18:21:43 and your user is set to that same format then

    whole date =  toString($date_entered)
    2020-04-17 13.30


    date  = subStr(toString($date_entered),0,10)
    date = 2020-04-17

    hour =  subStr(toString($date_entered),12,2)
    hour = 13


    minute = subStr(toString($date_entered),15,2)
    minute = 30

    then you can build a series of ifElse statements to check if it's, for example, past 08:59 make it the next day at 14:00

    if the hour is greater than 8:

    greaterThan(number(subStr(toString($date_entered),11,2)),8)


    then return "14.00"

    else (the hour is less than 8)

    if the minutes are more than 29

    greaterThan(number(subStr(toString($date_entered),15,2)),29)

    then round up to the next hour by converting the hour to a number and adding one

    number(subStr(toString($date_entered),12,2))+1

    you will need to check if the result is greater than 24 (you'll need to do more work if it is)

    greaterThan(number(subStr(toString($date_entered),12,2))+1, 24)

    .... etc...

    you get the gist.

    I'll be honest with you, if I was doing this I would certainly not do it with sugar logic, I'd rather do it with a logic hook and even then it would be a bit of a pain.

    If you don't want to do any programming 's Logic Builder (which is a third party tool he sells that generates it's own logic code, not a sugar logic hook) may be one way to go. I can't speak to Logic Builder specifically, I gave it only a cursory look since I prefer to develop my own logic hooks within the sugar framework.

    FrancescaS

Children
  • Hi  Francesca,

    Let me highlight the benefits of using BPM for this particular task

    Sugar BPM allows to run Business Rules , the ones allow to use Time Span for adding business hours according to the Business Center availability time

    It means that if e.g. the request is submitted on Sunday (dayoff) , and the office (Business Center) opens on Tuesday at 1pm and closes at 6 pm, then adding 2 business hours will give us the datetime of "Tuesday 3pm" 


    Being implemented in the out-of-the-box Sugar, it helps to save a lot of time and/or money on custom logic hook developments, because all the implementation of available date calculations in the logic hook could be easily avoided, and I'm not saying here about learning the code development

    I'm trying to highlight these advantages of the BPM, because Sugar Pro is not in the Sugar pricelist anymore, and we believe that very soon every subscriber will be able to benefit from SugarBPM functionality on-prem as well as in Sugar Cloud

    So our the task for this case is to fill out the gap in the fruitful BMP mechanic by, I suppose, overriding the time to 16:00 and perhaps that could be done with Logic Builder configuring and then included into the SugarBPM

    Regarding the tool - from Sugar Admin perspective, Logic Builder is Sugar configuring tool, from a technical perspective Logic Builder is nothing else but a Sugar custom code generator - it delivers no-code the customizations that developers got used to delivering

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • SugarPro customers now have access to SugarEnt, I've just not had the time to do the conversion on our systems yet.

    Thank you for pointing out the advantages, I will have to spend some time learning how to work with BPM.

    F.