Subscribe community to a user

Posted on June 9, 2011 by admin

I’ve been asked today by one of the reader “How to subscribe a user to a community though a back-end”.
And here is the answer to the question:

There is a stored procedure that will do that for you. The name is [SPOC_COMMUNITIES_SUBSCRIBE] that takes Comminity_ID and USERID as parameters.

You can find Community_ID in table OC_COMMUNITIES and USERID is a username for that user.

This procedure just adds another record in table OC_COMMUNITYSUBSCRIPTIONS with USERID and Community_ID, also checks for duplicate.

Posted in Uncategorized | Tagged , | Leave a comment

JavaScript events triggered on screen via custom script

Posted on April 25, 2011 by admin

JavaScript has various event handlers, like onChange or onMouseOver. In HR.Net it is not obvious how things work and how to trigger functions based on events.. Here are some tips:

This is how to trigger event when date field on the screen is changed..

var date = $('#DATEFIELDNAME_txtInput');
date.blur(function() {
  required_function();
});

Where DATEFIELDNAME is a name of the date field; required_function() is something that you need to run when date is changed. For some reason onChange event did not work properly for me.

Text fields events are not straight forward either. 

var text_input = document.getElementById("TEXTFIELDNAME_txtInput"); 
text_input.onblur = function(){ required_function();};
text_input.onchange = function(){required_function();};
text_input.onkeyup = function(){required_function();};

Where TEXTFIELDNAME is a name of the date field.

HR.Net is wrapping every form element into a span-container and you can not trigger events on these containers, hence you need to go directly to HTML the object, hence we are adding “_txtInput” after name of the field name.
Inbuilt function $HRnet(“fieldname”) has a limited use and not always helpful.

Also you can reference fields in JQuery style:

var Date = $('#DATEFIELDNAME_txtInput');
Posted in Uncategorized | Tagged , , , , | 4 Comments

Execute Stored Procedures From Screens

Posted on April 14, 2011 by admin

We discovered a way to execute SQL Stored procedures from HRNET screens, via JavaScript custom script. Nothing new for the world of JS, but new for us. I did not know you can run sql queries from JavaScript…
Here is the example:

function executeStoredProcedue(){
   var connection = new ActiveXObject("ADODB.Connection"); 
   var recSet = new ActiveXObject("ADODB.Recordset"); 
   var cmd = new ActiveXObject("ADODB.Command");
   var ConnectionString = "Data Source=SQLSERVER;Provider=SQLOLEDB;User Id=user;Password=password;Persist Security Info=True;Initial Catalogue=HRNET_DATABASE;" ;

   var record_ID =  document.getElementById("hdRecordID").value;

   connection.Open(ConnectionString,"user","password",-1);
   cmd.ActiveConnection = ConnectionString ;
   cmd.CommandText = "HRNETDatabase.DBO.name_of_stored_procedure" ;
   cmd.CommandType = 4 ;
   recset = cmd.Execute(null,[record_ID]);
}

The only drawback is that every time you have this running in IE, you will get a security warning from ActiveX. Also this will not work in Firefox, if your clients will happen to use it.

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

Change column collation

Posted on March 23, 2011 by admin

This is how you can change text column collation to something else

ALTER TABLE tableName
ALTER COLUMN columnName nvarchar(1000) COLLATE Latin1_General_CI_AS
Posted in Uncategorized | Tagged | Leave a comment

Import into SQL Server from CSV

Posted on March 23, 2011 by admin

This is how to import data from CSV into SQL Server

BULK INSERT  TableName
FROM 'c:\data.csv'
WITH
(
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)
GO
Posted in Uncategorized | Tagged , | Leave a comment

Join Update

Posted on March 23, 2011 by admin

Update table with entries from other table, with join

update table
set 

	field1 = other_table.field1,

	field2 = other_table.field2

from table
	inner join other_table on other_table.field3=table.field3
Posted in Uncategorized | Tagged , | Leave a comment

Export data as XML

Posted on March 23, 2011 by admin

This is how you export XML from SQL Server

select * 
from table
for xml auto, elements
Posted in Uncategorized | Tagged , | Leave a comment

List users per role

Posted on February 16, 2011 by admin

Admin Console of HR.Net allows only to list users by their primary role. And secondary roles are in a shade.
To display all users that belong to a particular role (primary or secondary) you can use this bit of sql: 

select ur.userid, rl.rolename
from oc_roles r
	inner join oc_rolelanguages rl on r.roleid = rl.roleid
	inner join oc_userroles ur on ur.roleid = r.roleid
where rl.rolename = 'Role Name'
Posted in Uncategorized | Tagged , , , , , | Leave a comment

Java Script Validation in Workflow

Posted on January 7, 2011 by admin

This method no longer works with later versions of HR.Net. See update.

Screen validation has been mastered by me a long time ago and we are using it all over the place.
But workflow screen has not been properly validated before. Today I digged through the page code and discovered the way to do workflow screen validation. It works the same way as screen validation, but you need to override function with different name. See the example below: 

var orig_ValidatorOnSubmit = window.ValidatorOnSubmit;

/**
 * If this function returns false, the workflow is not going to be saved. 
 * Beware that you need to show an error message to user, otherwise they would be 
 * left with non-working form and no way of knowing about the problem 
 * If this function returns true, form is going to be saved fine.
 */

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

Get parent_id from Javascript

Posted on December 30, 2010 by admin

Sometimes you need to get current record properties from the screen.
Here is how to find Parent Record ID (if available), Current Record ID and table name for the current screen.
Please note that a record can have no parent table, or can can have no current_id (when creating a new record and have not saved yet)

var parent_id = document.getElementById("hdParentRecordID").value;

var current_record_id = document.getElementById("hdRecordID").value;

var tableName = document.getElementById("hdTableName").value;
Posted in Uncategorized | Tagged , , , | Leave a comment