Yet Another Successful Project

And yet another successful project with HR.Net is coming to an end. Oh how I miss those sleepless debugging nights and clients with tears of joy!

Anyway, I’d like to remind my readers that I am available to work on your HR.Net project on a contract basis. My rates are moderate and knowledge of HR.Net is thorough (just read this blog). Experience ranges from HR to Oil and Gas systems, even attempted to program Genetic Algorithm in HR.Net (luckily, unsuccessful. HR.Net is just not a right tool for this).

So if you are looking to get some heavy-lifting done, drop me a line on mail@amvcomp.co.uk and we’ll discuss your requirements and how to get them implemented.

Posted in Uncategorized | 2 Comments

Find all workflows that email particular address

Quite often people within organisation change roles or leave. And if your HR.Net workflows were programmed to email that person, you’ll have to go into these workflows and change the address. But if you have thousands of workflows? you don’t want to go through each one and check if it emails that particular address. For these cases you should use this query:

select 
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
	flow.workflowname,
	flow.description,
	flow.allowstart,
	params.WORKFLOWID,
	params.STAGEID,
	params.DATA 
from OC_WORKFLOWSTAGEPARAMETERS params
  inner join OC_WORKFLOWS flow on params.workflowid = flow.workflowid
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where params.DATA = 'joe.doe@example.com' and params.datatype=0
order by pppf.folder_name, ppf.folder_name, pf.folder_name, f.folder_name, flow.workflowname

This will show you all the workflows (including folder structure) and stage names that email to joe.doe@example.com.
To mass-update the email address you need to update OC_WORKFLOWSTAGEPARAMETERS table. In particular Data field.
In that table, as you may have guessed by the name of it, there are parameters for different workflow stages, including Mailers, Mail-Merges and Send-Form.

To change the address from Joe.Doe to John.Smith, you need to execute this simple query:

update OC_WORKFLOWSTAGEPARAMETERS
set DATA ='joe.doe@example.com'
where datatype=0 and DATA = 'john.smith@example.com'

And that’s all there is to it!

Posted in Uncategorized | Tagged , , | Leave a comment

Switch to Attached Documents Page

A while back, when I worked with HR.Net version 3, I came up with a bunch of random JavaScript hacks.
Now I’m working with version 4. JavaScript API has been changed and some of my tricks do not work in the new version. One of the things that was failing was switching to Attached Documents Page.

Today I had a pretty long but productive session in Chrome JavaScript debugger (did you know you can run HR.Net in Chrome??) and came up with a new script for Attached Documents:

$(window).load(function(){
	HRnet.Form.ScreenActions.ShowAttachedDocuments()
});

That is slightly neater than the old version, where I did not know about jquery $(document).ready() function and how to use it, so had to wrap the call to the function inside of a timer.
Now, here I can’t use $(document).ready() because somewhere else in Visual’s scripts there is document.ready function with page window resize that will not get triggered if I call ShowAttachedDocuments() inside of it as well. But $(window).load() is fired after all $(document).ready() are executed, so the window does get resized properly and we get to go to the page we want – attached documents.

Posted in Uncategorized | Tagged , , , | Leave a comment

Autosave a screen

I had a question asked ages ago about Auto-Save of a screen. And Dan Hutson have just posted an answer to this question:

// auto-save after this many minutes
var countDown = 15; 

// get current time at screen load
var startTime = new Date(); 

// calculate one minute
var oneMinute = 1000*60; 

// run clock function every minute
var int = self.setInterval("clock()",oneMinute); 

function clock() {
    // get time now
    var timeNow = new Date(); 
    
    var countUp = Math.ceil((timeNow.getTime() - startTime.getTime()) / oneMinute); 
    // calculate differences in times
    
    // calculate time remaining until auto-save
    var minsRem = countDown - countUp; 
    
    // update edit box on screen with number of minutes remaining (optional)
    $HRnet("EditBox3").setDisplayValue(minsRem + " minute\(s\)"); 
    
    if(minsRem == 0) {
    
        // save screen if auto-save time has elapsed
        HRnet.Form.UI.Buttons.Save.doClick(); 
        
        // reset current time
        startTime = new Date(); 
    }
}

// call clock function on first load
clock(); 

This is all Dan’s script and it shows the countdown before every auto-save. The main function here is HRnet.Form.UI.Buttons.Save.doClick(); that does save the data, the rest of the code is count-down and displaying.

So if you don’t need any of this, you can just call HRnet.Form.UI.Buttons.Save.doClick(); whenever you need, or on timer:

$(document).ready(function() {
    window.setInterval(HRnet.Form.UI.Buttons.Save.doClick, 5000);
});

This would click Save button every five seconds.
I believe this would not work in a workflow, as you can’t save the workflow – you need to submit the workflow and that can happen only once. And would really you want to submit the workflow many times, when you have some complex logic behind it (i.e. send email to everybody in your company)??

Thanks for the idea, Dan!!

Posted in Uncategorized | Tagged , , , , | Leave a comment

Wild-card for searches

Some things in my work I take for granted and don’t realise that can be not obvious. One of my readers Steph noted, HR.Net uses percent sign % as a wild-card in searches. So whenever you do search for a person, a workflow, any other search dialogue, try using "some_wor%" to get all results matching the wild-card: some_word, some_work, some_working1, etc.

Thanks, Steph for reminding about simple stuff -)

Posted in Uncategorized | 4 Comments

jQuery

In case you have not realised, HR.Net is using jQuery for some of it’s functionality on screens.
So power of jQuery is at your mercy when you write the Custom Scripts for the screens.
Shame that the version of jQuery is heavily outdated: current release (today is 6 Nov 2012) is 1.8.2, but HR.Net uses version 1.2.6 which was released on May 24th, 2008. More than 4 year old technology.

Anyway, enough slagging off guys from Vizual, I’m sure they are not fond of their technical debt they already have…

What I was going to say that AJAX calls did exist in jQuery even in 2008. So I don’t see a reason why not use that on your HR.Net screens.
For example one of the usages I have found is validation of new records against database even before they go into the database.
The script I’m looking at just now is validating new Maternity record against all the existing Absence records:

function sendRequest(){
	$.ajax({
		type: "GET",
		url: "/hrnetsupport/MaternityValidator.php",
		data: { person_id: "EF831A66-CC57-45F5-8435-001BF5E77DA3", startdate: "01/01/2001", enddate: "01/05/2002" },
		dataType: "json",
		error: function(data) { 
			alert("Some server error. Please report to your system administrator: " + data.message); },
		success: function(data) {
			if (data.success !== true) {
				alert(data.message)
			}
		}
	});
}

I’m sure that does not make sense to you and looks like garbage. I’m sure it is. I’ve just quickly scratched that in a notepad, did not check if that works. But if you know what jQuery is, then you’ve got the idea. I hope I don’t need to tell you, that you need to provide infrastructure and logic for MaternityValidator.php as well. (PHP is not essential, any other server side language can do the work)
If you don’t know jQuery, but need some complex data validation, it is worth investing your time in studying jQuery and AJAX.
Or you can hire me to script up the validations for you -))

Posted in Uncategorized | Tagged , , , | 7 Comments

Find screen by base table

Find all the screens that modify particular table

select 
	pppf.folder_name as [Folder],  
	ppf.folder_name as [Folder],  
	pf.folder_name as [Folder],  
	f.folder_name as [Folder],  
	scr_lang.SCREENNAME,
	scr_lang.DESCRIPTION,
	scr.*
from OC_SCREENS scr
	inner join OC_SCREENLANGUAGES scr_lang on scr.SCREENID = scr_lang.SCREENID
	left join OC_ADMINCONSOLE_FOLDERS f on scr.folderid = f.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id  
where TABLENAME = 'Absence'
Posted in Uncategorized | Tagged , | 3 Comments

Find workflows by name

Ever tried to look through all the folders with workflows searching the one that fails? Know only the name? Use this sql to show you folder names and workflows mathing the name search.

select 
  pppf.folder_name as [Folder],
  ppf.folder_name as [Folder],
  pf.folder_name as [Folder],
  f.folder_name as [Folder],
  flow.workflowname,
  flow.description,
  flow.allowstart
from OC_WORKFLOWS flow
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where 
  flow.WORKFLOWNAME like '%People%'
order by flow.workflowname
Posted in Uncategorized | Tagged , , | Leave a comment

Find screens by name

Ever struggled to find the screen called “something” in a tree of folders?
This script will give you full location of the screen with all the folders. Just change the name in the where-clause and run this against the HRNET database.

select 
	pppf.folder_name as [Folder],  
	ppf.folder_name as [Folder],  
	pf.folder_name as [Folder],  
	f.folder_name as [Folder],  
	scr_lang.SCREENNAME,
	scr_lang.DESCRIPTION,
	scr.*
from OC_SCREENS scr
	inner join OC_SCREENLANGUAGES scr_lang on scr.SCREENID = scr_lang.SCREENID
	left join OC_ADMINCONSOLE_FOLDERS f on scr.folderid = f.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id  
where SCREENNAME like '%SOMETHING%'
Posted in Uncategorized | Tagged , , , | Leave a comment

Find workflows triggered on particular fields

This blog is not dead! It’s been a while since my last post here, but I’m back with some fresh ideas and scripts. Thanks to my new assignment with HR.Net.

If you would like to find all the workflow triggers that are triggered on a particular field in a particular table, run this sql against hrnet database

select 
	pppf.folder_name as [Folder],  
	ppf.folder_name as [Folder],  
	pf.folder_name as [Folder],  
	f.folder_name as [Folder],  
	tbl.TRIGGERNAME,  
	tbl.description,  
	tbl.ONINSERT,
	tbl.ONUPDATE,
	tbl.ONDELETE
from OC_WORKFLOWTABLETRIGGERS tbl
	inner join OC_WORKFLOWTABLETRIGGERFIELDS flds on tbl.TRIGGERNAME = flds.TRIGGERNAME
	left join OC_ADMINCONSOLE_FOLDERS f on tbl.folderid = f.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id  
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id  
where tbl.TABLENAME='JOBDETAIL' and FIELDNAME='DATEOFLEAVING'
Posted in Uncategorized | Tagged , , , | Leave a comment