External EntitiesLast Updated: 07/26/2016 Introduced in Verision: 3.2 |
This tutorial demonstrates how to create and configure External Entity data structure in Decisions. External Entity was introduced in Decisions 3.2. External Entity is a data structure that helps to work with the objects that are not stored in Decisions database. With External Entity in Decisions it is possible to create Actions for External objects like: Search, Edit, Delete etc. Moreover, it is possible to Report on such objects. For instance, we may only have a connection to one folder on some FTP server. This folder may contain XML or JSON files with some data (like resume). With External Entity data structure in Decisions we may interact with these files and treat them as Decisions stored entities.
Example:
In this example we are going to create External Entity data structure based on the External database table that Decisions not integrated with.
Our simple database table looks as following…
Our Decisions instance is not integrated with this database table.
We begin in the Designer Folder with selecting External Entity option from Datatypes/Database > User Defined Types on the Folder Actions Panel.
In the Add External Entity window we Name the Type and Add Data Members.
After we added all Members, we click Ok to save External Entity data structure.
Just like any Defined Data Structure the system creates Structure itself and Configuration Folder in our Designer Folder.
We navigate to the Configuration Designer Project for our External Entity data structure. In the Configuration Folder we should be able to see six Flows that are generated by the System. We need to configure these Flows to be able to interact with External Entities and treat them as they are stored in Decisions.
The Flows are:
Get ID From Entity – here we need to build logic to define External Entity’s unique field as an ID in Decisions system.
Search – build logic to search Entity based on the input text in the Portal search bar.
Save – build logic to Save/Update External Entity. This Flow adds Edit action on the Entity in Decisions. This action pops up a window with Entity’s properties where user can change them and save the changes.
Fetch – logic to Get single Entity by Id (defined in Get Entity By Id Flow).
Fetch All – logic to Get All existing Entities of defined type.
Delete – logic to delete Entity by Id.
Additionally to these required actions we can create our custom Actions like for any Defined Data structures.
First, we need to build Get ID From Entity Flow. We locate a thumbnail for this Flow and click Edit Flow action.
In the Flow Designer we simply connect the outcome from the Start Step to the End Step in the Flow. Then, we select End Step. For the Id Input field to the End Step we Select Value of CustomerID Entity field. The logic is that this Flow takes in an External Entity and defines which field (or merged fields) will serve as unique identifier for the External Entity in Decisions.
This completes Get ID From Entity Flow. We can save the Flow and close Flow Designer.
Back in the Configuration Designer Project we locate Fetch Flow and Edit it.
This Flow should output a single Entity based on the Id input. Id is defined in Get ID From Entity Flow. In this example we are going to query external database to get desired record. We add Raw SQL Step from Integrations > My Integrations > Databases > Advanced category.
Then, we connect both outcomes from the Raw SQL Step to the End Step in our Flow.
Next, we select Raw SQL Step on the workspace and configure it as following. We are going to Use Custom Connection. Pick MSSQL Provider. Then, check Return Data checkbox and select Data Rows as Return Type.
For the Connection String Input we provide Constant connection to our External Database.
For the SQL Command Input we select Text Merge. Plain Mapping type and click Show Editor for Merged Text.
In the Merge Text Editor we create a query that returns a record where CustomerID matches id Input into this Flow. Click Ok to save and close Merge Text Editor.
Then, we select End Step on the workspace and click Show Mapping Editor option.
In the Mapping Editor we use Build Data Mapping type for the Entity. Then, we expand Result outcome from the Raw SQL Step, and using fields from the First item in the array we build our Entity. Save and close Mapping Editor when finished.
This completes our Fetch Flow. Save the Flow and close Flow Editor.
Back in the Configuration Designer Folder we locate and Edit Search Flow.
As mentioned previously in this tutorial, we need to build Search logic that will be Fetching our External Entities based on the input text into the Search bar in the Portal. First, we are adding Raw SQL Step from Integrations > My Integrations > Database > Advanced category.
Then, we configure this step as following… We are going to use Custom Connection to the MSSQL provider and Return Data Rows. In the Inputs to the step we are defining Constant Connection String to the database. For the SQL Command we use Text Merge.Plain Mapping Type and click Show Editor.
In the Merge Text Editor we define query command that uses Search Text to match CustomerID. Click Ok to save Merged Text.
Next, we connect Failed outcome from the Raw SQL Step to the End Step in our Flow. On the Done outcome we add ForEach Step from the All Steps [Catalog] > Flow Management category.
In the resulting window we need to pick a collection for the ForEach Step. CustomerIDs outputted in the Result > Field01 > TextValue array, so we pick this collection and click Next.
On the Next outcome from the ForEach Step we add generated Get [NorthwindCustomer] step from the Integrations > All Integrations > External Entities > [RootFolder.SubFolder.ExternalEntityTypeName] category. Note: Functionality for this step we have implemented in the Fetch Flow for our External Entity.
Then, we configure data mapping for the Get [ExternalEntity] step. This step requires Id as an input. We use Select Value Mapping Type. With Path picker we select Item output from the ForEach Step.
Next, we are going to create a List of our External Entities for the output for this Search Flow. We add Add Item to List step from the All Steps [Catalog] > Data > List category.
Then, we connect Done outcome from the Add Item to List step back to the ForEach Step and click Show Mapping Editor option.
In the Mapping Editor we connect Entity as a New Item and New List as an Original List. In the Outputs from Add Item to List step we Change Value of the New List. In this way we simply update the List with New Item on each iteration. Save and close Mapping Editor when finished.
Then, we connect Done outcome from the ForEach Step to the End Step in our Flow. For the Entities Input into the End Step we Select Value of the New List.
This completes our Search Flow. We can save the Flow and close Flow Designer.
Now, we should be able to Search our External Entities in the Portal but they will not have any actions available. To add Edit action for the External Entity we need to implement logic for the Save Flow. We locate Save Flow and Edit it.
In the Flow Designer we add Raw SQL Step from the Integrations > My Integrations > Database > Advanced category.
Then, we configure Raw SQL Step as following… We use Custom Connection to the MSSQL Provider. Then we define Constant Connection String Input. For the SQL Command we choose Text Merge.Plain Mapping Type and click Show Editor.
In the Merge Text Editor we define Update database record statement based on the Entity Properties input into this Flow. Click Ok to save and close Merge Text Editor.
Back in the Flow Designer we connect both outcomes from the Raw SQL Step to the End Step in our Flow. This completes our Save Flow. It does not return any data, it just updates our External Entity. We can save the Flow and close Flow Designer.
Now, when we search our External Entity in the Portal, we are going to have Edit action on the Entity. Click Edit action…
Edit window pops up with Entity properties that we can Edit.
We edit some fields and click Ok.
Then, if we search for the same External Entity and click Edit action…
We can see that our changes to the External Entity was saved. Click Ok to close Edit window.
Next, we are going to locate Delete Flow in the Configuration Designer Folder and Edit it.
In the Flow Designer we add Raw SQL Step from the Integrations > My Integrations > Database > Advanced category.
Then, we configure Raw SQL Step as following… We use Custom Connection to the MSSQL Provider. Then we define Constant Connection String Input. For the SQL Command we choose Text Merge.Plain Mapping Type and click Show Editor.
In the Merge Text Editor we define Delete statement that is going to Delete our External Entity from the database based on the CustomerID field that can be found under the Entity input to this Flow. Click Ok to save and close Merge Text Editor.
Back in the Flow Designer we connect both outcomes from the Raw SQL Step to the End Step in our Flow. This completes our Delete Flow. We can save the Flow and close Flow Designer.
Back in the Portal we can Search for our External Entity, and when we click on the External Entity, we can see new Delete Action.
If we click this Delete Action, Confirmation window pops up. We click Ok and Delete Flow deletes our External Entity from the database.
Finally, we are going to Edit Fetch All Flow. This Flow will generate Get All step to use in the Flows. Locate Fetch All Flow and click Edit.
This Flow is going to be designed in the similar manner we designed Search Flow for our External Entity. First, we add Raw SQL Step from the Integrations > My Integrations > Database > Advanced category.
Then, we configure Raw SQL Step as following… We are going to use Custom Connection to the MSSQL Provider and Return Data Rows. Next, we define Constant Connection String Input. For the SQL Command Input we use Text Merge.Plain Mapping type and click Show Editor.
In the Merge Text Editor we define a query statement to return all rows from the database table. Click Ok to save and close Merge Text Editor.
Then, on the Done outcome from Raw SQL Step we add ForEach Step from All Steps [Catalog] > Flow Management category.
In the resulting window we expand Result > Field01 outcome from the Raw SQL Step and select TextValue Array for ForEach Step and click Add. This array contains collection of CustomerIDs.
On the Next outcome from the ForEach Step we add Get [NorthwindCustomer] from Integrations > All Integrations > External Entities > [PathToExternalEntityTypeFolder.ExternalEntityTypeName] category.
Then, we add Add Item To List step from All Steps [Catalog] > Data > List category.
Next, we connect Done outcome from the Add Item To List Step back to the ForEach Step in our Flow and Done outcome from the ForEach Step to the End Step. Connect Failed outcome from the Raw SQL Step to the End Step in the Flow.
Then, we select Get [NorthwindCustomer] step on the workspace and configure data mapping for this step. For the Id Input we Select Value of the Item outcome from the ForEach Step.
Next, we select Add Item To List step on the workspace and click Show Mapping Editor option.
In the Mapping Editor we connect Entity to the New Item field, New List to the Original List, and on the Outputs from the step we Change Value of the New List. Click Ok to save and close Mapping Editor.
Then, we select End Step on the workspace and Select Value of the New List as Entity Input to the step.
This completes our Fetch All Flow. We can save the Flow and close Flow Designer.
To be able to Report on the External Entities we need to create a Data Source. We can do this by building Reporting Data Flow. In the Configuration Designer Folder we click Create Reporting Data Flow on the Folder Actions Panel.
Then, we Name the Flow and click Create to proceed to the Flow Designer.
In the Flow Designer we add Get All [NorthwindCustomer] step from the Integrations > All Integrations > External Entities > [PathToExternalEntityFolder.ExternalEntityTypeName] category. This step is generated after we implement logic in the Fetch All Flow.
Then, we connect Done outcome from the Get All step to the End Step in our Flow. We select End Step on the workspace and for the ReportData Input to the step we Select Value of the Entities outcome from the Get All step.
This completes our Reporting Data Flow. We save the Flow and close Flow Designer.
Now we should be able to Report on the External Entities. In the Designer Folder we click Create Report/Page > Create Report on the Folder Actions Panel.
Next, we Name Report and click Create to proceed to the Report Designer.
In the Report Designer we can locate and add Data Source for our External Entity in the Data Sources > Flow Reports category.
Then, we add desirable columns for our Report, save Report and close Report Designer.
Back in the Designer Folder we locate our Report and run it.
Report runs with all External Entities which are not stored in Decisions. We can click on any External Entity in the Report and get our designed Actions on this Entity.
