Data Extraction?

[Guest Paul Alexander]

I'm not even sure if the title is actually what I want....but here goes!

We have requests which are logged automatically from email when someone leaves the company. The email is sent in a template from a 3rd party HR application. 

What we'd like to be able to do is to extract the leavers' pay ref (which is always on the 5th line down in the email and is prefixed by the words 'Pay Ref:' and then use that pay ref number to disable the user account in Hornbill (and later AD once the ITOM updates have come out)

Is this kind of data extraction possible with tools available to us from Hornbill? If not, COULD it be done please?

 

 

[Victor]

@Paul Alexander does this help? 

 

 

[Steven Boardman]

@Paul Alexander is it possible for the request to be passed via API call rather than email from the HR System? if so and it is possible to invoke a logrequest API call, then you could look to include the pay ref value as an input param?

[Guest Paul Alexander]

All sorted thanks @Steven Boardman and @Victor

It seems that I was struggling because i didn't realise that indexOf()  in javascript is case sensitive (so indexof() wasn't working),

and that I didn't need substring() I needed substr()

I've got it now though....thanks for the help! 

 

[samwoo]

On 7/17/2019 at 9:44 AM, Paul Alexander said:

All sorted thanks @Steven Boardman and @Victor

It seems that I was struggling because i didn't realise that indexOf()  in javascript is case sensitive (so indexof() wasn't working),

and that I didn't need substring() I needed substr()

I've got it now though....thanks for the help! 

 

Hi Paul,

If you are doing all of this via Hornbill, are you able to provide details?

Thanks,

Samuel

[Guest Paul Alexander]

Hi @samwoo

I haven't got VERY far with this yet as it's a bit of a side project.....but basically, all I've done is this:

• I take the description of the request and (as these requests are all logged from an email template) find where the words 'Pay Ref' is found in the template, and push that into a custom field (using                                                                      &[global["flowcode"]["description"].indexOf('Pay Ref ') + 7])
• I then use a 'get request info' node to make sure I've got the latest info (in other words, the start point for the Pay Ref number)
• Our PayRefs are all 7 digits long, so I use: &[global["flowcode"]["description"].substr (global["flowcode"]["customField26"], 7)]
• This picks out the next 7 characters in the email AFTER the end of the Pay Ref location. 
• I can then use THAT 'find' to open up a search page in Hornbill looking for the pay ref number of this person using https://live.hornbill.com/[Hornbill Instance Name]/coworkers/?query=[the pay ref number goes here]&page=1 (I will be able to do this once I get all of the pay ref numbers into a field in the user account form which is indexed - so can't do it just yet!). What this does is tell me whether the user has a hornbill account, in which case I can do 'stuff' with it. If they don't have a Hornbill account then they don't have an AD account so the request can be stopped there.

 

As I said, I haven't got very far and it's not usable yet - but I'm thinking the info can be added to a custom button when a Leavers request comes in. It's all pretty basic, but it's keeping me off the streets, and might open up ideas for other things we can do with other templated emails maybe.

 

 

[samwoo]

Fantastic @Paul Alexander, genius even.

Can you keep us updated with this as a story because you have (I reckon) entered the realms of the (relatively) unknown for a lot of Hornbill users. 

I've seen the commands you've mentioned throughout the forums, but i haven't seen it officially documented. (I will check the wiki to double check) 

If there is nothing on the wiki, could Hornbill add a page on advanced flowcodes usages and examples, such as what Paul has done? 

Thanks, 

Samuel

[SimonSheldon]

That would be very useful!

[Guest Paul Alexander]

To be honest, all I've done is taken a look at JavaScript coding (https://www.w3schools.com/js/default.asp) as I'm really not very good with this sort of stuff! 

I've just messed with it and (so far) it seems to be working ok!