Database View Assistance - Report on Record Producer variables

Carl Fransen1 · ‎06-24-2018

Hi Team,

We have a bunch of HR Services created a Record Producers in out hrportal. These capture variables when submitted in the portal and I can see these on the HR Cases - these variables are a combination of string fields and 'reference' which link back to the user table - not all the variables are mapped back to an HR table field - some are just variables.

I need to report on the variables in a Record Producer - my understanding is all the variables are stored in the 'question_answer' table. So I've created a database view which links the 'question_answer' and the 'sn_hr_core_case' tables:

This works great, but I also now need to report on some of the 'values' being gathered where these are linking to the sys_user table - see below where most fields are OK, except the 'value' field which still shows the sys_id:

Not all the 'value' entries are users, some are pure text which I need to report on and others are linking to another user - so I need to figure out how to link this to the sys_user table and find the sys_id's where they exist.

Everytime I add the sys_user table and link via 'q&a table.value' to 'sys_user.sys_id' it ONLY shows the records where a match is found - so I don't get ALL the records and the sys_is isn't translated.

Can anyone help with this?

Thanks

Carl.

Jaspal Singh · ‎06-25-2018

Hi Carl,

For your comment

Everytime I add the sys_user table and link via 'q&a table.value' to 'sys_user.sys_id' it ONLY shows the records where a match is found - so I don't get ALL the records and the sys_is isn't translated.

All you need is to use the Left join option available in Database view & make sure q&a table.value is to the left in Where clause. Just add it by configuring the list layout for database

Thanks,

Jaspal Singh

Hit Helpful or Correct on the impact of response.

Carl Fransen1 · ‎06-25-2018

Hi Jaspal,

Thanks for your reply - I made the change and although I can see the data, the result still shows sys_id's instead of names in the 'value' field. The 'value' field is a large string, 4000 chars, so not sure if this is the issue, see updated database view below:

I hope you can help with this?