Database View - "invalid field" error with accurate field name

[scwillson]

Hello, I am attempting to construct a database view in order to be able to report on variables from Record Producers (variables that don't necessarily cascade to the respective table).

I build a Database view containing Incident and Question Answer (question_answer is the table that contains all the Question and Answers from record producers). There is a field on question_answer that contains the sys_id of the record generated by the record producer, that it is related to; this field is called "table_sys_id".

I gave the Incident table a prefix of "inc" and the Question Answer table a prefix of "qa". The Where Clause fields were filled out as follows:

• [question_answer] clause: qa_table_name = 'incident'
• [incident] clause: inc_sys_id = qa_table_sys_id

For   both of these, I get the ERROR saying that the field name is an invalid field or not visible. This is for "qa_table_name" and "qa_table_sys_id"

I am unsure how to get this working. I am 100% sure that the field names are correct, so I'm assuming that the problem relates to them not being "visible". Any suggestions?

Here are my screenshot:

NOTE: only 1 of the 2 errors show at a time, it just depends which view table has a lower order. This screenshot is when "qa_table_name" is first.

[Jaspal Singh]

Hi Simon,

Try keeping only below where clause

inc_number=qa_table_sys_id

As for the incident records that you want can be filtered when a report will be created on the database view.

[scwillson]

That doesn't change anything, I still get the error about the "qa_table_sys_id" being the issue.

"Where clause in view u_record_producer_view has an invalid field or a field that is not visible (qa_table_sys_id)"

[scwillson]

Is there a particular ACL I need to check for, on the Question Answer table? or field?

[Jaspal Singh]

I tested the same & it worked.

Kindly find it as below

Only thing is you need to replace hr_case table with incident table & rest remains the same.