How to filter a Database View

[nived_k27]

Hi All,

In our instance we are having tables of change requests and change tasks.

Every change request will have number of change tasks, that will get triggered according to the order defined.

I am trying to create a Database View by adding these tables together, that will shows only the lowest order active change task for an active change request.

Can anyone help me on using filters on the where clause.

[Adam Stout]

There is no need for a view since there is a relationship you can dot walkthrough.  You need to base your report on change task (and dot walk to the change fields you need in the report).  The trick with the latest change task only.  You will need a scripted filter that does this that handles this custom logic.

Alternatively, you could look at adding a field to the change that is a reference to the lowest order active task and a BR on change task that updates the BR to calculate this when the change tasks change the active or order state.  This is probably the best solution which would make for easy reporting and fast processing.  In this case, your report would be based on change (and no change task) and you would dot walk to the task through the new reference field.

[nived_k27]

Hi Adam,

Thank you for your response.

I have created a new string field 'CTASK'(u_ctask) on the change form, and created a before update BR on 'Change Request' table .

I have used the following code on the BR,but it is not working.

var grctask= new GlideRecord('change_task');
grctask.addQuery('active','=','true');
grctask.addQuery('change_request','=',current.number);

grctask.query();

if(grctask.next())
{
grctask.orderBy('short_description');
current.u_ctask=grctask.number;
current.update();
}

  Could you please help?

[Adam Stout]

1) It should be a reference, not a string field.

2) Why are you sorting by short description?  Does this contain a string that will sort correctly?  This seems very fragile.

3) What table is the BR on?  It should be on the change_task table (not request where the new field is)

[nived_k27]

Hi Adam,

Thank you for your help.