Exporting Purchase Order and PO Line Items from ServiceNow into FNMS

[jaalstead]

We recently moved the creation of Purchase Orders / PO Line Items from FNMS into ServiceNow. However, we don't use SN ITAM so our software licenses and reconciliation still reside in FNMS. Our Asset Team would like  all "software" type of POs copied from SN into FNMS. My thought was to export from SN into an excel spreadsheet on a nightly basis and then use the Business Adapter to import. However, I'm having some issues because it appears to create the PO records, but not the POL in the database.

I attached some screenshots to this to show what my PO and POL look like in the business adapter.

A few questions:

1. Has anyone done this successfully before? If so, what are the gotchas? Do you have a sample .xml file I could use to map my fields correctly on the Purchase Order and Purchase Order Line items in the business adapter?

2. Is my issue with not creating the POL because I don't have a line item? ServiceNow doesn't have sequential line items (1, 2, 3, etc...) that I'm able to use in my spreadsheet. I would need to find a way to populate a column programmatically with sequential line items starting over at 1 for each new PO.

Any help would be appreciated!!!

Jason

[kclausen]

@jaalstead - You are on the right track.  The PO Line Number is a required field within FNMS/ITAM, which is why you do not see any PO Lines being imported.  You may need to perform some additional research, because it is very common to have multiple lines on a single PO.  ServiceNow must have some identifier to distinguish multiple items for the same PO.

A potential work-around would be to use the row number in Excel as your Line/Sequence Number and match on that.  The downside here is that if you happen to export the same item out of ServiceNow into Excel, it will likely have a different row number and you will not be able to match to the original PO Line that you created in FNMS/ITAM and you could import duplicates.

[jaalstead]

SN Does have a PO Line Item number, but it isn't sequential within the PO itself. For example:

PO12345678 might have line items POL00000001, POL00000010, POL00000018, POL00000023

Since it is a string and not numeric, I don't think it would work for the POL in FNMS.

Is there a way to do a sql before the import on the POL field from SN? Somehow convert the POL to a sequential # for each PO? Not 100% sure, just thinking of a way.

[kclausen]

@jaalstead - The PO Line Numbers within FNMS do not need to be sequential, they just need to be unique with a Purchase Order.  I think you would be able to remove the "POL" prefix at the start of the ServiceNow line number and use the remaining characters as the line number within FNMS.  In your example, PO12345678 would have line numbers of 1, 10, 18 and 23.

[jaalstead]

Thanks! I'll give that a try!

[mfranz]

You can import Purchase Order Lines without an original Item Number. We do regularly import data from system lacking these. You could create your own Item Number using SQL and use other fields for for matching in the business adapter.

Another consideration in this context: You may want to make sure that you can match each POL exactly to it's original record in SN. That may include having a custom field to store the original string - that is assuming you run FNMS on-prem.

[jaalstead]

Good idea actually. Thank you!

If a column has POL0000123, is there an easy way to strip this off in the Business Adapter by doing a query somehow or a transformation?

[mfranz]

Easily possible with SQL, multiple options could work. For example removing POL from the string and converting the rest to an int.

CONVERT(INT, REPLACE(PurchaseOrderLine, 'POL', ''))

Or taking right 8 characters converting them to an integer. It really depends on the data schema you're getting from SN.

CONVERT(INT, RIGHT(PurchaseOrderLine, 8))

You could encase this into a case statement checking the sub-string with ISNUMERIC(). Just to avoid hard errors.

CASE
WHEN ISNUMERIC(REPLACE(PurchaseOrderLine, 'POL', '')) = 1 THEN CONVERT(INT, REPLACE(PurchaseOrderLine, 'POL', ''))
ELSE NULL
END AS PurchaseOrderLine

You'll need to use "real" SQL, so you might need to stage your data first.