Problem with dot-walking and extended table

[benedwards]

I’m trying to dot-walk from the Project Change Request table (project_change_request) to fields on the Project Task table (project_task). If I use parent.top_portfolio.{any column} nothing works, I presume because ServiceNow is expecting project_change_request’s parent column to refer to a task record (which does not have a top_portfolio column), instead of a pm_project_task record which is what

Project_change_request has a “parent” field that is a reference to Task. The way that project_change_request records are used, the parent column actually refers to a Project Task.

I have run into an issue using dot-walking an extended table in a before query business rule. I’m using addQuery() in custom script to add conditions to the query. When I dot walk through a particular extended table, it’s not working.

Here’s an example of what I’m trying to do:

• Let’s say table A has column X.
• Table B, has column Y, and B extends A.
• Table C, has column Z, which is defined as reference to A.

So say we know that C record’s field Z always contain a reference to a B record. Can I select the B record’s field Y by dot-walk from the C record? Doing z.y does not seem to work (gets nothing).

It’s not that surprising, I’m just wondering there’s a workaround? Some way to “cast” the Z field to be a reference to B?

[dvp]

did you try with ref_FIELD_NAME?

[benedwards · Accepted solution]

Thanks for the hint! What I did to get it to work was use ref_TABLE_NAME. The "ref_TABLE_NAME" tells the dot walk to think the next field (in the dot walk) comes from extended table TABLE_NAME. That's pretty cool.

So in my example, the correct dot walk would be z.ref_b.y

[midjoule]

Hello,

For those coming to this thread, I share here how I managed to do this after having struggled a bit.

This is the working version of the piece of code using a filter to a child table access through a reference table attribute:

The tricky part was in line 43.
I wanted to get the list of IP Addresses in use for the Configuration Item that are not retired nor stolen. In other words, any IP Address in use. However, the Hardware Status attribute is not part of the cmdb_ci class, but it's only added to the cmdb_ci_hardware class (child of cmdb_ci).

As mentionned earlier by @benedwards, the trick is to access the child table by adding the "_ref" prefix. However, I struggled anyway to find the right filter in my case.

So I finally found it by going to the cmdb_ci_ip_address table and adding a filter and browing through the attributes to find the path to the field I wanted to filter on (it's quite convenient):

After running it, I could see the query in the browser address or could also copy it by right-clicking the filter link and selecting the copy query menu:

Hope this will help someone 🙂