Import a CSV dataset into a nested field

A comma-separated values file, or 'CSV', is a plain text file format commonly used to import and export data between systems that can be used with nested fields in Flowfinity Actions. Each line in a CSV file represents a record, and values within those records are separated by a comma.

A nested field in Flowfinity itself is also of a plain structure, but it is located within an inner level of a parent record. This presents a challenge because the data import operation will only import CSV records into the top level of the nested hierarchy, not directly into a nested field.

This article describes how to configure a 'Task' to import a CSV dataset into a nested field. Tasks are performed by software robots to update records or to connect with other apps to launch operations on new or existing records.

By creating an intermediary application that has an Import operation, you may configure a Task using a software robot that will append data to the desired nested field as a sub-item.

Example

You have a reference table of thermal resistor electrical resistance values used to translate the values of electrical resistance into the degrees of temperature.

We will use the method described above to import a CSV dataset into the 'Probe Conversion Table' nested field within a sample 'Temperature Probes Reference Data' application.

Nested field 'Probe Conversion Table' as shown within the app editor:

Instructions

1. As it was mentioned it is necessary to create an intermediary application that reflects the structure of the 'Probe Conversion Table' nested field in the 'Temperature Probes Reference Data' application and an Import operation with a Task assigned.
2. Open the app editor and create a new application named ‘Nested field CSV Import Helper.'
3. Copy all the required fields from your target app, we're using the 'Probe Conversion Table' nested fields.
4. Create new fields 'Temperature Probe Type' and 'Probe Type Record Exists.'
   The 'Temperature Probe Type' field is a key that will be used to find and match the appropriate records in the 'Temperature Probes Reference Data' application.
   The 'Probe Type Record Exists' field flags whether a record for a particular Probe Type already exists within the 'Temperature Probes Reference Data' application.
   
5. Go to the 'Operations' tab.
6. Create a 'Task' and give it the title 'Import to Temp Probes Ref Data Nested'. Add a description if necessary. Set the status of the Task to 'Enabled.'
   We will then create four Actions that will be explained in the next few steps:
   1. 'Check Probe Type Record Exists'
   2. 'Create Record And Append To Nested'
   3. 'Edit Record And Append To Nested'
   4. 'Reset Probe Type Record Exists Field'
   
7. Click 'Add data action' to launch the Task Action Editor.
8. Title your Action 'Check Probe Type Record Exists.'
   The 'Check Probe Type Record Exists' Action will query the 'Temperature Probes Reference Data' application to identify whether a record for a given Probe Type exists.
   It then populates the 'Affected Records Count' system variable value into the 'Probe Type Record Exists' field. The 'Affected Records Count' system variable indicates how many records were affected by this particular Action.
9. Navigate to the 'Target' tab. Set the Target to 'Operation.'
10. Set the Target app to 'Temperature Probes Reference Data.'
11. Set the Operation to 'View Record.'
12. Set the View to 'Get Record By Probe Type.'
13. Set Apply to 'First available record.'
    
14. Navigate to the 'Return' tab and click 'Add mapping.'
15. Set 'Source record field' to 'Probe Type Record Exists.'
16. Set 'Type' to 'Field' and 'Target Operation Field' to 'Affected record count.'
    
17. Return to the 'Operations' tab and click 'Add data action' to launch the Task Action Editor.
18. Title your Action 'Create Record And Append To Nested.'
19. Add a 'Condition' set to 'Probe Type Record Exists' 'Equals' 'Constant' '0.'
    The 'Create Record And Append To Nested' Action is conditional and launches only if the 'Probe Type Record Exists' field is equal to 0.
    It will then create a record in the 'Temperature Probes Reference Data' application and append the first nested record into the 'Probe Conversion Table' nested field.
    
20. Navigate to the 'Target' tab. Set the Target to 'Operation.'
    
21. Set the Target app to 'Temperature Probes Reference Data.'
22. Set the Operation to 'Add New Record.'
23. Set the Invocation count to '1.'
24. Navigate to the 'Fields' tab.
    
25. Select 'Map fields manually.'
26. Click 'Add mapping' and assign the Target operation field as 'Temperature Probe Type', Type as 'Field' and Source record field as 'Temperature Probe Type.'
27. Click 'Add mapping' and assign the Target operation field as 'Probe Conversion Table' and 'type to 'Parent fields.'
28. Navigate to 'Mapping' and go to the 'Properties' tab and click the 'Edit nested' control.
    
29. Go to the 'Fields' tab and select 'Map field manually' and fill in the fields, types and values as shown:
    
30. Return to the 'Operations' tab and click 'Add data action' to launch the Task Action Editor.
31. Title your Action 'Edit Record And Append To Nested.'
32. Add a 'Condition' set to 'Probe Type Record Exists' 'Does Not Equal' 'Constant' '0.' The Action 'Edit Record And Append To Nested' is conditional and launches only if the 'Probe Type Record Exists' does not equal 0.
    It edits an existing record in the 'Temperature Probes Reference Data' application and appends a nested record into the ‘Probe Conversion Table’ nested field.
    
33. Navigate to the 'Target' tab. Set the Target to 'Operation.'
34. Set the Target app to 'Temperature Probes Reference Data.'
35. Set the Operation to 'Edit Record.'
36. Set the View to 'Get Record By Probe Type.'
37. Set Apply to 'First available record' and check 'Abort task execution when there are no records returned.'
38. Map the View parameter to 'Probe Type Parameter, Type to 'Field' and Record field to 'Temperature Probe Type.'
    
39. Navigate to the 'Fields' tab.
    
40. Set Mapping mode to 'Map fields manually.'
41. Map the Target operation field to 'Probe Conversion Table', Type to 'Parent fields' and click the 'Edit nested' control.
42. Navigate to Mapping and click the 'Properties' tab.
    
43. Go to the 'Fields' tab and select 'Map fields manually' and fill in the fields, types and values as shown:
    
44. Return to the 'Operations' tab and click 'Add data action' to launch the Task Action Editor.
45. Title your Action 'Reset Probe Type Record Exists Field.'
    The Action 'Reset Probe Type Record Exists Field' will reset the value of 'Probe Type Record Exists' field to blank to control and reset the workflow.
    
46. Navigate to the 'Target' tab.
47. Set the Target to 'Record.'
    
48. Navigate to the 'Fields' tab.
49. Map the Field to 'Probe Conversion Table' and Type to 'Reset.'
    
50. Save and publish your application 'Nested field CSV Import Helper.'
    Now due to the import operation in the 'Nested field CSV Import Helper' application the 'Probe Conversion Table' nested fields in the 'Temperature Probes Reference Data' application are now be filled with the dataset from the CSV file!
    

For more information about nested fields and CSV imports please contact Flowfinity support.