Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

Where are Custom Fields in Database


#1

I have created a Case custom field that is a multi-select. I would like to be able to run a SQL query including this field but I cannot find where the field or options are stored in the database.

It appears that other custom fields end up in dbo.cases with a prefix of custom_ but not this multi-select.

Where is a multi-select custom field saved in the database?


#2

I just added a drop down to test your question in my companies test environment with TR 4.2 and the field is added to the Fields table:
name system_name entity_id label
multiselect custom_multiselect 1 Multi-Select

Once added to the Fields table and you go into a case and add a value to the multi-select - a record in the Case_Assoc table will be created with the Case ID and a record for each selection.


#3

Yep, @BGanger is correct, this field is managed separately via the case_assocs table, as it wouldn’t be a good idea to use a single field to manage the n to m mapping as this would be difficult to do in a fast and scalable way.


#4

Ok thank you! I will have a look at see if I can make that work in my SQL query.


#5

Is there any chance that you have a SQL example available that will return a case and the saved multi-select custom field values for that case? I can see how the cases, case_assocs, and fields table are all related. It would save me some time if you already had an example query so that I don’t have to figure out how to get the value out the configs column. Any help would be much appreciated!!


#6

The Config column is not real helpful - imho:

[{“context”:{“is_global”:false,“project_ids”:[10]},“options”:{“is_required”:false,“items”:“1, 1\n2, 2\n3, 3”},“id”:“745ee189-f057-40e2-bf80-e5b6d72be27a”}]

What part would you want out of the the above example? It will be difficult to get (though I don’t consider myself an expert in SQL) and require a bit of SQL gymnastics.

This SQL would return what case 46170 (in my system) has for the multi-select:
select co.value, c.id, co.name, * from cases c
join case_assocs co on c.id = co.case_id
where c.id = 46170

This returns value 1 and 3 which are the 2 selections in the case.


#7

BGanger - Thank you for the reply. I would like to be able to report on the text that the user sees on the screen. For example, we have a select list that has these options: 1, Authorizations 2, Assignments, etc. I would like a query that would report back “Assignments” when the value = 2.


#8

{“is_required”:false,“items”:“1, Test\n2, Test1\n3, Test2”} (I changed my example to make it easier)

You then want to strip the bold area and match to the value in the case_assocs table. This gets more difficult - I stripped out characters in the Sessions table for a query I did awhile back and I used 2 temp tables created in ram to manipulate the data.

I can see having one table with the following records:
‘1, Test’
‘2, Test1’
'3, Test2’
and doing a like between the case_Assoc.Value table and this but that will be slow if there are a lot. Not sure how to get one query that will clear the unwanted data when there are different numbers of values.

Quicker SQL would have one table with 2 fields - one for the number and one for what the string is (Test) and match in the number and value fields. Not real sure how to do that but it certainly should be possible.

Hopefully Gurock will have some suggestions… :slight_smile:


#9

Hi all!

Directly extracting the value labels via SQL is not really possible/easy as they are not stored in a structured table format but as JSON instead (for various reasons). I would recommend reading the values and translating them to the actual strings (as displayed in TestRail) as a two-step process. The first step can be implemented via raw SQL and requires a simple SELECT on the cases and case_assocs table. The second step would involve a small script that reads the custom field meta data, converts the JSON and maps the values (IDs) to the actual value labels.

Instead of accessing the database directly, you can also use the API and this would be the recommended approach. This would actually even be a bit easier as TestRail would handle all the cases/case_assocs logic for you in this case.

I hope this helps!

Regards,
Tobias