Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Rule triggered within a particular time frame

SOLVED
Go to solution
Highlighted
Peter Dent
Frequent Visitor

Rule triggered within a particular time frame

Hi,

Can anybody think of a way to have a Database rule which will send an email within one month of the Person Leave Date.

On the face of it it sounds simple. I can setup a rule to be triggered when the current date is one month from the leave date, but, that is assuming that the leave date is entered on the system more than one month in advance.

I need to be able to send the email if the date is one month or less from today's date and I want it to be only triggered once.

I'm using SD 4.5 and SP17.

Thanks.
15 REPLIES
George M. Meneg
Honored Contributor

Re: Rule triggered within a particular time frame

Well,

Create a custom duration field and let it have the value of substracting current date from leave date. If duration is <= 30 days then send email. If not, then update a second custom boolean field.

Then create a db rule that will be triggered when this custom boolean is true and validate its schedule 30 days before leave date. As action send an e-mail.

But have in mind that in case you have many people this will create an equally big number of scheduled tasks and scheduled tasks slow the system considerably.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George,

Thanks but, how do you create a custom duration field?

There are no duration fields available on the Person form. I've used the duration fields for an alarm type function on the Problem form. But like I said there are no duration fileds on the Person form?
George M. Meneg
Honored Contributor

Re: Rule triggered within a particular time frame

Hello Peter,

Are you sure that you don't have two custom duration fields? Because I do, though uninitialized, PerDuration1 and PerDuration2

To be exact *every* entity on custom fields has two duration fields.
menes fhtagn
George M. Meneg
Honored Contributor

Re: Rule triggered within a particular time frame

Execute the following query

select * from ifc_columns col, ifc_tables tab where
col.col_tab_oid=tab.tab_oid and tab.tab_name='itsm_per_custom_fields'
and col.col_name like 'pef_duration%'

it should return two rows.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George,

I ran the query and it returned two rows but, there are definately no Duration fields in the list of Custom Fields for Person.

Maybe I've got the wrong end of the stick do you mean Calulated Fields?

There are two duration type fields in there but I'm not sure how to use them?

When I click on the "Based On" drop down list there is nothing in the list?
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George,

Are you sure these fields are available in SP 17?

I've checked other items, like Problem and Service Call and there are no Duration type fields in there either?

We also have another instance of HPOV which is on SP12 and there's no custom duration type fields in there either?

The duration fields I used before, for the alarm function, was the system one already on the Problem form. I was able to use it because we didn't use it for anything else.
George M. Meneg
Honored Contributor

Re: Rule triggered within a particular time frame

Hello Peter,

The query you ran returned the person custom fields with name pef_duration1 and pef_duration2.

These are custom field of duration type.

Please run the following query

select ent.ent_name, lal.LAL_TEXT as 'Label',
(lower (tab.tab_acronym) + '.' + col.col_name) as 'column', tab.tab_name,
atr.atr_name, atr.atr_computed as 'computed?', atr.atr_customfield as 'custom?',
atr.atr_custfieldact as 'activated?' ,att.att_name as 'type',
ent.ent_name as parent_ent, (lower (tab2.tab_acronym) + '.' + col2.col_name)as 'column',
tab2.tab_name
from ifc_columns col
inner join ifc_tables tab on col.col_tab_oid=tab.tab_oid
inner join ifc_attributes atr on col.col_atr_oid=atr.atr_oid
inner join ifc_attrib_types att on att.att_oid=atr.atr_att_oid
inner join rep_labels_locale lal on atr.atr_lbl_oid=lal.lal_lbl_oid
inner join ifc_entities ent on col.col_ent_ref=ent.ent_oid
inner join ifc_entities ent2 on atr.atr_ent_oid=ent2.ent_oid
left outer join ifc_columns col2 on col.rel_col_to=col2.col_oid
left outer join ifc_tables tab2 on col2.col_tab_oid=tab2.tab_oid
where lal.lal_lng_oid=1033
and tab.tab_name = 'itsm_per_custom_fields' and att.att_name='Duration'
order by ent.ent_name asc

This should also returns two rows. This query returns details about the two custom duration fields available on person. As you are going to see there are custom fields, probably not activated and certainly not calculated.

If 'activated?' returns 1 it means that you 've already used these fields.

Unfortunately the layout of the custom fields is changed on SP18 and later so I can't post you a screenshot and show you where to find custom duration fields. But they must be here, on custom fields of persons.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George,

The SQL you posted returns the following error:

16:31:13 Error: ORA-00923: FROM keyword not found where expected

I'm using Toad to run the SQL.

Please can you advise?
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George,

Here's a screen shot of the table schema that I can see.

The two fields you mention are there but, they do not appear in the Admin Console > Custom Fields.

Any idea why that would be?
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

Also, I've been looking at the database table structure.

Some tables and therefore the Forms have built in Duration fields, such as Actual Duration, on the Service Call form.

In the table this is shown as a datatype of "Float".

The Custom fields, which I cannot see for some reason are shown in the tables as datatype "Float (32)"

Does this have any bearing on the problem?
George M. Meneg
Honored Contributor

Re: Rule triggered within a particular time frame

Hello Peter,

Since you are using TOAD I assume that your DB is on oracle. This query uses TRANSACT SQL syntax (since it is writen for MS SQL Server) and requires some modifications for running on Oracle.

Please try this one. If it doesn't run as is please ask your Oracle DBA for some help in order to convert it to PL/SQL.

select ent.ent_name, lal.LAL_TEXT as 'Label',
col.col_name as 'column', tab.tab_name,
atr.atr_name, atr.atr_computed as 'computed?', atr.atr_customfield as 'custom?',
atr.atr_custfieldact as 'activated?' ,att.att_name as 'type',
ent.ent_name as parent_ent, col2.col_name as 'column',
tab2.tab_name
from ifc_columns col
inner join ifc_tables tab on col.col_tab_oid=tab.tab_oid
inner join ifc_attributes atr on col.col_atr_oid=atr.atr_oid
inner join ifc_attrib_types att on att.att_oid=atr.atr_att_oid
inner join rep_labels_locale lal on atr.atr_lbl_oid=lal.lal_lbl_oid
inner join ifc_entities ent on col.col_ent_ref=ent.ent_oid
inner join ifc_entities ent2 on atr.atr_ent_oid=ent2.ent_oid
left outer join ifc_columns col2 on col.rel_col_to=col2.col_oid
left outer join ifc_tables tab2 on col2.col_tab_oid=tab2.tab_oid
where lal.lal_lng_oid=1033
and tab.tab_name = 'itsm_per_custom_fields' and att.att_name='Duration'
order by ent.ent_name asc

Duration are indeed stored as float on the DB .

I used custom duration fields (on service calls and work orders) long time before SP17.

Are you sure that you have activated this custom fields? In order to be appear to the forms the fields must be activated.

Go to Administrator Console/Data/Custom Fields and double click "Person". From the drop down list at the top of the form you should be able to find the two custom duration fields. Pick one, give it a name, select a format, enable it either for all categories or specify the categories on which you 'd like this field to be active.

Once you activate the custom field you'd be able to use it at the forms/db/ui rules/views etc.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George, firstly thanks for sticking with this, its mjuch appreciated.

Sorry if I didn't explain properly before but what I meant was that the two duration fields that we are speaking of do not apear in the list of custom fields, where you would normally activate them.

There are no custom duration fields for any of the items, Person, Service Call, Incident, Organisation, Project, etc.

The only fields that show in the Custom fields list for Person are of type Text (40/255/65535), Date, Code, Boolean, Number and one Workgroup and Organisation.

There are no other type, no Duration type fields at all for any item?

Is there a system setting somewhere to enable their use?
George M. Meneg
Honored Contributor
Solution

Re: Rule triggered within a particular time frame

Hello Peter,

Ok, I got it now. The duration are of type NUMBER and not of type duration. Browse the custom fields on person and you should find
the fields "PerDuration1 (Number)" and "PerDuration2 (Number)" with names "PerDuration1" and "PerDuration2" respectively.

Activate one of them (either for all categories or for specific categories) and you'll be able to use it.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

George,

OK, got them, thanks for sticking with it!

Why didn't I see them before? Doh
Peter Dent
Frequent Visitor

Re: Rule triggered within a particular time frame

As above.
//Add this to "OnDomLoad" event