Mapping fields, Defaults & Formulas in FTP Import Map

Written by Leslie T

Last published at: August 25th, 2023

Mapping fields

Once you have created your first FTP Import map, you must then ensure all your fields are mapped correctly. 

 

If you Autogenerated your fields

You should see all field pre-created. You still need to edit them to ensure the Internal Field Name is mapped to the correct column in your file.

To access the mapped fields, click the Options dropdown of the map and click Manage Fields

 

If you did not Autogenerate your fields

You will have to create each field manually

 

Field Options

Field Index

The position of your field column in the file

External Field Label

The label of your field in the file

(This is just for informational purposes and is not functional)

Internal Field Name

The corresponding Samples field

 

Important

Once you have finished the mapping of all your fields, be sure to use the "Validate map" Option to check that all mandatory fields are mapped. 

If any fields are missing, you can create your Default directly from this screen. (Learn more about Defaults below)

 


 


Managing Defaults

Defaults allow you to pre-define data (fixed text or with formulas) that might be missing or incorrect in the source file. 

For missing data — In cases where a field is not provided in the source file, you can define a default value to ensure the field is always populated. 

For fixing data — In cases where the data in the source file might be formatted incorrectly.

How to define a default

  1. Navigate to the Importing Maps page
  2. In the Options dropdown for your map, select Manage Defaults
  3. Click “Create Field Default”
  4. Choose the Internal field for which you want to define a default
  5. Fill in your Default Value
    1. Fill it in as plain text

       
    2. Fill in a formula

       
  6. If defining a formula, select “Interpret as Formula” checkbox.
  7. Click Create

 


Leveraging Formulas

When defining defaults you can write formulas to transform data provided in the source file or generate data from scratch.

Use formulas similar to Excel or Google Sheets to fill in or transform data.

 

List of compatible formulas (list is always growing)

  • CONCAT (text1, [text2], ...)
  • LEFT (text, [num_chars])
  • MID (text, start_num, num_chars)
  • RIGHT (text, [num_chars])
  • TRIM (text)
  • FIND (find_text, within_text, [start_num])
  • REPLACE (old_text, start_num, num_chars, new_text)
  • SUBSTITUTE (text, old_text, new_text, [instance])
  • EXTRACT (text, delimiter, piece_number)
  • LENGTH (text)
  • COLUMN (identifier)
  • SUM (number1, [number2], [number3], ...)
  • PROD (number1, [number2], [number3], ...)
  • NOTEQUAL (value1, value2)
  • EQUAL (value1, value2)
  • IFERROR (value, value_if_error)
  • IF (logical_test, value_if_true, value_if_false)
  • NULL ()
  • NOW()  
    • will populate with the current UNIX timestamp at time of processing
  • UNIQUEID() 
    • Produces a 32 hex character string completely from scratch that is unique. Does not require any input or source data to generate this number
  • MD5()
    • Also produces a 32 hex character string but requires some source data. In this case you might want to concatinate data from other fields that could uniquely describe the product. For instance Color+Fabric+Pattern transformed using this formula would give you a unique number. Note that if you have samples that have the same color+fabric+pattern, in this case, would transform into the same number.
       
 
 

 

How to use formulas

  1. Navigate to the Importing Maps page
  2. In the Options dropdown for your map, select Manage Defaults
  3. Click “Create Field Default”
  4. Choose the Internal field for which you want to define a default
  5. Fill in your formula
    1. Use the following format to populate data from another column
      COLUMN("X")
  6. Check the box that says “Interpret as formula”
  7. Click Create
     

Testing your formula

In the Edit Field Default window, click the Check Documentation link to open up a helper that outlines all of the available formulas and allows you to validate your formula.




 

 

Example uses

Customer does not have any SKUs or Style numbers data available to import but they need them to use the Masterlist or some other functionality.

  • Use CONCAT if we know that their SKU is the combination of a other available fields such as color+size
  • If the CONCAT produces a value that is too long (over 50 characters), use the UNIQUEID() formula to generate a shorter string
  • If the customer does not have any useful data to use as the source data, use MD5() to generate a code from scratch.

 

Another example could be when the customer has their product image files named in a standard convention such as style-color.jpg. Now using FIND, LEFT, RIGHT and/or MID formulas, we can pre-populate the image placeholder so that the user can easily mass upload the images.