Hello everyone. I have a use case for some information about my Data Sources. I have a decent amount of forms in place. I'm making some changes, and I can see on the Forms administration page which processes use a data source. I can also see which tables are being used for offline access. What I wish I had is a list of the tables used in the forms process specifically, and on the other side, which forms are using the offline tables.
On the data sources menu, I select my production DB, and I see a list of forms that use this source. I would love to be able to click the process and see the list of tables being used. I can find that information by opening the process and going to the lookup rules, but having that data more easily available would be nice since some of my forms have a lot of rules.
If I click the Offline Lookup Tables, I see a list of the tables that are available offline. Here, I wish I could click the table and see the processes that are using these offline tables.
Our main business system, an IBM i Power system, has tools that we use to find all programs that use a DB file and which files are being used by programs. I would love to see something similar to this in Forms.
I'm sure there might be a query I could do in the Forms DB to find this information, so if someone could help me with that, it would be greatly appreciated.
Discussion
Discussion
Lookup tables in Forms, where used?
posted one day ago
0
0
replied one day ago
You can use this script in SQL to see your tables and form connections:
SELECT TOP (100) PERCENT ced.table_name, cesp.stored_procedure_name, cfm.form_id, lr.lookup_rule_id, Lookups.database_display_name AS Data_Source, dbo.cf_business_processes.name AS Process_Name,
dbo.cf_forms.name AS Form_Name
FROM dbo.cf_lookup_rules AS lr INNER JOIN
dbo.cf_field_column_mapping AS cfm ON cfm.mapping_id =
(SELECT TOP (1) mapping_id
FROM dbo.cf_field_column_mapping
WHERE (lookup_rule_id = lr.lookup_rule_id)
ORDER BY mapping_id) LEFT OUTER JOIN
dbo.cf_external_dbtables AS ced ON lr.external_datasource_id = ced.datasource_id LEFT OUTER JOIN
dbo.cf_external_stored_procedures AS cesp ON lr.external_datasource_id = cesp.datasource_id INNER JOIN
(SELECT DISTINCT
dbo.cf_lookup_rules.lookup_rule_id, dbo.cf_lookup_rules.external_datasource_id, dbo.cf_field_column_mapping.form_id, dbo.cf_form_process_mapping.process_id, dbo.cf_external_databases.database_display_name
FROM dbo.cf_lookup_rules LEFT OUTER JOIN
dbo.cf_field_column_mapping ON dbo.cf_lookup_rules.lookup_rule_id = dbo.cf_field_column_mapping.lookup_rule_id INNER JOIN
dbo.cf_form_process_mapping ON dbo.cf_field_column_mapping.form_id = dbo.cf_form_process_mapping.form_id INNER JOIN
dbo.cf_external_dbtables ON dbo.cf_lookup_rules.external_datasource_id = dbo.cf_external_dbtables.datasource_id INNER JOIN
dbo.cf_external_databases ON dbo.cf_external_dbtables.database_id = dbo.cf_external_databases.database_id
WHERE (dbo.cf_lookup_rules.is_disabled <> '1') AND (dbo.cf_lookup_rules.external_datasource_id IS NOT NULL)) AS Lookups ON lr.lookup_rule_id = Lookups.lookup_rule_id INNER JOIN
dbo.cf_forms ON Lookups.form_id = dbo.cf_forms.form_id INNER JOIN
dbo.cf_bp_processes ON Lookups.process_id = dbo.cf_bp_processes.process_id INNER JOIN
dbo.cf_business_processes ON dbo.cf_bp_processes.bp_id = dbo.cf_business_processes.bp_id
WHERE (dbo.cf_business_processes.is_deleted <> '1') AND (dbo.cf_business_processes.is_archived <> '1')
ORDER BY cfm.form_id
3
0
replied one day ago
Thank you@████████. This is what I was looking for. I assume you must use the statement in your organization. I would love to see this kind of information in the Forms application. Hopefully someone from Laserfiche will see this and provide some feedback too.
0
0
replied one day ago
Yes, I use it for a few things but mainly cleaning up and making sure I don't edit or remove something that is being used in a forgotten form. I agree, it would be nice to have some basic viewing within forms.
You could create yourself a dashboard in a form and use that, but I don't suggest doing a directly query lookup against the Forms DB. We move a lot of info like to local tables specifically to create dashboards that do not have to be real time.
0
0
replied one day ago
That is a great idea. My company was a slow adopter of Forms, and it's picking up now. I think I will create a dashboard that could use this, and I agree with you about the local table idea.
Thanks for all the great info.
0
0
replied one day ago
I have some similar code. The one I use also pulls views and stored procedures.
SELECT DISTINCT f.form_id ,f.[name] AS form_name ,eds.datasource_id ,COALESCE(edbdt.sql_server,edbspr.sql_server) AS sql_server ,COALESCE(edbdt.sql_database,edbspr.sql_database) AS sql_database ,CASE WHEN COALESCE(eds.datasource_id,'') <> '' THEN CASE WHEN COALESCE(edt.table_name,'') <> '' THEN 'Table or View' WHEN COALESCE(espr.stored_procedure_name,'') <> '' THEN 'Stored Procedure' ELSE '' END END AS [object_type] ,COALESCE(edt.table_name,espr.stored_procedure_name) AS [object_name] FROM cf_forms AS f JOIN cf_fields AS fld ON f.form_id = fld.form_id LEFT JOIN cf_field_column_mapping AS fldcm ON f.form_id = fldcm.form_id AND fld.field_id = fldcm.field_id LEFT JOIN cf_lookup_rules AS lur ON fldcm.lookup_rule_id = lur.lookup_rule_id LEFT JOIN cf_external_datasources AS eds ON lur.external_datasource_id = eds.datasource_id LEFT JOIN cf_external_dbtables AS edt ON eds.datasource_id = edt.datasource_id --AND eds.datasource_type = 1 --tables and views LEFT JOIN cf_external_databases AS edbdt ON edt.database_id = edbdt.database_id LEFT JOIN cf_external_stored_procedures AS espr ON eds.datasource_id = espr.datasource_id --AND eds.datasource_type = 1 --tables and views LEFT JOIN cf_external_databases AS edbspr ON edbspr.database_id = espr.database_id WHERE COALESCE(eds.datasource_id,'') <> ''
1
0
You are not allowed to follow up in this post.