Disclaimer: This requires the STARTER pack for #IntegrationHub (the smallest package) or license to use the Utility Action Spoke.
Sometimes there is a need to extract data from ServiceNow – it can revolve around multiple tables, records, and sometimes even some "standard text" that should be included. It makes it hard to create a database view with all these requirements. Another suggestion I've seen is to create a background script and build up a comma-separated string that you then copy to, e.g., Notepad, open Excel, and then import that data. Quite messy and hard to run as part of a process.
So, is there an alternative? Yes, it involves zero coding, Flow Designer, the smallest IntegrationHub subscription, and a JSON object (does that count as coding?? :D). Anywho, it's better than a Kinder Egg: The Utility Actions Spoke!
It requires the activation of the Utility Actions Spoke plugin as seen below. Remember to install the latest update!
After activation a new Application Menu appears (IntegrationHub Utilities). Find the module called "File Schemas" under it.
What does your final result look like
Before you go any further you need to have an idea how what your final product will be like. What columns do you need, what type are they and then you can go to create the file schema. The file schema is a JSON object that needs to be filled out with all the columns and headers you want to appear in your final CSV file. Here is the documentation for how to fill it out, but I have given a simple example below:
For this example, I have created a simple Request Item with a multi-row variable set and a regular string variable so that we can generate an output. But again, the file can be created on any input, so it can be whatever you want.
Create a flow in Flow Designer
After you have created the File Schema and saved it, you create a new Flow. When you have created your trigger, you add a new "Action" to the Flow. Search for the Utilities Spoke and find the one called "Begin File". There is also one called zip, which makes it possible to create a zip file with multiple files. But for this demonstration we will just be creating a single file:
You then name your file - it can be dynamic or static as I have chosen below here. The important thing is to have .csv in the filename, delimited as File Type and that you select your newly created File Schema:
The reason you need the File Schema is that otherwise, you are not able to write to any columns in the file. Next, you need to add another action from the Utilities Spoke called "Append to Delimited File." This writes a new line to your .csv file and requires the input of the file you created in step 2 above, together with the File Schema. Once this is selected, you can click on the "Add field value":
When you click Add field value, it gives you all the columns you have provided in your File Schema. It would be best if you then mapped the values on those. In my example, I am looping through a multi row variable set, and for each row, I am adding a row to my file. I am providing one static value in Test 3, a standard variable in "Number Variable" that is the same for all rows, and for the rest, it is coming from the multi-row variable set:
It is essential to understand that for each "Append to Delimited File," we are adding a row to our CSV file - how you populate the data is totally up to you. The data could come from various sources; it can be scripted and calculated here or be some static text.
When you are done mapping your columns to your values and don't need more rows, it is time to build the file. Add a new action to your Flow. Again, find the utilities action spoke and add the action "Build File." It needs the input of the "Begin file" in the File field:
Then it created a file but placed it in a not-very-useful position in the action hub table. So, let's move it to where you need it. First, let's use the action "Get Attachments on Records" to retrieve the attachments we have created (if we have created more). Use the "Begin file" record reference as the Source Record:
To a for each loop in the flow and reference the "Get Attachments on Record" above. For each attachment, we wish to add it to someplace useful. It could be sending it by email, or it could be, as it is in this case, attaching it to a record (RITM) and sending it to the user. For this, we use the "Move Attachment" Action, where the source record is the looped record, and the target record, in our case, is our Trigger item:
Time for testing
Now your flow is done - at least enough to test if it is working. Make sure you have a test record with valid data. In this case, we have created a RITM with some demo data so that we can test it:
After testing, let's open up the record and see if we have a result:
Eureka! We have a custom CSV file without doing any coding! It is a CSV file; sometimes, you want it as a .xlsx. I haven't been able to generate a "true" .xlsx file, but it is straightforward to convert the CSV file to a .xlsx just by opening up Excel and then following the below gif:
Voilá - there you have it. How to create a custom .csv file in ServiceNow without the limitation to related records, database views, or background scripts. Just by using Flow Designer - and the beauty is that it is done in such a quick fashion, it can be implemented as part of a process, and it can be reused.
