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

Find start and end of week and day in SQL Server

Yet another attempt to build up a library of SQL Server functions. As I found out, not all my previous functions did work properly in all environments.

-- finds start of current week for the provided date
CREATE FUNCTION [dbo].[fnStartOfWeek]
(
  @INPUTDATE DATETIME
)
RETURNS DATETIME

AS
BEGIN
  -- THIS does not work in function.
  -- SET DATEFIRST 1 -- set monday to be the first day of week.

  DECLARE @DOW INT -- to store day of week
  SET @DOW = DATEPART(DW, @INPUTDATE)

  -- Magic convertion of monday to 1, tuesday to 2, etc.
  -- irrespect what SQL server thinks about start of the week.
  -- But here we have sunday marked as 0, but we fix this later.
  SET @DOW = (@DOW + @@DATEFIRST - 1) %7
  IF @DOW = 0 SET @DOW = 7 -- fix for sunday

  RETURN dbo.fnStartOfDay( DATEADD(DD, 1 - @DOW,@INPUTDATE) )

END

GO


-- finds end of the current week for the provided date
CREATE FUNCTION [fnEndOfWeek]
(
	@INPUTDATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @WEEKSTART DATeTIME,
			@WEEKEND DATETIME
	SET @WEEKSTART = dbo.fnStartOfWeek(@INPUTDATE) -- Find start of this week.
	SET @WEEKEND = DATEADD(week,1, @WEEKSTART)		-- add one week to the start of the week.
	SET @WEEKEND = DATEADD(second, -1, @WEEKEND)	-- take out one second from the end of the week
	RETURN @WEEKEND
END

GO 

-- Finds end of the day for the provided date
CREATE FUNCTION [fnEndOfDay]
(
	@DATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
	RETURN DATEADD(s,-1,DATEADD(d, DATEDIFF(d,0,@DATE)+1,0))
END

GO

-- Finds Start of the day for the provided day
CREATE FUNCTION [fnStartOfDay]
(
	@DATE DATETIME
)
RETURNS DATETIME
AS
BEGIN
	Return DATEADD(d, DATEDIFF(d,0,@DATE),0)
END
Posted in Uncategorized | Tagged | Leave a comment

Disable Form Validation When Saving Workflow as a Draft

Again, thanks Dan Huston for a great question which was this:
Is it possible to distinguish between when a workflow form is being submitted and when the user is saving it to their drafts folder?

We have times when the manager does not have certain information and therefore wants to save the form rather than submit it, but the page validation script still fires in this instance.

We certainly can disable screen validation when saving as a draft. Here is the script. As usual, see the principal behind the script and adjust as you require. You might want to have some validation of data for draft…

/**

 * SaveDraft() function is executed in workflow screen when user is saving a draft.
 * This also executes ValidatorOnSubmit() that validates all the information on the form to be consistent.
 */



//save original SaveDraft function 
var orig_saveDraft = window.SaveDraft;

//Override SaveDraft function 
window.SaveDraft = function(){

  // disable validating function
  window.ValidatorOnSubmit = function(){};
 
  // execute original SaveDraft() function
  return orig_saveDraft();
}
Posted in Uncategorized | Tagged , , , , , , , | 3 Comments

Validate repeating section

Thanks to Dan Huston for this script. I have not tried it myself, but I trust Dan on this matter.

This script is going through all the fields in repeating sections and checks if value is provided.
In repeating section each label and field is suffixed with xxROWxxn, where n = 0,1,2,3…. etc.

Please see the idea behind this script and adjust to your needs.

var ErrorText = "";

function check_repeating_section(field_name, longfield_name)
{
   var field;
   var validate = true;
   // loop to go from zero, until we no longer can find an field
   for (var i = 0; i != -1; i++)
   {
      field = document.getElementById(field_name + "xxROWxx" + i.toString() + "_txtInput"); //get field by ID
      if (field === null) //check if field exists
      {
         i=-1;
         return; // if no field, we exit the function
      }
      r = i + 1
      if(field.value === "")
      {
       ErrorText += "Please enter a value for " + longfield_name + " in row " + r.toString() + ". \n";
       validate = false;
      }
   } //end of for-loop

   return validate // if field there, we return it's value
   }

   if(!check_repeating_section("CourseName", "Course Name"))
   {
    alert(ErrorText);
   }
Posted in Uncategorized | Tagged , , | Leave a comment

Find screens that edit particular field in a table

As a follow-up from yesterday post about finding workflows that change field in a table, today I needed to find all the screens that contain particular field from a table.

Here is the SQL query:

select 
  pppf.folder_name as [Folder],
  ppf.folder_name as [Folder],
  pf.folder_name as [Folder],
  f.folder_name as [Folder],
  scrname.screenname
from OC_SCREENCONTROLS ctrl 
  inner join OC_SCREENLANGUAGES scrname on scrname.screenid=ctrl.screenid
  inner join OC_SCREENS scr on scr.screenid=ctrl.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 ctrl.tablename = 'JOBDETAIL' and ctrl.fieldname = 'PRINCIPALJOB'
Posted in Uncategorized | Tagged , , , , | Leave a comment

Find workflows that change particular table field

Today my task was to find out what workflows are changing particular field within particular table. I could go through all the workflows and all the data-writers and check. But I have hundreds to choose from, I’ll spend all day trying to do it. And there will be no guarantee that I find them all and not missed anything.

For that task I came up with SQL request that shows you all the workflows, and even folder structure where to find them.

Here you go:

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_WORKFLOWDATAWRITERFIELDS fields
  inner join OC_WORKFLOWS flow on fields.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 
  fields.tablename = 'JOBDETAIL' 
  and fields.fieldname = 'PRINCIPALJOB'
order by flow.workflowname

Obviously you’ll need to replace FIELDNAME and TABLENAME to required to you. I’m sure there would be a better way to find out folder structure, but for this purposes this will be as good as any: quick and dirty.

Posted in Uncategorized | Tagged , , , , , | 1 Comment

Intersting questions and answers

Recently I had a lot of people emailing me and saying “thanks” for the blog, as it helped them out in one way or another.

You are welcome!

Glad it helps somebody else, not just me. I’ll try to post useful tips here.
Just don’t be shy to ask a question. Sometimes interesting questions lead to an interesting post here -)

And if you really want to say thanks, you can check out my Wish-list on Amazon and gift me something. I’ll be flattered -))

Posted in Uncategorized | 4 Comments

Determine if record is saved

Sometimes we need to determine in JavaScript if record is already saved or not. You can use this function for this:

/**
 * Check if we are creating a new record,
 */
function areCreatingNewRecord(){
  var record_id = document.getElementById("hdRecordID").value;
  if (record_id == '00000000-0000-0000-0000-000000000000'){
    return true;
  }else{
    return false;
  }
}
Posted in Uncategorized | Tagged , , | 4 Comments