I want to query two different tables using AND condition in a single transform map script.

[r_21]

Hi All,

I have a condition ,in that i have to check for a user in sys_user table and as well as a project in pm_project table.

This is an AND condition.

If both (valid user and valid project) are there in their corresponding tables ,only then a valid record will be inserted to the 3rd table from staging table.

If any of them is invalid or empty.Then that row of record from staging table should be ignored.

I have written many scripts but i am stuck in implementing all the requirements.

Thanks and regards

Rajesh Choudhary

[michal29]

Hello Rajesh,

I would probably nest an if loop in an if loop, meaning:

var gu = new GlideRecord('sys_user');

gu.addQuery('USER_FIELD', 'YOUR USER QUERY');

gu.query();

if (gu.next()) {

      var gp = new GlideRecord('pm_project');

      gp.addQuery('PROJECT FIELD', 'YOUR PROJECT QUERY');

      gp.query();

      if (gp.next()) {

              // Your insert table here smth like

              var gst = new GlideRecord('staging table');

              gst.initialize();

              ...

              ..

              .

              gst.insert();

      }

}

But i realize its maybe not the most efficient way to achieve what you want.
PS. You can also make you of get function like gu.get ('USER_FIELD', 'YOUR USER QUERY'); which returns a single record instead of querry.

Regards,

Michal

[r_21]

Hi Michal,

I have tried your code but validations are not working neither for user nor for project............invalid records are even taken and inserted into the 3rd table.

[michal29]

Hello Rajesh,

From here, the best way to go is debug.

I mainly put a few console.log or alert() here and there so lets say, put it

console.log(gu.sys_id);     <- compare with what you recieved

console.log(gp.sys_id);     <- compare with what you recieved

you can also try to put wanted sys_id instead of Query, to check if the table gets searched right.

Alternatively you can go to background script and test anything there, like get all the records and see if there is what You want like:

var gu = new GlideRecord('sys_user');

gu.addQuery('USER_FIELD', 'YOUR USER QUERY');

gu.query();

while (gu.next()) {

gs.print(gu.sys_id);

}

[Chris M3]

This third table you are inserting into, does it have a reference to both the user and the project records, and thus getting populated in the transform, and possibly even the coalesce fields?

Trying to picture exactly what you are wanting to do, but let me give you an example of what I think is a similiar situation.

We have a transform into a m2m type table linking users to CI's.   The transform map coalesces on two fields, the user and CI reference fields.   On both of these columns in the transform, the choice action is set to 'Reject'.   What this does is prevent us from creating new users/CI's, and the record is skipped with an error (No record in the m2m table)