Question

Forms Data Source - Linked Processes

Forms Business Processes Feature How To

Updated August 3, 2020

asked on July 31, 2020 • Show version history

Is there a way to determine which processes are using a particular data source in Forms (without going process-by-process through the Lookup Rules)?

I'm aware of the processes shown within the data source configuration on the administration page, but this only outlines which processes have permission to use that data source, not necessarily which processes are actually using it:

Background:

I have a client that will be making changes to a Forms data source, including changing the table names and data arrangement within the tables.

(I'm aware that when simply moving a data source, you can update the data source in Forms to point to the new location, which will update all of the Lookup Rules in the processes)

However, since they are changing the overall arrangement of data, our approach it so make a second copy of the database, then have them make the changes to the second copy. Once the new data source is ready, they will go through the processes and change the Lookup Rules to reference the data in the new database, as it is arranged there.

However, this client has 200+ processes, and at some point within the data source configuration, they used the "Select All" feature and added ALL of the processes to that data source's permission list; so every process is shown under that data source. We know that, even though all of the processes have permission to access that data source, not all of the processes are actually using that data source.

I want to find out if there is a way to determine which processes are actually referencing that data source within their Lookup Rules (similar to the "View Linked Workflows" option in Workflow).

I know there isn't a way to do this natively within the Forms application, but perhaps a query in the Forms database that can find this information?

I'm just trying to save from needing to go through 200+ processes, one-by-one, and checking the Lookup Rules.

Any assistance is appreciated!

P.S.- If there isn't a way to do this, perhaps we can request this added feature in future release versions of Forms.

0 0

Answer

APPROVED ANSWER SELECTED ANSWER

replied on August 3, 2020

Xiuhong,

Thanks for pointing me in the right direction!

I was working on the query for a while after I posted this, and got it to a point where I could map the Lookup Rules to the databases, but couldn't get it mapped back to the process name without getting inaccurate results (it was showing me all of the process that were added to the data sources again, even if they didn't have active Lookup Rules).

Your response pointed me in the direction of the "cf_field_column_mapping" table, which I hadn't reviewed before, then I figured out how to map the Lookup Rules to the Form IDs, then I could get it mapped back to the processes accurately. Here is the query I'm using now, that's giving me exactly what I'm looking for (Data Source, Process, and Form only with ACTIVE Lookup Rules):

SELECT Lookups.database_display_name AS Data_Source, cf_business_processes.name AS Process_Name, cf_forms.name AS Form_Name

FROM (
	SELECT DISTINCT cf_lookup_rules.lookup_rule_id, cf_lookup_rules.external_datasource_id, cf_field_column_mapping.form_id, cf_form_process_mapping.process_id, cf_external_databases.database_display_name
	FROM
	cf_lookup_rules

	LEFT OUTER JOIN cf_field_column_mapping
	ON cf_lookup_rules.lookup_rule_id = cf_field_column_mapping.lookup_rule_id

	INNER JOIN cf_form_process_mapping
	ON cf_field_column_mapping.form_id = cf_form_process_mapping.form_id

	INNER JOIN cf_external_dbtables
	ON cf_lookup_rules.external_datasource_id = cf_external_dbtables.datasource_id

	INNER JOIN cf_external_databases
	ON cf_external_dbtables.database_id = cf_external_databases.database_id

		WHERE cf_lookup_rules.is_disabled != '1'
		AND cf_lookup_rules.external_datasource_id IS NOT NULL

) AS Lookups

INNER JOIN cf_forms
ON Lookups.form_id = cf_forms.form_id

INNER JOIN cf_bp_processes
ON Lookups.process_id = cf_bp_processes.process_id

INNER JOIN cf_business_processes
ON cf_bp_processes.bp_id = cf_business_processes.bp_id

WHERE cf_business_processes.is_deleted != '1'
	AND cf_business_processes.is_archived != '1'

ORDER BY Data_Source

Thanks again!

1 0

Replies

replied on August 3, 2020

Open the data source from the Data Sources page, find the number in the URL and replace {datasourceid} with the number in following queries and execute the queries to get a list of form IDs that use the data source

IF OBJECT_ID('tempdb..#DatasourceID') IS NOT NULL
DROP TABLE #DatasourceID

select datasource_id into #DatasourceID from [cf_external_dbtables] where database_id={datasourceid}

Insert into #DatasourceID select datasource_id from [cf_external_stored_procedures] where database_id={datasourceid}

select DISTINCT form_id from [cf_field_column_mapping] where lookup_rule_id in (select lookup_rule_id from cf_lookup_rules where external_datasource_id in ( select * from #DatasourceID))

IF OBJECT_ID('tempdb..#DatasourceID') IS NOT NULL

DROP TABLE #DatasourceID

2. open the lookup rule page of each form using URL such as http://{formsserver}/Forms/design/lookup/{formid}

2 0

You are not allowed to follow up in this post.

Question

Question

Forms Data Source - Linked Processes

Answer

Replies

Sign in to reply to this post.