format of exported link info

Dave Oshinsky · ‎05-08-2023

I'm comparing different API's and formats for exporting table data. So far, I've found this Export API (e.g., incident.do?JSONv2 to get JSON)

Exporting and converting records into complex data types (servicenow.com)

and the Table API

Table API | ServiceNow Developers

Only the former (Export API) can export in CSV format, while both can export JSON or XML. Links between tables are expressed differently, with Export API exporting something like JSON

"opened_by":"6816f79cc0a8016401c5a33be04be441"

and Table API exporting something like JSON (note the extra link key)

"opened_by":{"link":"https://MyInstance.service-now.com/api/now/table/sys_user/681ccaf9c0a8016400b98a06818d57c7","value":"681ccaf9c0a8016400b98a06818d57c7"}

CSV exported from Export API completely omits all link fields like opened_by.

Is there any way to get a CSV including the link fields? The link URL from Table API seems redundant, since the link value (id) and the table SCHEMA info for opened_by already specify that it's a link to sys_user table. Incident SCHEMA info for opened_by shows it's a link/reference to sys_user:

Does anyone have comments on best practices for exporting links? CSV would seem to be ideal, as it's more compact than XML or JSON, but exported CSV's don't include any link fields.

DrewW · ‎05-10-2023

I'm going to use an XML import as an example but its just to get the point across.

So lets just say that you go to instance A and export table X to XML. Table X has reference field R (refR) that points to table T. When you import table X into instance B the system just sets all of the fields to the values that are imported. So refR gets set to a value and the system does nothing else, it does not check it, it does nothing other than store the value. Since reference fields just store a 32 character alphanumeric value the system does not care that the value in refR is and does not care that the value in refR does not exist in the sys_id field of table T. To prove this you can even use a script to set a reference field to any value you want, like say "Frogs are green". The system will take it, it does not care. The Business Logic looks up the display value for refR when you look at the form or the list. If it does not find the value that is in refR in the sys_id field in table T it just moves on. Which is why you get the screen shot I posted. If you then export table T to XML and import it into instance B you will have imported the sys_id that refR has stored and the next time you open the form or list the system will find the sys_id in table T now and will show the display value for that record.

The database does not use foreign keys at the database level, it uses business logic for that.

If you want to test setting a ref field to "Frogs are green". Background script follows and you just need to pick your own sys_id to load.

var i = new GlideRecord("incident");
i.get("cbb77b101b36a910edd354e4604bcbe8");
i.setValue("caller_id", "Frogs are green");
i.update();

So when you import data you can put anything you like in any reference field. Its just more helpful if you make sure those values are valid sys_id's that do or will exist in the referenced table at some point. It can be handy because it allows you to import the tables in any order you like. You just need to make sure that when you import the data from instance A that you use the sys_id's from instance A when creating the records in instance B and things will be good.

View solution in original post

DrewW · ‎05-09-2023

Its going to depend on what you are doing with the data. If you are using this to replicate the data to a database then I would just make sure you have the sys_id's for the values in Reference fields, the URL links to the records they are referencing are of limited use in making an offline copy of the database. If you are using this for an excel file for some kind of report or data analysis then I would make sure you just get the Display values.

I personally would avoid CSV. There are many fields in the system that can have data in them that I have see screwed up in a CSV formatted export. I have always had good luck with XML and JSON so I do not even bother looking at CSV unless someone tells me it absolutely has to be that way.

Dave Oshinsky · ‎05-10-2023

Thanks DrewW. Even as a ServiceNow newbie, I've already seen that CSV export format is screwed up. It's completely missing all "reference" fields. This is unfortunate, as CSV is much more compact (in size) than JSON or XML.

Inserting exported data back to the source instance (from which it came) is a primary goal. So long as all references (to which that data refers) remain intact, it is straightforward. Things get more complicated when the parent items (to which exported data refers, by id) is missing.

Things get still more complicated when one is trying to take data exported form one instance, and insert that data (in some massaged form) into another instance. Many tables refer to sys_user. Can data referring to sys_user (or another other parent table) be inserted to a different instance than the source (with some mapping of id's to destination instance id's, and/or by inserting parent records e.g. to sys_user first)?

DrewW · ‎05-10-2023

When you say a different instance do you mean another ServiceNow instance? If so then yes you can just pull/push the data over to the other instance. The chance of an overlapping SYS_ID is very very low. You also do not necessarily have to push/pull parent and user data first, but it would probably be a good idea to do so.

Also if you are moving data from one ServiceNow instance to another ServiceNow has a built in way to do that.

Dave Oshinsky · ‎05-10-2023

Yes, into another instance than the one that exported the data. I currently have easy access only to one instance (my PDI), so have not tried it. I would think that the POST (import) to the other instance (than the exporting one) would fail when there are link/reference id's (e.g. to sys_user, or to numerous other possible parent tables in the destination) that are invalid on the destination instance.

I assume you refer to this built-in way of replicating data from instance A to instance B:

https://docs.servicenow.com/bundle/sandiego-servicenow-platform/page/administer/instance-data-replic...

I am wondering whether this deals with table references (fields in one table containing id's that refer to another table). If yes, how? By mapping id's into id's on the destination instance, possibly inserting records to which a record refers first?