Date Format in Auto-Emails

[SJEaton]

Hi

In one of our ProCaps we have date pickers that we then pull in via custom fields into an auto-email.  The dates however are presenting in American format (see example attached) and we want to change this to DD/MM/YYYY.

We have amended the user details (including the system administrator) to the date format that we want but it still hasn't fixed it.

Can you advise where we need to specify the date format we want presented in auto-emails please?

Thanks

Sam 

[SJEaton]

From: HR Admin [mailto:<edited>]
Sent: 03 August 2017 07:48
To: <edited>
Subject: Request to Increase the Pay of an Agency Worker - Ref: SR00001147

 

Dear Matrix Account Manager

Could you please implement the following higher rate changes to this placement:

Placement no:
Start date for proposed higher pay rate:
Higher pay rate termination date:
SAP number:
Job category:
Role title:
Increased pay rate for role:
Is this role ‘in scope’ or ‘out of scope’ for IR35 purposes?
Start date of placement:
End date of placement:	2017-09-09

Kind regards, 

Human Resources 

Corporate Development Group

London Borough of Waltham Forest 

Waltham Forest Town Hall

Forest Road

London E17 4JF

Tel: <edited>

Email: 

Twitter: 

Website: 

[Victor]

@SJEaton if the date is stored in a custom field it will be stored as string using the database format (UTC) for date/time values:  YYYY-MM-DD hh:mm:ss. Once the value is stored in a custom field, it can't be converted to any time zone or date/time format as it is (now) a string.


In your above posts, I have removed the attachment and edited email addresses and telephone numbers as it is considered confidential information which should not be made publicly available.

[SJEaton]

So the string can't be changed?

[Hornbill Staff DR]

Hi Sam,

 I hope you are well.
That's correct. This is a known design limitation that we are acutely aware of and taking steps to address. The planning has been completed therefore it is just a matter of prioritising and initiating the development work. Presently I can't provide a delivery date but this is a challenge that other Hornbill users have encountered and we recognise it as something that certainly has to be overcome.

Dan

[SJEaton]

Hi Dan

OK, keep me updated.  Thanks

Sam

[Victor]

@SJEaton the date/time convert functions (the ones that convert a date/time to a time zone or a format) only work with date/time fields. Hornbill would not know what value you store in a string (VARCHAR)custom field if is a simple text or a date time... as @DanielRi said this is something that should be catered for in a future update.

This being said, thanks to @Trevor Killick for pointing this out, as a workaround you can use Hornbill Integration Bridge to convert the value in the custom field to a specific format. If you tell me the BP name on the requests where this email is being sent I can quickly draw it to show you how it works...

[SJEaton]

OK cool. Its in the 'HR Request to Increase the Pay of an Agency Worker (Control 3) BP and it's the 'Email to Matrix' node on the 'Confirmation and Close' stage.

Sam

[Victor]

@SJEaton there is a copy of the BP, named: HR Request to Increase the Pay of an Agency Worker (Control 3) - Copy . Is ready to use, can you give it a try? I suggest using this BP for tests, to see if it works (if it does what you need) then you can do the same in the original process...

EDIT: currently it only formats Custom_G. If the test is ok for this field we can do the same for other custom fields storing date/time values...

[SJEaton]

No it hasn't worked...

 

Dear Matrix Account Manager

Could you please implement the following higher rate changes to this placement:

Placement no:
Start date for proposed higher pay rate:
Higher pay rate termination date:
SAP number:
Job category:
Role title:
Increased pay rate for role:
Is this role ‘in scope’ or ‘out of scope’ for IR35 purposes?
Start date of placement:
End date of placement:	undefined

Kind regards, 

Human Resources 

Corporate Development Group

[Victor]

@SJEaton Yep, you're right, it didn't work, I put the wrong date formatter there ... Corrected this in the copy process. Can you give it another try?

[SJEaton]

Yes that's worked for Custom G now, thanks.  So do I just copy the flow code for the other custom fields I want to update?

Sam

[SJEaton]

All done

Thanks for your help.

Sam

[Victor]

@SJEaton sorry for the delayed reply, I am a bit late here ... but yes, it would be the same node logic for all other custom fields storing dates.

For reference, in case someone else finds this thread and/or is interested in teh solution, this is how it was done (date format changed) via the BP using iBridge integration nodes:

First, have an "Integration Call" node to convert the date value stored in a custom field (let's say Custom A):

Second, have an "Update Request" node, for custom fields, which updates the same custom field (in our example Custom A) with the converted value (which is the response parameter from the Integration Call node):

You should now have the converted value in your custom field!

[SJEaton]

Hi

I've used this in my 'I Want to Appoint - V2.0' BPM to convert the date format for custom field A.  This custom field is actually captured in the details of the request and not the ProCap and when the workflow reaches the point that it generates the email with the converted date in it, it's automatically changing the date entered in custom A to 'undefined' and then entering 'undefined in the email.  Help!?

It's happened on test request SR00001284.  I'll send some screen shots of the integration bridge I've set up but I can't see anything wrong with it.  Any ideas why this is happening?    

Sam

[SJEaton]

This is the custom field that's changed....

[Victor]

@SJEaton I have sent you a PM... don't think you can share a custom field across services (thinking on the table structure) ... but I might be wrong...

[SJEaton]

I'm just trying to convert the format of it when it gets used as a variable in an email?

[SJEaton]

If I test it out selecting 'Internal Appointment' it works ok and the date remains in the details and the format is changed nicely on the email that is generated at the Om-Boarding - Internal stage.  When I test it out selecting 'External Appointment' however, the issue occurs when it reaches the Acknowledgement email to Manager node i.e. it changes the date in custom A to undefined and then enters undefined I the email.

[SJEaton]

@James Ainsworth I don't suppose you can assist with this issue can you?  We are planning to go live with this process next week so I'm hoping we can fix the issue before we do!  Thanks

Sam

[Martyn Houghton]

@SJEaton

Does you progressive capture branch to a different form/question when it is an external appointment, as it could be that it is not getting set properly in the first place by the question setup?

Cheers

Martyn

[SJEaton]

The Employee Start Date isn't captured in the ProCap, the analyst enters it in h_custom_a via the details in the request itself

Sam 

[Martyn Houghton]

@SJEaton

When it is first entered in the Details section does it appear to display properly and then change to being undefined, as that would then suggest it being corrupted by the Integration process in the BPM linked to the Service/Catalog entry.

Does the specific custom field setup on this service differ from the one setup for Internal Appointment, or is it the same service?

Cheers

Martyn

[SJEaton]

Yes it appears fine throughout the process until it reaches the stage where the integration process takes place and the acknowledgement email is triggered.  It does seem that it is being corrupted yes.  The custom field is the same for whether it's internal appointment or external appointment.

Sam

[Martyn Houghton]

@SJEaton

If you could post screen shot of the BPM prior to the integration call. It could be that it is branching differently and perhaps not retrieving a fresh copy of the Request Details to pick up the date entered into the details section prior to calling the integration stage.

Cheers

Martyn