Database view not working for Dictionary table

[SamuelTse]

Hi,

I am trying to create a database view to join the sys_audit and sys_dictionary table because i needed to display the field label from the sys_audit table. Currently it is only displaying the field name. However, I run into a pretty simple problem and not sure what went wrong.

I created a very simple database view as follow, under the "View Tables" section:

I only have one table for now but when I run this database, it should only return all the fields created for Incident table. However, I get everything in the dictionary table back.

What am I missing?

Thanks,

Sam

[Kristoffer Mon1]

May I know the requirement you are ultimately trying to achieve?

[SamuelTse]

Currently we are exporting a small subset of records for a particular table out of the sys_audit table. Basically we want to let the user know what fields have been change in the last x days. Right now, the report is showing the field name, which is not user friendly. We wanted to show the field label of the fields. Since the "field name" field in the sys_audit table is not a reference field. The best way I can think of is to create a Database View to join the sys_audit and sys_dictionary table together by using the "field name" in the sys_audit field and the "column name" field on the sys_dictionary table. That is pretty much what I am trying to accomplish.

When I create the Database View, I wanted to limit the number of records to return from the Dictionary table so I need to set the condition in the "Where Clause", which is giving me problem because something like prefix_name="incident" doesn't work (see my original post)

I hope this is clear. 

Sam

[Manish Vinayak1]

Hi Samuel,

Why don't you try to join Field Label (sys_documentation) table? Following is an example from my personal dev instance where incident table, Field Label table (sys_documentation) and Audit (sys_audit) table are joined:

The last query not visible in the screenshot is:

label.name='incident' && label.element=audit.fieldname

Here are the results:

I believe that's what you are trying to achieve.

Hope this helps!

Cheers,

Manish

[SamuelTse]

Hi Manish,

Thanks for the solution. Yours definitely works. I will mark your reply as the correct answer. However. here is the interesting thing. I tried this on my instance, it doesn't work. but when I do it in my personal dev instance, it works. 

Now I really wonder why. It seems like I can only get 799 records from the sys_audit table when I set it as a sole table in my Database view(nothing in the where clause) for whatever reason. That's the problem. I suspect my original Database View works too, it is just that the audit table is not returning enough audit records. 

Any thoughts on this? I created a ticket with HI. I can't seem to think of any setting that can limit the number of records returns for the sys_audit table.

Thanks,

Sam