We want to report off the Web Help Desk SQL database. Is there a published guide for the tables?
-
Bump....
I'm trying to update asset custom fields via PostGreSQL, rather than having to manually use the web GUI for 450 assets.
If anyone can shed a light on the relationships between asset, asset_custom_field and custom_field_definition, I would find it very helpful.
My example: we have created a field called "Role" which details if the device is Core, Production, Stage, etc.
I can see the relationship between asset_custom_field and custom_field_definition, see the SQL code below:
SELECT fd.label, cf.definition_id, cf.entity_id , cf.id, cf.string_value
FROM asset_custom_field cf
INNER JOIN custom_field_definition fd
ON cf.definition_id = fd.id
WHERE definition_id = 62
AND cf.string_value IS NOT NULL
Returns:
label definition_id entity_id id string_value Role 62 5 4251 Core Role 62 18 4265 Core Role 62 10 4288 Core Role 62 38 4295 Core Role 62 265 4366 Production Role 62 269 4379 Production Role 62 270 4381 Production Role 62 271 4384 Production Role 62 272 4388 Production Role 62 273 4390 Production Role 62 274 4395 Production Role 62 275 4396 Production Role 62 277 4408 Production But how do I link this back to the Asset table, to show the "Role" assigned to each asset?
As neither entity_id or id match the asset.asset_id.
Once I've learnt that, mass updating the "Role" field should be easy....
Thanks
