Verified Answers

If an answer to your question is correct, click on "Verify Answer" under the "More" button. The answer will now appear with a checkmark. Please be sure to always mark answers that resolve your issue as verified. Your fellow Community members will appreciate it!  Learn more

  • State Verified Answer
  • Replies replies
    4
  • Subscribers subscribers
    167
  • Views views
    149
  • Users 0 members are here
Related
Options

How to copy an “Elapsed time – Calculate days” field in SBM?

Thu Le

I need to calculate the duration of the state “Pending Review by TL”, which may repeat many times because the “Report Rejected by TL” transition may repeat many times.

Here is the property of the related fields:

I had no problem to calculate the rejection duration every time it happened using the calculation option from the Field Overrides property at the “Report Rejected by TL” transition:

However, when I used the following AppScript to copy the calculated value of the “TL – Report Rejection Duration” field to the other fields like “TL – Report Rejection 1”, “TL – Report Rejection 2” and  “TL – Report Rejection 3” etc., the copied fields get a value totally different from their corresponding “TL – Report Rejection Duration” field unfortunately.

In this screenshot, for the first rejection, it took 1 hour and 7 minutes (1:07) for the team lead (TL) to reject the report, but the copy script returned a value as 167 days and 12 hours (167 12:00) instead.

In the following one, for the second rejection, it took 38 minutes (0:38) for the TL to reject the report, but this time the copy script returned a value as 95 days (95 0:00) instead.

 

For the third rejection, it took 10 minutes (0:10) for the TL to reject the report, but this time the copy script returned a value as 25 days (25 0:00) instead.

I also include the timeline of all transitions related to this report record here as references.

It looks like there was a conversion happened during the “getfieldvalue” of an “elapse time” field that I still cannot figure out nor understand. What did I do wrong with my script or with the property settings? Please help, thanks!

  • 0  

    I haven't done very much with calculation fields, so I don't have an answer off the top of my head.

    After the getfieldvalue, have you tried printing the return value to the event viewer using:

    QUOTE = Chr( 34 )  
    Call Ext.LogErrorMsg( "SBM AppScript: Get value is " & QUOTE & Reject_Duration & QUOTE )

    Also, check the database to see what value is stored in the table for the Duration on this item.

    I would start with comparing the value in the database to the value that is returned by the getfieldvalue. Then, also compare this to what get copied to the database with the setfeildvalue. Maybe the get is returning milliseconds and set is expecting seconds (or something similar).

  • 0 in reply to   

    Thank you Vickie for your quick response to my problem!

    This is my new example where the rejection duration was 18 minutes (0:18).

    When I checked the Even Viewer, both my get and set action had the same value as 1080 seconds as 18 minutes x 60 = 1,080 seconds:

    However, here is what I received from my DBA:

    So in the database, the 18 minutes value stored in milliseconds as 18 minutes x 60 x 60 = 3,888,000 milliseconds

    but after that I'm totally lost when the real value for the first rejection returned as 45 days (45 0:00).

    From the above findings, what should I do in order to have the correct value from SetFieldValue function?

    Please help! thanks! 

  • Verified Answer

    0   in reply to 

    Hi Thu, 

    When you get the field value in AppScript, if gives the number of seconds.  When you then copy that number into a new field with 'Calculate days' set, the system interprets the integer as number of hours[1].  So in this case, 1080/24 = 45 days.  To get around this, follow this tip[2]:

    TIP

    For elapsed-time fields, the value that you retrieve from a GetFieldValue() call is not compatible with a call to SetFieldValue(), because a standalone number in an elapsed-time field is interpreted as hours rather than seconds. To get around this, provide SetFieldValue() with the string "00:00:" plus the number of seconds retrieved by GetFieldValue(). For example:

    "00:00:" & elapsedTime

    SBM converts the seconds into the correct hours, minutes, and seconds for you.

    [1] https://docs.microfocus.com/doc/Solutions_Business_Manager/11.8/ttag_setting_display_options_for_datetime_fields

    [2] https://docs.microfocus.com/doc/Solutions_Business_Manager/11.8/ttts_apprecord

    David

  • 0 in reply to   

    David,

    Thank you so much for your quick response and for the instructions that I missed while searching the SBM documentation! The calculation works fine for me now.

Resources

Support
Documentation
Learning Services
Partner Programs
Privacy
Compliance
Help
Company
Privacy Policy
Terms of Use
Cookies Preferences
Accessibility
Anti-Slavery Statement
Support
Contact Us
Careers
Code of Business Conduct and Ethics
The opinions expressed above are the personal opinions of the authors, not of OpenText. By using this site, you accept the Terms of Use. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.), Hewlett Packard Enterprise Company, or Micro Focus. As of January 31, 2023, the Material is now offered by OpenText, a separately owned and operated company. Any reference to the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks is historical in nature and the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks are the property of their respective owners.