In this article we will explain a specific use case where we populate a database using the For Each block, which can be found in the Custom blocks section in Omniscope Evo workflow menu.


Note: This Custom block is running a Python procedure behind the scenes, so you need to have Python installed on the machine that is running Omniscope.


In many use cases where there is a need to execute the same procedure for multiple parameters you may consider using this block to manage a looping mechanism.


Let's have a look at a scenario where the user wants to extract multiple csv files from a folder, then populate a database, where each csv file will become a new table in the database.



We will create one Omniscope project that will be tasked to extract the csv file metadata from the target folder, containing the csv details, and the second file (here 'File 2.iox'), that will publish the data to a database.  


Step 1

In the first file we are using the File Attributes source block to connect to the data folder, as we are interested in the file names/ file path only, rather than the Batch Append block, which would extract the data as well.

Following that we will use a Search/ replace block to remove any characters from the file names that might not be welcome in the database table naming convention (dashes, spaces etc.) 


Using the Field Organiser block we will name the two fields of interest [Table name] and [File path]. 


To configure the ForEach block we will use the url address of the File 2, and also reference the 2 parameters : Table name and File path (these will appear as options in the drop-down menu).




Step 2

In the File 2 we will create 2 project parameters with matching names - Table name and File path. There is no need to specify initial values and you can leave them blank for now.


We will connect a File block, which has a File path parameter in the Path field, to a Database Output block, which has the Table name param in the Table name field, and our target database credentials.


(There is a pink link symbol next to every block field that has an active parameter setting)


 

Before executing the first file, containing the ForEach block, it is useful to check 

Admin settings > Advanced >Workflow > Concurrent Workflow Jobs 

that Omniscope installation can execute 2 or more workflow jobs concurrently.


Now we can run and execute the first file, which will trigger the File 2, as our ForLoop block is running the execution remotely, going through the files one by one and populating the target database.


This is just one example where this block can be applied. 

More resources regarding ForEach block and Omniscope rest APIs can be found below:

https://help.visokio.com/support/discussions/topics/42000006475
https://help.visokio.com/support/discussions/topics/42000006554
https://help.visokio.com/support/solutions/articles/42000073133-workflow-rest-apis

and custom blocks:

https://help.visokio.com/support/solutions/articles/42000071270-getting-started-custom-scripts-in-omniscope-evo
https://help.visokio.com/support/solutions/articles/42000071109-custom-block-r-python-api-reference