Why report does not make "List" type fields available to select for the report?

[Todd O]

I am building a report from my custom (extension to cmdb_ci) table. On my entry form, I added a new field that is a "list" field which references my customer (extension to change_request) table.   That is, I can select multiple CI records to be associated with my Change Request. Everything works great and I'm able to associate multiple CI's to my one CR. The problem is that I'd like to have a report of my CI's and then list all of my associated CR's.   When I added the "list" column onto my CR entry form, it automatically created a new column of type "List" that references my CI table.   But, when building my report of CI's, the available column list does NOT show my this new field for associated CR's.   Is this a limitation of reports to NOT show list fields to the report?

Thanks.

Todd

p.s., just to clarify, this field is of type "List" which is a glide_list field.   It's supposed to hold a list of references to the other table. Which is what I want to show on the report. Also, when I use SOAP UI and try to pull this field back, SN will not return this at all. How do I access a list field on a table (whether report or REST API or any other way)?

[brianquinn · Accepted solution]

Todd,

If you were able to report on a list field, you would not always get consistent results.   List fields are stored in the database as comma separated values, so order would matter.   In your example, if you added "Computer A" and then "Computer B" on one CR, and on a different CR you added "Computer B" and then "Computer A", reporting would treat them as completely different since the stored database values are different.

It sounds like a "Many to Many" list would be a better solution for you.   This would allow you to relate CR's to CI's and then you can use database views to join the CR, CI, and M2M tables together and report on that database view.   This will probably give you the most flexibility.

I also just tested accessing the watch list field and I was able to pull it use SOAP and the REST API.   If this is not working for you can you provide a little more detail on exactly how you are testing this?

Thanks

Brian

[Todd O]

Brian, Thanks for clarifying, that helped and makes sense. So, here was the solution that I'm going with.   Keep in mind that I would like to stick to out of the box functionality as much as possible.   So, there is an existing table called task_ci which seems to have the purpose of holding a relationship between task (parent of CR) and CI data (parent of my extension table for my CI items).   Also, there is a related list available during CR entry called CIs Affected that populates this table.  

From my initial testing, using the task_ci table seems to be a good approach for the m2m relationship I'm looking for. In the report, it also gave me access to traverse down into each table (CR and CI) to pull the fields I want.   I'll keep heading down this path unless I hear that I shouldn't.

Lastly, I see this table (task_ci) holds a few columns that I may not care about (e.g., applied (boolean), applied_date, xml (not sure what this holds).   So, I'm a little concerned about using this table for the wrong purpose. Any insight on usage of this table for my requirements?

Thanks.

Todd

p.s., Brian you have the same name as a character on one of my favorite shows. It's call impractical jokers. Brian and the gang are hilarious!

[A Westervelt]

task_ci is designed for the M2M relationship between all tasks and configuration items. Reading from your posts, this is the exact table that you should be pulling reports on. However, since this is a join of the cmdb_ci and task you may have to find creative means to collect values specific to the change_request table.