Import and Transform Maps - Issues with Parent.

[altacc]

Hi.

I have spend many hours and I'm tearing my hair out on this - I've tried so many different things and no joy, even though it seems quite simple (I'd have done it in 2 minutes in SQL Server!)

The issue -

I'm trying to import Location data, including setting the parent. Some Locations have no sub locations and some have sub locations of the same name as other sub locations. E.g.

Building 1

Building 2

--Floor 1

--Floor 2

Building 3

--Floor 1

--Extension

Due to the nature of the tree picker in Service Now only showing the child element when chosen, and which would be confusing for "Floor 1" in the above example, I have reorganised the above data as follows -

Building 1

Building 2

--Building 2 - Floor 1

--Building 2 - Floor 2

Building 3

--Building 3 - Floor 1

--Building 3 - Extension

As well as being clearer when chosen in Service Now it also provides a unique field which I thought would help make things work.

However, when I import and transform I get the following -

Building 1

Building 1 (parent Building 1)

Building 2

Building 2 (parent Building 2)

Building 2 - Floor 1 (parent Building 2)

Building 2 - Floor 2 (parent Building 2)

Building 3

Building 3 (parent Building 3)

Building 3 - Floor 1 (parent Building 3)

Building 3 - Extension (parent Building 3)

So basically I get a "bad" record for every parent, where it creates a row with a parent to itself. I have tried adding in code to get around this. I added this to the onBefore -

  if (source.u_fulllocation == target.name)

      ignore=true;

The result is that no updates are done to parent at all. And I also tried a different way, adding the following to the field map script for the parent field -

  if (source.u_fulllocation == source.u_location) {

      return null; }

  else {

      return source.u_location;

  }

Again, no updates are done to parent in this case. A lot of this is trial and error - I'm fairly new to Service Now and javascript but this seems to me something that should be very simple... but isn't turning out that way.

If anyone can help I would much appreciate it.

[marcguy]

have you chosen to Coalecse on name, look at the field maps related list on the transform map and ensure you have specified a field which is going to be unique, i.e. name or if you have something better like location ID, use that, once you have a coalesced field, it will only create one of that location as opposed to multiple, I think that's what is happening here.

[altacc]

Hi, and thanks for the quick response. I am coalescing on the field "FullLocation" (a concatenation of Building and Floor, if Floor exists) which is unique, yes - my data is in the form like the second list in my example above. I don't have a Location Code or the like, but I figured as long as the field was unique, it's as good as a location code.

[marcguy]

Arh ok, it's not reading the value supplied in the parent mapping and finding the correct parent therefore creating one (incorrectly).

so one thing to check because 'parent' is a reference field, is that your supplying the value for parent that it can match to.

it would expect that it's the value you see in the reference field when looking at it, but if your supplying a different unique field then you can tell the tool this by adding another column to the field mapping related list, called: 'Referenced value field name'

Scenario for using this is when I'm supplying say a user to go into a reference field, I don't want to supply Joe Brown because there may be many so I supply email address and then specify in the referenced value field name: email so the tool knows to look on the user table for the email that matches the one supplied.

So if you are doing this, you might need to specify u_fulllocation in the parent row as the supplied referenced field value. I'm presuming your excel looks something like below

name |                                           parent                       |                       fullllocation

building 1                             |                                           |                     building1

floor2                                           | building1             |                     building1floor2

if what your telling the tool is the unique field or the referenced value field name doesn't EXACTLY Match what it's looking for then it will create a new reocrd (you can choose not to do this by using the CHOICE ACTION field and marking that as REJECT or IGNORE in the related list too, but then you will just end up with blanks parent fields anyway so not much better except you don't get records to have to keep deleting.

One thing I have done in the past to make life easier, is import parents first to make sure they are created, then import the children next so i know nothing should get created as a parent and I can easily see what the parents display value has been set as.

We've all been here before when setting up imports for the first time so don't worry it gets easier

[altacc]

I'm really not getting it, I'm sorry!

Looking at my data there is actually more going on that I thought in that a few seem to actually work! Here's my input data -

And here's how it transforms (using a 2 step process similar to what you describe - importing all discrete locations and then attempting to map parent in a separate transform

As you see, 2 of them do what I want, the rest do not! I am beyond confused now!