Find a Report By Name

Posted on October 19, 2014 by admin

It’s been a while since the last post. But the blog is not dead, I keep working with HR.Net and when I find good enough reason to post here, I do.

Here is the new gem I had to discover. In attempt to delete an unused column from an unused table, I was present with dialog saying “Can’t delete field, it is used in a report”. Good on Vizual that they are showing reports that depend on fields. But how they do it:

2014-10-20 00_08_50-Ts - ts.cornerstone.org.uk - Remote Desktop Connection

Yep, thanks. This field is used in report “Test Blah”. But where the hell is this report? In the system I support just now there are over a thousand of reports with about a hundred of folders.

So this script will help you show the location of a report in your system:

select 
    pppf.foldername as [Folder],
    ppf.foldername as [Folder],
    pf.foldername as [Folder],
    f.foldername as [Folder],
    d.*
from OC_DOCUMENTS d
	left join dbo.OC_DOCUMENTFOLDERS f on d.folderid = f.folderid
	left join dbo.OC_DOCUMENTFOLDERS pf on f.PARENTFOLDERID = pf.folderid
	left join dbo.OC_DOCUMENTFOLDERS ppf on pf.PARENTFOLDERID = ppf.folderid
	left join dbo.OC_DOCUMENTFOLDERS pppf on ppf.PARENTFOLDERID = pppf.folderid
where d.DOCUMENTNAME = 'Test Blah'
Posted in Uncategorized | Tagged , | Leave a comment

Find all workflows that use a picklist, by picklist name

Posted on August 7, 2014 by admin

One of my readers did ask if there is a way to find all the workflows that use a particular picklist.
A little bit of digging and here you go:

select 
    pppf.folder_name as [Folder],
    ppf.folder_name as [Folder],
    pf.folder_name as [Folder],
    f.folder_name as [Folder],
    w.WORKFLOWNAME, 
    pl.SHORTDESCRIPTION as PicklistName 
from OC_WORKFLOWS w
	inner join OC_SCREENS s on w.SCREENID = s.SCREENID
	inner join OC_SCREENCONTROLS sc on s.SCREENID = sc.SCREENID
	inner join OC_PICKLISTS p on p.PICKLISTID = sc.WORKFLOWPICKLISTID
	inner join OC_PICKLISTLANGUAGES pl on pl.PICKLISTID = p.PICKLISTID
	left join OC_ADMINCONSOLE_FOLDERS f on w.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 pl.SHORTDESCRIPTION = 'Picklist Name'
Posted in Uncategorized | Tagged , , , , | Leave a comment

Lost a picklist? Find it with a query

Posted on December 27, 2013 by admin

Many times over I did spend a lot of time looking for a particular picklist. I know the name, but have no idea what folder it is in. And if you have more than just a few of them, it is sometimes impossible to find what you are looking for without help. Here is how you find a picklist by a name: 

select 
  pppf.folder_name as [Folder],
  ppf.folder_name as [Folder],
  pf.folder_name as [Folder],
  f.folder_name as [Folder],
  pickLang.SHORTDESCRIPTION
from OC_PICKLISTS pick
	inner join OC_PICKLISTLANGUAGES pickLang on pick.PICKLISTID = pickLang.PICKLISTID
  left join OC_ADMINCONSOLE_FOLDERS f on pick.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 pickLang.SHORTDESCRIPTION = 'Picklist Name'
Posted in Uncategorized | Tagged , | 3 Comments

Yet Another Successful Project

Posted on February 12, 2013 by admin

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

Posted on February 8, 2013 by admin

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

Posted on February 1, 2013 by admin

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

Posted on November 30, 2012 by admin

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

Posted on November 14, 2012 by admin

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

Posted on November 6, 2012 by admin

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

Posted on June 22, 2012 by admin

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