Help with transforming fields

[shane_davis]

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]

If you could provide an example of the different ways you might expect one of them to be referred to and the actual name of the model in the table, then I might be able to help you put something together that would look for certain things and assign the right one accordingly. What would be great for each of these models to have an ID or something that's unique that we could use to figure out which one to assign in ServiceNow, but that sounds like a long shot as well.

Could you provide a list of all the model numbers that you expect to be used as input that all should be the same in ServiceNow?

[shane_davis]

Shawn,

        I really appreciate your help.   If you need more information than below, please let me know.

Below is how the models will show on the spreadsheet:

Opti 7010 (USFF)

Opti 9020 (USFF)

Latitude E5540

Latitude E7440

Below are the actual names in the ServiceNow field:

Dell Inc. OptiPlex 7010

Dell Inc. OptiPlex 9020

Dell Inc. Latitude E5540

Dell Inc. Latitude E7440

[Shawn Dowler · Accepted solution]

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.

[shane_davis]

Shawn,

        You are awesome!   That worked perfectly.   The spreadsheet I tested with has only the Opti 7010 (USFF) and it worked by putting the model Dell Inc. OptiPlex 7010 into the ServiceNow model field.   It also populated the Manufacturer and Display name as these fields pull content from each other.   I'm getting a spreadsheet with all models and testing those shortly.

      Your help is very much appreciated.   Thank you!

Shane

[Shawn Dowler]

I'm glad it worked for you. Feel free to reach out to me if you run into any more snags.