How basically transform map will work

[Tamil5]

Hi All,

I have an intermediate table in service now that will be act like a source table when LDAP schedule load runs.

So my source table will be 'imp_location' & the target table is 'cmn_location' (this configuration is done by someone)

When I check the cmn_location table records, everyday update is happening, but 'imp_location' table is empty all the time.

So I though, once the data transform completed these records will be deleted.

Now I need to import my own application data(it has location values) to 'cmn_location'. So I thought of using the same Transform Map.

Created a Datasource Source table. It automatically mapped the Transform Map here.

Created a Scheduled Import with this Data Source & executed.

But data did not move from 'imp_location' to 'cmn_location' table.

'imp_location' did not get cleared & cmn_location table did not get updated with the new values.

Can any one help me with this?

Thanks

Prici

[TrenaFritsche]

Hi Prici,

What is the "import my API result data" data?   Is it a spreadsheet that needs to be imported to a staging table or where is it and what does it consist of?   Can you just put the data into imp_location or is there separate fields not in imp_location that need to be added to cmn_location.

My initial thought is if you get your data (API data) into the imp_location table it will automatically transform into cmn_location.   If you have fields that you populate in imp_location that are not in the existing transform map, you add another transform map specifically for those fields and coalesce on Name.   Not 100% sure if this is what will help.     Let me know if it makes sense.   Without knowing the data coming from your API, its kind of hard to assist.

Thanks,

Trena

[Tamil5]

Hi Trena Fritsche

Ya sure, the data which i'm retrieving from our own application is (Name,Street,City,State,Country. Most of the record contains only Name value).

Though it has 5 fields only Name will have values & rest of the fields will be blank most of the time.

Using the below code, i could get the location details from my application.

sm.setHttpMethod("get");

sm.setEndpoint("https://myapplication.mydomain.com/api/v1/locations/${date_updated_after,active}");

sm.setRequestHeader("Accept", 'application/json');

sm.setRequestHeader("Content-Type", "application/json; charset=UTF-8");

sm.setRequestHeader("key","<<secure key >>");

sm.setQueryParameter("date_updated_after", '2016-04-04');

sm.setQueryParameter("active", 1);

var response = sm.execute();

var responseBody = response.getBody();

var parser = new JSONParser();

var parsed = parser.parse(responseBody);

for (it=0; it < parsed.length ; it++){

  var gr = new GlideRecord('imp_location');

  gr.initialize();

  gr.name = parsed[it].name;

  gr.country = parsed[it].address.country;

  gr.street = parsed[it].address.street;

  gr.city = parsed[it].address.city;

  gr.state = parsed[it].address.state;

  gr.insertWithReferences();

}

this code inserting data into imp_location table. But this data is not transformed to 'cmn_location' table.

As I said earlier, already there is a Transform map for 'imp_location'   with Target table as 'cmn_location'.

imp_location table has a WSDL url. So it seems this imp_location is getting data from some external application & transferring in to 'cmn_location' (I have never seen a single record in imp_location table. So I think this table will be auto cleared once the transform is done)

With the help of API code I could insert the location data. But my issue is data transform is not happening. Upto my understanding this will be transferred automatically. But in my the data staying in Source table.

I tried to manually execute the Transform map but it is saying, Import set not created.

Even if i create import set manually, it is not running.

Hope am not confusing the things.

Please let me know if you need more details to help.

Thanks in advance

Prici

[TrenaFritsche]

Hi Prici,

The Transform map that is OOB and Active is called Location and it runs when data is inserted into imp_location table.   I'm wondering about your "gr.insertWithReferences();" statement.   Can you change it to be:   gr.insert();     The table imp_location is the staging table (import table) and those fields are all String and don't have references, but that may not be an issue...I just haven't used that function.

Have you confirmed that all the data is arriving in the imp_location table?   I would sort your data by updated so that you can tell quickly what was done and when...also you could use your ISET number to track what occurred for each import.   Based on your 1st screen shot above, it looks like your import set ISET0223838 from 6/14 inserted the name from your insert into imp_locations, but if you continue to use the same data, it will no longer insert and it will be ignored because all the data that is coalesced already exists (nothing changed on your 6/15 import from your 6/14 import.     It looks to me like the import works just fine and you don't need to create another transform map to import other data, but you do need to make sure the data being inserted into imp_location is being done.

The locations screen shot that you provided (this is the imp_locations table), when you click on the created date link, you will see the actual data that was inserted into imp_location and then you can click on the Target Record link and it will show you the transformed cmn_locations record.

Let me know if you have any additional questions and I hope this information has been helpful.

Thanks,

Trena

[TrenaFritsche]

Also, I wanted to confirm that your regular location table (cmn_location) list isn't being filtered by an active value is it?   I know I've been burnt by that before...we have an active flag in this table and the list is filtered by Active=true and when I was testing this import, I wasn't seeing the newly transformed rows because the transform map doesn't have the active flag or there isn't a default on this field to always be true when new record is inserted, so all the transformed rows were inactive and didn't show in the filtered list until I removed the active=true.   This may not be your case because I think the filter and active flag is custom to our instance.   I also did want to mention that is this load going to be ongoing because the way the OOB transform map is configured, if you have a record with the same name but the address was changed/fixed in your source system, the coalescing that occurs will insert a new row if one of those fields are different instead of just updating it.   Depending on your requirements, you may want to change the transform map or   make it inactive and create your own.   I would definitely read up on how transform maps work.   I think it will help you going forward.

Thanks,

Trena

[TrenaFritsche]

Hi Prici,

Were you able to get this to work?   Let me know if my assistance was helpful or not and if your question has been answered so it may help others if so.

Thanks,

Trena