Table Mapping from External API

Normally in ServiceNow, often the standard way to handle an external data source writing to tables in the instance, is to use staging tables and transform maps/scripts. For this example I am going to show another way to achieve the same result using Flow Automation.

I want to stress from the outset, that this is a simple demonstration, and for such an idea to be solutioned, developed, tested and moved into production there would be much more robust lifecycle management. Particularly for the logic presented here, as this was more about getting it up and running. Far more rigorous validation and cleaner logic would be developed, and I am sure while looking at this you can see areas for improvement.

However for this, I just want to demonstrate the following:

• Set up a basic Catalogue Item that requests a company name from the O-Data Service
• Use Flow Process Automation to make the API call, handle the request, and check if the requested company does not already exist
• If the Company does not exist, use the data fields from the response body, and map them to the User and Company tables in ServiceNow

Step 1 - Create a basic Service Catalogue Item

Create a basic catalogue item with a single-line text field, either create direct on the Default view or use catalogue builder. Make sure you place a link somewhere on the catalogue for the users to easily look up the North-Wind suppliers data in another browser window. The user can simply pick out a Supplier name (CompanyName field) to enter into the field.

Step 2 - Create an Action that Calls the North-Wind Service

I have set up the Process Automation to have the API request completed in a standalone Action, that way it frees up my Flow to just perform logic, and I can also re-use the API Action for other purposes.

Important to note, that when deconstructing the URL request to take parameters in the Action, its easier to test this in Postman first, then fill in the fields correctly for a 'REST' step .

This is the payload we want returned, and you can see below how to have set up this step in Flow Designer so it returns the response body. Once I have the response working I could then start parsing the data with a basic script step. Alternatively you can also use the JSON parse step, which is really neat. You simply copy/past your response payload out of Postman, then it will create the object for you which can be used for outputs.

The next step after the request is to extract the response body and set them as output variables that can be used in the flow. See script below, very simple using inputs and outputs. The object/array model will always be slightly different depending on which service is being requested, to get this right just have a look at what the payload response is like in postman. Sometimes you will have deep nested objects and arrays that you will have to navigate through.

Now, when I am using this Action in a Flow, its easy to access the data pills with the attribute values I need for mapping to a ServiceNow table. These can be either dot-walked in the Flow steps, or can drag and drop.

Step 3 - Create the Flow Logic

Now the Action is working correctly, and providing the output variables with the data I need, I can start setting up some Flow logic. See the diagram view below from Flow Designer; a service request is made as the trigger, then I want to retrieve the parameter the user has passed into the Catalogue Item, and forward that to the API Action I made previously.

Once the request is made, I then want to compare it to what is already in the company table and end the flow if it already exists. Otherwise there will be duplicates created every time the service is called.

If the requested company is non-existent on the core table, then I can go ahead and create both the User record for company contact, and the new company record.

In the update record steps, there are basic fields modified depending on the conditions.

If after all the validation checks are made, it is now possible to go ahead and create the new records needed, and finally update the RITM. Firstly, see the Flow step for creating a User record below, and notice where the values are coming from (previously made Action).

This newly created User, will now be used as the Company Contact in the core_company record that is created in the following step. See below:

Step 4 - Test the end to end process

After testing each step along the way, the whole process can be seen fully working as intended, the RITM fields update, and the new User and Company Records are made, mapped from the API request to North-Wind service.

For this demonstration, I showed how to make an outbound API request and then use that payload response to map both the User and Company tables in ServiceNow. The way this was achieved was by using a Service Catalogue Item, linked to a Flow. Which then uses an Action to make a request to the external service.

Have a great Christmas! ??