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-10-2023

I have not used that way but I have used this one for new users in prod to get them in Dev and Test.

https://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/import-sets/concept...

When it pulls over a record and the assigned to field value does not exist in the user table it just looks blank until you import that user. It does not try and create records in the tables the reference fields point to. So it just looks like this until the referenced record is created or imported.

Dave Oshinsky · ‎05-10-2023

On my PDI, the asset field seems to refer to alm_asset table (which has 730 children referring to it!). Assume we're importing table T to destination instance B, using data exported from instance A, and the asset field contains id X for a particular row. So, you're saying that the destination child table T on instance B (refers to parent alm_asset via asset field) is imported without error? Initially the asset field appears blank in the UI? Once an import is done to alm_asset for a record with id X, the asset field for that record changes from blank to something valid? Does the id change back to the original id X, or to a newly assigned id?

Table API (Reference | ServiceNow Developers) POST /now/table/{TableName} can only handle one record at a time, which would be prohibitively slow when inserting many records. I'm finding some more appropriate API's to use:

Import Set API | ServiceNow Developers

Import sets key concepts (servicenow.com)

The Import Set API is probably the REST API used under the hood for XML Import sets. Sorry about 1,000,001 questions....

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.

Dave Oshinsky · ‎05-10-2023

Thanks DrewW, that is fantastically informative!

DrewW · ‎05-10-2023

At least I learned something in the time I have been doing this.... 🙂