External Entity CRUD Actions

Preparation 

This article will demonstrate using the Create, Selective Update, and Delete Flow steps for Decisions External Entity Structures. Before continuing to the next section, create a Database integration. The database that is integrated with will need to have a table with the following fields:

To create the table and values quickly, copy and paste the SQL commands in SQL Server Management Studio. These commands will create the table and insert test values that will be used later.

CREATE TABLE person (PersonID varchar(255), Firstname varchar(255), Lastname varchar(255), DOB date);

insert into dbo.person (PersonID, Firstname, Lastname, DOB)
values ('8ce8be77-fe28-43ef-8b96-4c3743192a0e', 'Alan', 'Dredger', '2020-10-30');

insert into dbo.person (PersonID, Firstname, Lastname, DOB)
values ('f048a932-4619-4b8e-8b94-79450b59af9c', 'Justin', 'Ceer', '2020-10-25');

insert into dbo.person (PersonID, Firstname, Lastname, DOB)
values ('cf80321e-6c6c-4e66-867e-ec24d710df39', 'John', 'Eisen', '2020-10-17');

Create External Entity Datatype

The final part of the setup is to create an External Entities Decisions Data Structure. It will have the same fields as the external database table.

The Configuration Folder for External Entities will come with default CRUD action Flows inside the folder. These Flows are not pre-configured and will need to be designed. 

Create 

To create a record in the Person Entity Data Structure

1. Create a Flow
2. Expand the 'User Define Types' category
3. Expand the External Entities category
4. Expand the 'Person' datatype
5. Drag over the 'Save Person' step onto the flow canvas
6. connect it to the 'Start' and 'End' step
7. On the 'Item To Create' input, change unknown to 'Build Data'
8. Set 'Folder Id' to a constant value, and pick the 'Person Entity Data Folder'
9. Set constant values for 'Name', 'Firstname', 'Lastname', and 'DOB'

Click the 'Debug' button to run the flow. Decisions will create that record in the Person Entity Structure.

Update

To update an existing record in an Entity Structure the 'Save' step will need to be used. This is found in User Defined Types -> External Entities -> Person. This step can change one or more data fields of a record in an existing Data Structure. The 'Get All [Person]' step found in 'User Define Types' will be used to retrieve data. Map the Id of the first or last record from the list into the 'Save[Person]' step Id input. Once the Id has been mapped in, users can change the value of any data field to update that record.

'Save [Person]' Step

Delete

To remove records from the External Entity data structure, use the 'Delete [Person]' step found in User Defined Types -> External Entities -> Person.  This step expects the 'Person' composite type as an input. Once a 'Person' composite type has been provided, running the flow will delete that record from the Data Structure.