Hello, My use case is that I'm trying to create a view to build a pivote report that will show how many incidents and problem tasks are related to a given problem. However, when I try to only pull in incident records that have problem_id populated I get the following type of error:
In my test example I am only joining Task and Incident:
Task table prefix is tsk and Incident table prefix is itsk. My Where clause for the incident table looks like this:
tsk.sys_id = itsk.sys_id
&& itsk.problem_id IS NOT NULL
The goal to have the view only show incident records where problem_id isn't empty
You don't need to join task and incident, the platform knows to do that for you.
You will want to join incident to problem with a normal join (INNER JOIN) and that will give you only Incidents that have Problems attached. Next, to get the related Problem task information do a LEFT JOIN on problem task.problem = problem.sys_id. Here's a screenshot.
You don't need to join task and incident, the platform knows to do that for you.
You will want to join incident to problem with a normal join (INNER JOIN) and that will give you only Incidents that have Problems attached. Next, to get the related Problem task information do a LEFT JOIN on problem task.problem = problem.sys_id. Here's a screenshot.
This is super close but maybe my logic is off... Changing the order definitely cleared the error though I don't understand how or why yet. It seems I may have under/mis-stated my goal which is to be able to use a the Pivot report type to have a row of PRB and two colums where one column is the count of Incidents related to the PRB record (if any, there could be PRB with no incidents from a proactive perspective) and the second column would be the count of Problem Tasks related to the PRB record.
If I understand the ordering correctly the view takes all the INC records, then narrows that down to only incident records with PRB records, and then left joins in PTASK records (if any) that are related to the PRB records.
My thought process was that I would want all the PRB records then to left join in any INC and PTASK records where applicable. Starting with the Task table so that I could use task.sys_class_name ... though it occurs to me now that won't really work because regardless of which is first (INC or PRB) that will be the value of task.sys_class_name the way I was approaching it... so I have to rethink that.
So I ended up dropping the prefix.element for prefix_element as your example showed which seemed to clear things up quickly. I wonder if the . separator for the prefix got dropped at some point between fuji and geneva and I just missed it. Anyway, so with that I was able to get a list view that seems to provide the right information:
However, the Reporting piece is still wonky (note PRB0040081 in the example above):
Based on the data I would have expected to see something like:
INC | PTASK | COUNT
PRB0040081 | 6 | 2 | 8
The reporting example shows why I thought I might be able to get away with bringing in task as a table join as well to have a single element for the columns by using task.sys_class_name but as I explained before that just wouldn't work.
So all of that said, your examples did help solve my problem of the error I was seeing and at the moment I can seem to only attribute it to the difference between using prefix.element instead of prefix_element. Thank you for that. Now to figure out how to get the pivot to do what I want.
Also, remember if you are using View Fields, you absolutely must include every field that will be used in a JOIN, including sys_id
