Query Start Date >= This Year and End Date <= Next Year

[F_bio Gon_alves]

Hi SNC,

I am struggling with a query on resource_plan table.

I have a custom Service Portal widget to which I added this line on the HTML in order to exhibit a label with the number of records using a given filter:

<span class="label label-as-badge label-danger">{{c.data.resourcePlanCount}}</span>

In my Server Script I have the following code:

var resourcePlans = new GlideRecord("resource_plan");

resourcePlans.addQuery("state", "11");

resourcePlans.addQuery("start_date", ">=", "01-01-2017");

resourcePlans.addQuery("end_date", "<=", "01-01-2018");

resourcePlans.addQuery("portfolio", "524ceafddb4b7e0087f371910f9619bd");

resourcePlans.query();

data.resourcePlanCount = resourcePlans.getRowCount();

And here is my problem: the first ("state") and last ("portfolio") queries execute without any problem, but I simply cannot manage to execute the queries to the "start_date" and "end_date" fields.
I know this is not the best way to perform it (using hardcoded dates), but even using this approach I cannot make it work. Am I using the wrong syntax ">=" and "<="?

Thanks in advance,

Fábio Gonçalves

[F_bio Gon_alves]

Let me take this opportunity to ask you 2 additional questions, if you don't mind:

1. How can I add a dynamic behavior to this query? i.e. be capable of filter resource_plan table by ">=" than "current year" and "<=" than "next year", instead of using hard-coded values as I have now?

2. Following the same logic, how can I dynamically filter a given table using the logged-in user ID? I tried this line on my widget's Server Script but it didn't work:

liveMessages.addQuery("profile", gs.getUserID());

"profile" is an OOTB field that uses an User ID. I just am not able to pass it using gs.getUserID().

Any ideas on how to achieve this?

Thanks again.

Fábio Gonçalves

[sergiu_panaite]

For:

1. You will need to create a script include to calculate the date dynamically and call that script in the filter. A good example:

Dynamic Expiration date and subsequent Task

2. In this case 'profile' field is a reference field to Live Profile table (live_profile) and not sys_user. This means using gs.getUserID() you're getting the sys_id from sys_user rather than live_profile table which most probably contains a different sys_id for the same user.

As example on OOB instance I have for admin user (System Administrator) different sys_id's even if it's same user:

sys_user: 6816f79cc0a8016401c5a33be04be441

live_profile: e3949a5c0a0a3c6d01e089a708d3ebef

So if you need to use current user you will need to something like this:

var myUserObject = gs.getUser();
gs.print(myUserObject.getDisplayName());

var gr1 = new GlideRecord('live_profile');
gr1.addQuery('name', myUserObject.getDisplayName());
gr1.query();

gs.print(gr1.getRowCount());

if (gr1.next())
var currentUser = gr1.sys_id;

var gr = new GlideRecord('live_message');
gr.addQuery('profile', currentUser);
gr.query();

gs.print(gr.getRowCount());