Find workflows/mailers with particular subject

Sometimes I get support requests quoting an email received from HR.Net and asking to modify that email text or add more recipients. And then I need need to find that workflow containing the mailer that sent out that particular message. And given thousands of workflows, it can take days to find the exact workflow (unless you know exactly where to look for)

So I wrote this little SQL query that matches email subject to a workflow name/folder:


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_WORKFLOWMAILER mailer
  inner join OC_WORKFLOWS flow on mailer.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 mailer.htmlsubject like '%my subject%'
Posted in Uncategorized | Tagged , , | Leave a comment

Custom Script Validation on Screens… Again!

HR.Net is a mysterious beast! Something may work in one place, but same copy-pasted might not work in the other place. Last couple hours I have spent fighting Screen Validation again. I have already written about this feature a few times and this time none of these methods worked for me, even though I have not done anything different. No idea what happened with HR.Net and why old versions work in old screens, but not in new screens. Because HR.Net is full of shitty bugs, sometimes javascript function Page_ClientValidate is not available on a screen – I noticed that it disappears as soon as you add a grid to a screen. And in all my previous validations I was relying on this function to be present and called by the framework (“Page_ClientValidate” function which is provided by underlying .Net framework).

So after a lot of detective work I have found that save button triggers event HRnet.Form.UI.Buttons.Save.action and I could override this function. But “Save And Close” button was triggering different event HRnet.Form.UI.Buttons.SaveAndClose.action, so both of these should be overriden if both buttons are available.

Here is the basic script that ensures that some fields are filled in on the screen:

// override click on Save button
(function() {
  var proxied = HRnet.Form.UI.Buttons.Save.action;
  HRnet.Form.UI.Buttons.Save.action = function() {
    
    if(MyValidate() === false){
        return false;
    }
    
    return proxied.apply( this, arguments );
  };
})();

// override click on Save And Close button
(function() {
  var proxied = HRnet.Form.UI.Buttons.SaveAndClose.action;
  HRnet.Form.UI.Buttons.SaveAndClose.action = function() {
    
    if(MyValidate() === false){
        return false;
    }
    
    return proxied.apply( this, arguments );
  };
})();



function MyValidate(){
    var field1 = $HRnet("Field1").getDisplayValue();
    var field2 = $HRnet("Field2").getDisplayValue();
    var field2 = $HRnet("Field3").getDisplayValue();

    var ErrMsg ="";
    var validate = true;

    if (isBlank(field1)){
        ErrMsg += "You need to fill in 'Field 1'\n";
        validate = false;
    }

    if (isBlank(field2)){
        ErrMsg += "You need to fill in 'Field 2'\n";
        validate = false;
    }
   
    if (isBlank(field3)){
        ErrMsg += "You need to fill in 'Field 3'\n";
        validate = false;
    }
            
    if (!validate){
        alert(ErrMsg);
        return validate;
    }
}


function isBlank(val){
    if(val==null){
        return true;
    }
    for(var i=0;i
							
Posted in Uncategorized | Tagged , , | 2 Comments

Java Screen Field Validation

It has been a while since I had to do JavaScript in screens. And even longer since I done a validation on the screen. Since then there were changes in HR.Net which I have missed. And my old way to validate screens no longer works. But the fix is simple enough – all you need to do is add a parameter to the old script:

var orig_ValidatorOnSubmit = window.ValidatorOnSubmit;

window.ValidatorOnSubmit = function(validationGroup){
  var ErrMsg ='';
  var validate = true;
 
  // your custom validation goes here
	if (!validate){
          alert(ErrMsg);
		return false
	}
	return orig_ValidatorOnSubmit(validationGroup);
}
Posted in Uncategorized | Tagged , | 1 Comment

Find all users in a role

You know, HR.Net users have a primary role that defines their level of access to the data. Also there are many additional roles that allow to do other stuff, like using of navigator. But additional roles don’t give any data-permissions. That’s the way HR.Net security is done.

And sometimes you need to see all the users with a specific additional role. Here is the script:

select 
    OC_USERS.USERID, 
    primaryRole.ROLENAME as PrimaryRole
from [dbo].[OC_ROLELANGUAGES] 
    inner join OC_USERROLES on OC_ROLELANGUAGES.ROLEID = OC_USERROLES.ROLEID
    inner join OC_USERS on OC_USERS.USERID=OC_USERROLES.USERID
    inner join [OC_ROLELANGUAGES] primaryRole on primaryRole.ROLEID = Oc_users.ROLEID
where OC_ROLELANGUAGES.rolename='My Role'
order by primaryRole.ROLENAME
Posted in Uncategorized | Tagged , | Leave a comment

Updating of tables ends up with a messy error message about incompatible type

After modifying one of tables in C.Net I ended up with an error message saying “Incompatible type blah-blah-blah” on table save (sorry, don’t have exact words or screenshot). Turned out that I have changed type of one of the fields (from computed Text to Pick-List). This drove mad the security permission model and HR.Net could not re-generate the security views it uses in the background.

To figure out what exact permission was causing issues I came up with this script:

select 
    vs.name,
    definition
from sys.objects o
    join sys.sql_modules m on m.object_id = o.object_id
    cross join sys.views vs 
where o.object_id = object_id(vs.name)
  and o.type = 'V'
  and vs.name like '%[_]tablename[_]%'

This script outputs a list of all views generated by the system and outputs actual SQL that is behind the view. That way I could easily identify what role and what permission was causing the issue and could modify.

I was lucky I remembered the table name I modified, otherwise I would have to look through a gazillion of SQL Views and figure out which one is causing issues to fragile internals of HR.Net

Posted in Uncategorized | Tagged , | Leave a comment

Convert Report to be Landscape

Today I had to make a new report in HR.Net Document Explorer. Unfortunately version of HR.Net I’m using is buggy.

Every time you try to view a report preview, it crashes:

2014-11-03 00_54_03

Vizual claims that this error is because I’m running 64-bit OS, but their software is only supposed to work on 32-bit. But if I create a sub-report, I can run a preview on that. I think lousy coding standards are at fault here and nothing to do with 32/64-bit compatibility. And Vizual support just tries to make themselves look less bad.

Anyway, after some poking about, I have found a database table where these report definitions are stored as XML. And we can manipulate XML as we wish! One of the properties report XML-definition is <Orientation>. It is set to 1 for Portrait page layout and 2 for Landscape (I did compare different reports). So now all we have to do is replace this value in the database:

update [dbo].[OC_REPORTS_REPORTDEFINITIONS]
set DEFINITIONDATA = cast(REPLACE(cast(DEFINITIONDATA as nvarchar(max)), '1', '2') as ntext)
from [dbo].[OC_REPORTS_REPORTDEFINITIONS] repdef
   inner join OC_DOCUMENTS docs on repdef.DOCUMENTID = docs.DOCUMENTID
where docs.DOCUMENTNAME = 'My Report Name'

Replace “My Report Name” with name of your report. Before running this query, please make a copy of your report – there is no guarantee that this will work, also there is a chance that your report might be messed up beyond recognition.

Also if you have sub-reports, they also will become Landscape. If this is your goal, then no problem. If not, you’ll have to go through all sub-reports and modify them back – you are in luck here. Document Console allows to modify the sub-report orientation without throwing errors.

Posted in Uncategorized | Tagged , , | 5 Comments

Find a Report By Name

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

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

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

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