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

Steve Letch May 24, 2021

Hi

 

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

select
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"
FROM
public."AO_54307E_VIEWPORTFIELD"
inner join public."AO_54307E_VIEWPORTFORM"
on public."AO_54307E_VIEWPORTFORM"."ID" = public."AO_54307E_VIEWPORTFIELD"."FORM_ID"
inner join public."AO_54307E_GROUPTOREQUESTTYPE"
on public."AO_54307E_GROUPTOREQUESTTYPE"."REQUEST_TYPE_ID" = public."AO_54307E_VIEWPORTFORM"."ID"
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

(?<=\_)([\w]{1,})

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

0 comments

Comment

Log in or Sign up to comment
TAGS
AUG Leaders

Atlassian Community Events