query

[George_Tarasi]

Is it possible to get  a list of fields on a unity form and the keywords they point to without going through the entire form one field at time?

[Adam_Levine · Accepted solution]

Hey George - not sure if this is what you're looking for or not but hsi.uffield might have that information.

The fieldsourceparam column holds the keyword ID for fields that map to keywords, so you could join hsi.keytypetable on that and keytypenum to retrieve more data about each keyword.  The ufformnum column in hsi.uffield corresponds with the unity form ID and formrevnum is the revision number, and fieldsourceparam is a blank string for other field types, so if you were looking for all keywords on a certain version of a certain form, you should be able to return that with something like the following:

select  f.ufformnum  ,f.formrevnum  ,f.fieldsourceparam  ,k.keytypefrom hsi.uffield f  left join hsi.keytypetable k on f.fieldsourceparam = k.keytypenumwhere  f.fieldsourceparam <> ''  and f.ufformnum = [YOUR_FORM_ID_HERE]  and f.formrevnum = [YOUR_FORM_VERSION_HERE]

... which would return something like the following example (taken from an employee information form):

Depending on the use case, you could easily write this as a reporting dashboards data source and allow the user to choose the form ID & version as parameters at runtime, create a view and use it as a WorkView external class source, etc.

Hope this helped in some way!

[Larissa_Armand]

We've done what Adam is describing with a Reporting Dashboard. 

Data Provider SQL: 

select rtrim(F.FORMNAME) as Form_Name, F.UFFORMNUM as Form_Num,
case when F.DSTYPE = '0' then 'Document'
when F.DSTYPE = '2' then 'Workflow'
end as Form_Type,
case when G.FIELDTYPE = '0' then rtrim(K.KEYTYPE)
when G.FIELDTYPE = '1' then rtrim(S.KEYSETNAME)
when G.FIELDTYPE = '3' then rtrim(K.KEYTYPE)
end as Keyword_Type_or_Group,
case when G.FIELDTYPE = '0' then K.KEYTYPENUM
when G.FIELDTYPE = '1' then S.KEYSETTABLENUM
when G.FIELDTYPE = '3' then K.KEYTYPENUM
end as Keyword_KeywordGroup_Num,
rtrim(G.UFFIELDKEY) as Field_ID
from HSI.UFFORM F
left outer join HSI.UFFIELD G on F.UFFORMNUM = G.UFFORMNUM and F.MAXREVNUM = G.FORMREVNUM
left outer join HSI.KEYTYPETABLE K on G.FIELDSOURCEPARAM = K.KEYTYPENUM
left outer join HSI.KEYWORDSET S on G.FIELDSOURCEPARAM = S.KEYSETTABLENUM
where G.FIELDSOURCE = '1'
and F.FORMSTATUS = '0'
and (F.UFFORMNUM = ##OB_OPTIONSELECT_ALL{(@{pFormNum}, -1)})
order by 1, 3
;

SQL for parameter select list: 

select rtrim(FORMNAME) as FormName, UFFORMNUM
from HSI.UFFORM
where FORMSTATUS = '0'
order by UPPER(rtrim(FORMNAME))
;

This prompts the user for a form name when they open the dashboard, then the details for that form are shown.