Database Inner Joins and Riada Insight (help for the community)

Steve Letch May 24, 2021



Thought I'd give back a little. I recently figured out DB inner joins and used this info to pipe Service Management DB info about portal forms, Id's, the fields on the forms, the groups etc etc into Insight to make life a lot easier in seeing what fields are where. I also pipe this info into Insight fields so users can easily select which forms they need tweaking and what fields they'd like added.


Here's my DB query

public."AO_54307E_VIEWPORTFIELD"."FORM_ID" as "Form ID",
public."AO_54307E_VIEWPORTFORM"."NAME" as "Form Name",
public."AO_54307E_GROUP"."GROUP_NAME" as "Group Name",
public."AO_54307E_VIEWPORTFIELD"."LABEL" as "Field Name",
public."AO_54307E_VIEWPORTFIELD"."FIELD_ID" as "Field ID",
public."AO_54307E_VIEWPORTFORM"."ISSUE_TYPE_ID" as "Issue Type ID"
inner join public."AO_54307E_VIEWPORTFORM"
on public."AO_54307E_VIEWPORTFORM"."ID" = public."AO_54307E_VIEWPORTFIELD"."FORM_ID"
inner join public."AO_54307E_GROUPTOREQUESTTYPE"
inner join public."AO_54307E_GROUP"
on public."AO_54307E_GROUPTOREQUESTTYPE"."GROUP_ID" = public."AO_54307E_GROUP"."ID";


Here's what my Import module looks like

import module.PNG

The Regex I use on the Field_ID part of the DB to match it up with the ID attribute of my Custom Field object Type


What it's doing is only starting from after the underscore. See the ViewPortFields table stores the custom fields as 'customfield_xxxxx', where as the ID in the customfields table is just the ID itself.


Anyway, here's the end result 😊

insight schema.PNG



Log in or Sign up to comment
AUG Leaders

Atlassian Community Events