Creating a Database View to Get Variables from Requested Item

[Chris315]

Hi,

I'm having difficulty creating a database view to join five tables together for data retrieval with the REST API.

I want to be able to retrieve the variables names and values from a request ticket. From what I can tell, the tables for this are as follows:

sc_request: Request ticket
sc_req_item: Request item inside ticket
sc_item_option_mtom: The variable ownership table
sc_item_option: Stores the variable values
item_option_new: Stores the variable names

Here's what I've come up with so far, but this results in lots of duplicated data, and a massive JSON:

I'd just like to be able to retrieve the request ticket number and who created it, followed by the variables in the associated item. In this case, it's a request to create a new user, so the variables are things like "firstName", "lastName", and such.

Thanks in advance.

[Chris315]

So, it looks like my issue probably has something to do with the join type. Essentially, I want to perform an inner join between each of these tables. Is there any way to do this? At the moment, I'm using REST to grab data from each table in question, and I'm joining them manually through code. This uses a lot of calls and it's pretty slow.

[kevinray]

Yes, you can do that, but keep in mind, you will get a lot more record returns, because it's going to return a row for every entry in your MRVS for every variable on your form.

Add the following 3 entries in to your Database view (Note you may have to add the "left join" field to your form view

Then use the "Choose Columns" on your report to select. If you use the same naming convention i've used, then:

Question(sio_item_option_new) this will be the variable question/label

Value(sio_value) this is the variable answer

[Priya Singh 2 2]

I am unable to view above picture, could anyone help as I have similar requirement.

[Prapurna kondur]

can you please post the picture again?