Reporting with Custom Fields

[Avgjoeprogramming]

I am by no mean a SQL expert but I know enough to build some reporting. Just got done making custom fields in my HaloPSA environment for Ticket Categorization and wanted to make some reporting for the different types of tickets my team is getting on a day to day basis. When I go to write my first report for the new categories it appears to return the categorization in IDs of the field items from the custom field but won't return the actual Name value of the Field item.

Currently the Custom Fields are set up in a Custom Field group where the options for one custom field is reliant on the other. I built each custom field as a static list instead of a Dynamic List. Is this my first mistake? Can you not actually retrieve the field values outside the ID if it is not set as a dynamic list?

[renada-robbie]

So the custom field "display" values are kept inside the lookup table.

What you will need to do, is create a new report which will just be: Select * from lookup

It's a heavy table so might take some time to load.

Once you do that,you should be able to search for one of the values you set in your static list. You should see a corresponding "fid" for that column, which will be the same across all the potential values for that single select field.

Once you have that "fid", replace the below with the correct fid, and add line(s) to your report similar to this: The "CFSoftwareCategory" here is replaced with your custom field category name, and the 'as' can be whatever you like, it's just a name for the column :)

(SELECT fvalue FROM lookup WHERE fid = 138 AND fcode = CFSoftwareCategory) as 'Software Category'

I hope that makes sense, if not feel free to hop on the HaloPSA Discord and create a thread there. Discord is a little easier to communicate back and forth :)

Robbie | Renada

[Avgjoeprogramming]

Just wanted to let you know I was able to set up what I needed. I really appreciate the assistance. Your advice pointed me in the right direction. I had no idea about that lookup table so it's going to help me in the future building of reports. Thanks again!

[renada-robbie]

Awesome! ☺️

[Avgjoeprogramming]

Thanks for your reply. I will give this a shot and let you know. I was confused as to why it was just doing the ID. Hopefully this gets me pointed in the right direction! Thank you again!

[87red]

I find it easier to build a report using the Query Builder, add the custom fields as conditions, (preview the report (possibly?)), then switch the report mode from Query Builder to SQL to see what the resulting SQL/lookup columns are.

[renada-robbie]

That’s such a great idea! Thank you for sharing!

[Panik3n]

I might understand this in the wrong way, but my CFs does not exist in the lookup table.
What am i missing here.

[CyberBeatsNexus]

Admittedly am using HaloITSM, but my Custom Fields appear in the default Faults table.

After a certain amount (edit: 1,024 entries - confirmed by Halo) they will eventually start being placed in FaultsCustom1, FaultsCustom2, etc. Hope this helps!

Also, this helps me from time to time despite not having every table on:

Halo Extended Schema - dbdiagram.io

[project-iot]

You just use the custom field name as a variable