RFL200021: How to auto-populate RayFlow dropdown & multi-dropdown data fields with data from a 3rd Party database

A RayFlow project has a number of data fields that are of either the dropdown or multi-dropdown type, and will be used to reflect information that resides within a 3rd Party database. 

Instead of manually adding each value to these data fields, the following process describes how to accomplish this via SQL queries that could be run from within a stored procedure or via a SQL Server Agent job associated with the RayFlow database: 

 

--Locate ParamterListID for the ddl or ddm data field
SELECT * FROM ParameterList 

 

--If 3rd Party database resides on same SQL instance as the RayFlow database, utilise this query
INSERT INTO ParameterValues (ParameterValueID,ParameterListID,ParameterValue,IsActive)
SELECT NEWID(),'ParameterListID_value',third_party_column_name,1 FROM third_party_database.schema_name.[object_name] --add a where clause if required 
AND third_party_column_name NOT IN (SELECT DISTINCT ParameterValue FROM ParameterValues)

 

--If 3rd Party database resides on a different SQL instance than that of the RayFlow database,

--create a linked server from the RayFlow SQL instance to that SQL instance, and then utilise this query
INSERT INTO ParameterValues (ParameterValueID,ParameterListID,ParameterValue,IsActive)
SELECT * FROM OPENQUERY ([linked_server_name], 'SELECT NEWID(),''ParameterListID_value'',third_party_column_name,1 FROM third_party_database.schema_name.[object_name] --add a where clause if required') alias
WHERE alias.third_party_column_name NOT IN (SELECT DISTINCT ParameterValue FROM ParameterValues)

Have more questions? Submit a request

Comments

Powered by Zendesk