Strange Transform Map/Script Phenomenon

[nathan34]

Running Helsinki.   Trying to see if anything I say below is a known or possible unknown issue.

Imported data from csv's into a Data Sources then did the field mappings to the Incident and Change tables.

First issue, I mapped one field of text to another text field and during transform some of the target fields were not being populated.   Now, one might say this could be bad data, however, when I even clear out the Import Set table and reimport and transform, it is not the same records that are not being populated properly.   And the kicker is that the u_ import tables are being populated properly from the loading of the data, so not sure as to why the this transform is not working properly.   Was wondering if there is a possible bug afoot here.

The second issue came about from trying to correct these fields that were not being populated.   In a Transform Script, I have these two snippets of code that don't seem to work 100% of the time.

// need to append a time to a date that doesn't have a time, which will be the result of a transform in order to make the field in GlideDateTime format
// so just for explaining purposes, 01/04/2016 2:50 PM, gets transformed to GlideDateTime knowing that that format is MM/dd/yyyy hh:mm a
if (source.u_time_field.toString().length == 10) {
         source.u_time_field += " 12:00 AM";
}

That script works for about 90% of records and the rest I have just been manually editing, but that can be troublesome for when I have to work with a lot more data.

The second script is essential the same experience

// just checking to see if any of these text fields are populated
if (source.u_text_field_1 || source.u_text_field_2 || source.u_text_field_3) {
         target.u_is_text_field_populated = true;
}

This doesn't seem to set that field to true every time it should.   Again, I am comparing to the Import Set table and one of those three fields is indeed populated.

So, the work around I've implemented is to just run another script afterwards in Xplore to modify my Incident and Change records how they should be, but I experience yet again another strange phenomenon.

// going to get all my desired incidents and compare their values to the u_ import set table
// if they are not the same then I'm going to set those values this way since the transform didn't work
var incident = new GlideRecord('incident');
incident.addQuery('sys_domain', "sys_id_of_my_desired_domain");
incident.query();
var counter = 0;
while (incident.next()) {
         // I've modified this set of data to a different number scheme that doesn't contain INC
         // this is how I know the data is from the import
         if (incident.number.indexOf('INC') < 0) {
                   // only need to look at the records that unsuccessfully set u_is_text_field_populated to true
                   if (!incident.u_is_text_field_populated) {
                             // find this record in the import table
                             var importTable = new GlideRecord('u_my_import_table');
                             importTable.addQuery('u_ticket_number', incident.number);
                             importTable.query();
                             importTable.next();
                             // here I am doing the exact same thing as I was trying to do in the transform script
                             if (importTable.u_text_field_1 || importTable.u_text_field_2 || importTable.u_text_field_3) {
                                       counter++;
                                       incident.u_is_text_field_populated = true;
                                       incident.u_text_field_1 = importTable.u_text_field_1;
                                       incident.u_text_field_2 = importTable.u_text_field_2;
                                       incident.u_text_field_3 = importTable.u_text_field_3;
                                       incident.update();
                             }
                   }
         }
}
gs.info('count: " + count); // letting myself know how many records I modified that did not get modified by the transform script

So, if I were to run this exact same script a second time, one would think that the count would be 0 since they all just got modified, but no this is not the case.   What I've realized is that every time I run this script, only half of the records get updated so I just keep running this script several time until I get a count of 0 then I know I'm good.   This should not be happening.   I should be getting the same result every time, which is supposed to be a count of 0.

So there are three things here that have occurred to me that I cannot explain in regards to transform mapping, transforming scripting, and even general Glide scripts.   I hope everything I've stated isn't too hard to follow.   Let me know if I need to elaborate more, but would really like to know if anyone can explain why this is happening, if this has happened to you, or you think this is a possible new bug.   Thanks.

[She Sull]

Hi Nathan, it is hard to say since I can't see your mappings and the tables, but from what you have provided (and if I understand the issues) I would check the following:

1st issue) Is the field length on the target table big enough to fit all of the data? We've had similar issues where the field length just need to be longer and after increasing it that resolved it for us.   Are you getting any errors in the import log that may reveal what the issue is? Does the data look okay if you use the Mapping Assist > Show Mapped Fields?

2nd issue) Is the field on the target table a date/time field or a date field? You'll want to ensure it is a date/time field if you are tying to append a time to the date. Also, are you doing this calculation on a Field Map script or the Run script on the Transform Map? This should probably do this on a Field Map script if that's not where you have it. Another thing you could try if the field is a date/time field and you only have the date on the file, for that Field Map do not use a script, but ensure the Date format is MM-dd-yyyy hh:mm:ss (or the appropriate format).

Hope that helps!

[nathan34]

Everything you mentioned I seem to be following already.

• Field length is good (4000)
• Zero errors in the import log
• Data looks good using Mapping Assist > Show Mapped Fields
• Target field is a date/time field
• Calculation is being done on a Field Map script
• The only reason I've had to result to using a script to adjust the time is because the Date format mapping works inconsistently (in this case anyway) so it's just there to pick up the pieces, but even then I still have some records that are not being transformed so I've ended up fixing by hand (but this can become improbably with more data).   FYI, this is just on the Change table.   The same Field Mapping and Transform Scripts work fine on the Incident table.

The thing that gets me the most is how that last script will only update the queried records by half each time, so I have to keep running it until my count becomes zero, which I then know they've all been updated.   It's puzzling to me.   This specific import process I will be doing for months to come so if along the way I find any clues or culprits, I'll be sure to report it.

[She Sull]

Another thing that has helped me in the past is ensuring the Number format of that date column on your file is the same for each row. You may need to change the format on your source. Good luck, let us know if you get it solved.

Thanks,

Sherry

[andymcdonald]

Hi Nathan - I would try the following:

change your first if statement as below:

if (source.u_time_field.toString().trim().length)

if (source.u_text_field_1 || source.u_text_field_2 || source.u_text_field_3) {

could be refactored as the following:

if (source.u_text_field.toString() || source.u_text_field_2.toString() || source.u_text_field_3.toString())

Also, in the other snippet, your query can be changed as follows:

incident.addQuery('sys_domain', .....);

incident.addQuery('number', 'DOES NOT CONTAIN', 'INC');

incident.addQuery('u_is_text_field_populated', false);

this eliminates logic in lines 10 and 12 below.