Help with transforming fields

shane_davis · ‎09-15-2014

I have a data source and transform map which automatically creates assets from an email with an XLS attachment. The asset creation is working by adding assets to the alm_hardware table. The columns on the spreadsheet are filling in the serial_number, po_number, u_order_number and invoice_number.

My issue is that I need to fill in the following Target fields and I cannot figure out how.

Source

Target

u_chassis_description

manufacturer

u_order_date (mm/dd/yyyy format)

purchase_date (yyyy-mm-dd format and Dictionary info is alm_hardware.purchase_date)

Shawn Dowler · ‎10-01-2014

This could be not as bad as I thought. It looks like each one is always the same, just not exactly what you have in ServiceNow. Try something like this:

// Set the model (must be a String or will not match the cases)


switch (source.u_chassis_description.toString()) {


case 'Opti 7010 (USFF)':


       answer = 'Dell Inc. OptiPlex 7010';


       break;


case 'Opti 9020 (USFF)':


       answer = 'Dell Inc. OptiPlex 9020';


       break;


case 'Latitude E5540':


       answer = 'Dell Inc. Latitude E5540';


       break;


case 'Latitude E7440':


       answer = 'Dell Inc. Latitude E7440';


       break;


default:


       answer = '';


}

Let me know if that worked and if you find it working for some but not all of your data. If you have more things that need to match up, you can add more case, answer, break statements above the default section.

View solution in original post

shane_davis · ‎09-30-2014

Below is the only item I have left for everything to be working. Is there anyone who can help provide information on how I would accomplish this? I've tried several things and nothing is working...

The XLS spreadsheet I am importing has a computer model like Opti 7010 (USFF). The source column is u_chassis_description (shown in Source/Target table above).

My goal is to take the model from the spreadsheet and match it to a model in the Model field on the alm_hardware table (shown above).

My issue is that I have the mapping, normalization, and transformation setup like below, but nothing is being put in the Model field.

Shawn Dowler · ‎09-30-2014

The model field is a reference field, so you have to either give it the exact display value of an existing model or the sys_id of an existing model. The code I posted above shows how I checked to see if the Department I wanted to associate to the User already existed and if it didn't exist, I added it to the Department table, then put the value in the Department field on the User record. If it already exists, then I just put the value in and move on. You could do something similar with your Dell Asset Creation Data Source table.

Your other alternative is to make sure the model you are trying to put into the Model field exists in the Dell Asset Creation Data Source table and that the display values match exactly.

shane_davis · ‎09-30-2014

Shawn,

Thank you for your reply. You are right; it is a reference field. In my case, however, I never know what model of computer(s) the spreadsheet is going to contain so I need something like field normalization to transform from the spreadsheet data to what the model field has.

Shawn Dowler · ‎09-30-2014

You could always try to script things to look for certain strings and try to match them up, but honestly, if you have no control over the quality of the data in your export, you can't really use it to import. You could do it for predictable things like telephone numbers or dates, but unless you only have like 5 models, then it's going to be darn near impossible to script out normalization functions for every model you have.

How is the data being entered on the source that you're exporting from. You might have better luck going back up that way and trying to get some standards in place on that system first. Good luck!

shane_davis · ‎09-30-2014

I probably don't have more than 5 models for this import. My issue is that I have no idea how to script it out. That is why I tried the field normalization and transformation, but that didn't work either.