000029442 - Query to retrieve information about all sub forms in Archer

--For the SubForm fields used in various applications
select FD .field_id, FT. field_name, M.module_name as ApplicationName,M .module_id,   FT.field_desc , FD .Alias, ASO. aso_status_name,
FDS.search_default_field , FDS .required, FD. field_help_options
from tblIVFieldDef FD
join tblLevel L on FD .level_id = L .level_id
join tblModule M ON M .module_id = L .module_id
join tblFieldTranslation FT ON FT .field_id = FD .field_id
join tblIVFieldDefSubform FDS ON FDS .field_id = FD .field_id
join tblASOStatus ASO ON ASO .aso_status = FD .aso_status
where FD .field_type_id = 24
--For the actual SubForms
select M .module_name as SubFormName , FD .field_name as FieldsOnSubForm , M .module_desc as SubFormDesc,
M.Alias as SubFormalias
from tblModule M
join tblLevel L ON M .module_id = L .module_id
join tblIVFieldDef FD ON FD .level_id = L .level_id
where M .module_type_id = 5