EdiConfigurator < Cargooffice

Tags: view all tags
---+!! EDI Configurator
---++!! Contents

<br />%TOC{depth="4"}%

---++ Description

For some of the more common EDI formats (CSV, XLS, XLSX and XML) the user is able to tell cargooffice how to parse an EDI file. Below is a description of this program. It can be found here:

*Backoffice -&gt; Shipments -&gt; EDI Configurator*

*A note on spreadsheet files :* If these are Microsoft spreadsheets containing formulas then the results might be unpredictable do to Microsofts proprietary formats of the fomulas. You have to test these files thoroughly before entering production or use an open standard.

---++ Example screen

This is an example of a configuration screen, an explanation follows.

<img alt="example screen" src="%ATTACHURL%/csvXls2.png" />

---++ Enter a new configuration or choose an existing one

Click *add new mapping* if you want to enter a new configuration. The system will ask you for a customer number, each time this customer sends and EDI file, this new configuration is used to import the data.

Alternatively you can choose an existing customer to make changes to it's configuration.

<img alt="add new mapping" src="%ATTACHURL%/csvXlsNewCust.png" />

---++ Mapping of cargooffice fields

There are three sections where you can map cargooffice fields to input data; the *order*, *shipment* and *goods* section. Generally an *order* in cargooffice has one or more *shipments*, and a shipment may have one or more *goods* (lines).

To add a field, choose the relevant cargooffice fieldname from the list and click *Add this field*:

<img alt="add new mapping" src="%ATTACHURL%/csvXlsAddMapping.png" />

Use parameter goodsFieldNames, shipFieldNames or orderFieldNames if you want to add a not listed field.

---+++ A literal value

The *value* column may contain a literal, a fixed value that will allways be loaded into the selected cargooffice field. In the example screen at the top of this page the literal *13160810* is loaded into field *customerID*. And the literal *Imailo* is loaded into field *orderName*.

---+++ column references (csv and xls)

By using @ you can reference columns in a spreadsheet (.xls files) and also columns in a csv file. In latter case (a csv file) !@A means the first column, !@B the second column etc.

<img alt="add new mapping" src="%ATTACHURL%/csvXlsColumnRef.png" />

---+++ field references

The *value* column may contain a reference to a field. The field must be defined earlier in the configuration and its name must be between curly brackets. Example:

<img alt="field reference" src="%ATTACHURL%/csvXlsFieldRef.png" />

*Note:* in this example *orderReference* must be defined earlier in the configuration (upstream).

<b>Note: </b>referencing a field can not be combined with concatenate function

---+++ XML tag references

By using / you can reference tags in an xml file. The reference is an xpath like reference where you need to specify both the group tag name as well as the tag name itself. For example */order/orderEmail* references the tag &lt;orderEmail&gt; within the group tag &lt;order&gt;. There is no need to reference any higher levels in the xml, the system only needs the group tag names for the *order*, *shipment* and *goods* (lines).

For an example XML have a look at: LPtransXml

<img alt="add new mapping" src="%ATTACHURL%/csvXlsXmlRef.png" />

*Note1:* You have to specify any lower levels in xpath definitions. If for example you want to reference &lt;packingType&gt; in the following xml, you will use: /goodsLine/packingDetails/packingType.

<verbatim>
<exampleXml>
  <order>
    ..
    <shipment>
      ..
      <goodsLine>
        <packingDetails>
          <packingType>container</packingType>
          <packingSize>20ft</packingSize>
        </packingDetails>
      </goodsLine>
      ..</verbatim>

*Note2:* The xpath definition always starts with the repeating group tag, in this example /goodsLine/ because this tag repeats itself within a shipment. In the same fashion references to shipment tags begin with /shipment/ and references to order tags begin with /order/. So always the repeating group tag first, and no higher levels.

*Note3:* To help the system finding the order-shipment-goodsLine hierarchy, please enter some fieldNames (tags) from each hierarchy into the params *orderFieldnames*, *shipmentFieldNames* and *goodsFieldnames*. <br /> *WARNING*:If not one tag of the XML can be found in these params then the file might not be processed.

*Note4:* It is possible to use [[RegExp][regular expressions]] in the xpath definitions. This might be useful if you need an xml tag with a specific attribute. If for example you need the vessel name from the xml below, you might use the following definition:

<img alt="add new mapping" src="%ATTACHURL%/csvXlsXmlRef2.png" />

<verbatim>
<exampleXml>
  <order>
    ..
    <shipment>
      <vesselDetails>
        <ident type="vesselType">bulk carrier</ident>
        <ident type="vesselName">Joyride</ident>
        <ident type="vesselLength">130</ident>
      </vesselDetails>
    ..</verbatim>

*Note5:* An easier method of getting an attribute is using the '@' character:

<img alt="add new mapping" src="%ATTACHURL%/csvXlsXmlRef3.png" /> <br /><br />

*Note6:* The system uses a *subset* of the xpath syntax. You can use the "real" xpath syntax if you need more complex definitions. In this case you *must* begin the definition with a double slash (//),<br /> Some examples:

   * //consignment/location[qualifier="OS"]/address
   * //transportInstruction/consignment/../datetime
   * //consignment/location[2]/name

*Remark1:* For more information and examples see: [[https://www.w3schools.com/xml/xpath_intro.asp][this page]]

*Remark2:* You can test your xpath definitions: [[http://login.cargooffice.com/xpathTest.php][here]]

*Remark3:* If your "real" xpath doesn't work, then try to define the complete path, so with all higher levels.

*Trick:* x-paths are only recognised if the first character in the 'value' field is '/'. It is not possible to concatenate an x-path with a leading text like 'Location:& &//consignment/location[qualifier="OS"]/address'.

What you can do to circumvent this is use a non-existing x-path as your first x-path. Like '//consigment/loc&Location:& &//consignment/location[qualifier="OS"]/address'.

You will trick the configurator into recognising the x-path, the first x-path will evaluate to nothing and effectively the result will have your leading text. Neat, huh?

*Remark4:* You can use ../ ("real" xpath only!) to go to a higher level of your xml file BUT you can only go as high as the highest level you have used in the configuration. For example:

<verbatim><exampleXml>
  <order>
    <orderReference>123</reference>
    <shipment>
      <vesselDetails>
        <ident type="vesselType">bulk carrier</ident>
        <ident type="vesselName">Joyride</ident>
        <ident type="vesselLength">130</ident>
      </vesselDetails>
    </shipment>
    <shipment>
      ....
    <shipment></verbatim>

<br />if you want to reach the orderReference, you could use //vesselDetails/../../orderReference but you can only do so if your starting point in the configuration was //order or higher.<br /><br />

*Warning:* Using the "real" xpath syntax might be slower than using the default subset.

---+++ Partial value: substring

Notation: @{column}:substr(int $start [, int $length ])

Substr takes a specific part of a value, defined by the *start* en *length* parameters. If *start* is positive, the resulting value is the part starting from this position to the end of the value, counting from zero. For example in string 'abcdef', the character at position 0 is 'a' and the character at position 2 is 'c', etc.

*Substr examples*:

Where column !@A has a value of '1234':

   * !@A:substr(1) results in `234`
   * !@A:substr(1, 2) results in `23`
   * !@A:substr(0, 4) results in `1234`
If *start* is negative, the result will start at the position counting from the end of the value, for example:

Where column !@A has a value of '1234':

   * !@A:substr(-1) results in `4`
   * !@A:substr(-2) results in `34`
   * !@A:substr(-3, 1) results in `2`

---+++ Concatenate values

By using & multiple references can be concatenated into one cargooffice field. Like these examples:

<img alt="add new mapping" src="%ATTACHURL%/csvXlsConcat.png" />

*customerID* : If column B has the value `1234` then the resulting value in field customerID will be `customer1234`

*orderReference* : If column I has a value 'ref' and J 'abc' then the resulting value in field orderRefenece will be 'ref abc'.

---+++ Split values

Notation: @{column}:split(string $separation, int $position)

This function splits a value into 1 or more parts where parameter *$separation* defines the character or string on which the value is split and parameter *$position* defines which part should be taken.

*Examples*:

Where column !@A has a value of `10:00  12:00`:

   * !@A:split("", 0) results in `10:00`
   * !@A:split("", 1) results in `12:00`

---+++ Dates

In general dates must be defined in the format YYYY-MM-DD, or (in spreadsheets) defined as a date formatted column.

The special function *today* takes the current date plus a number of days. Example:

| <img alt="" src="%ATTACHURL%/csvXlsToday.png" /> |
Normally *workdays* are standard, skipping weekends, as if you wrote today+1workday .<br /> If you don't want workdays and include weekends then add the text days to the number. Example:

| <img alt="" src="%ATTACHURL%/csvXlsToday5.png" /> |
Alternatively you can also use *pickupDate+n*. In this case the pick-up date is used plus an optional number of days. For example:

| <img alt="" src="%ATTACHURL%/csvXlsToday2.png" /> |
*Please note:*
   * in this case *pickupDate* must be defined first before you can refer to it.

Also (less common) you can use *deliveryDate-n*. In this case the deliveryDate is used minus an optional number of days. For example:

| <img alt="" src="%ATTACHURL%/csvXlsToday3.png" /> |
If your input date has another format you can use the *Translate* column to reformat it.<br />In the example below we use the *preg_replace* function to translate a dd-mm-yyyy formatted date into yyyy-mm-dd:

| <img alt="" src="%ATTACHURL%/csvXlsToday4.png" /> |
Also see the description about the *Translate column* below.

*Please note:*
   * weekends are normally skipped. So if today+1 is a Saturday or a Sunday, Monday will be used (except when you write 'today+1day' like example 2 above, then weekends are skipped).
   * this also applies to the deliveryDate-1 option, when deliveryDate is a Monday, deliveryDate-1 will be the Friday before.
   * just 'today' will not work; you have to use 'today+0' when referring to today or any other date.
   * only fields with a fieldname ending with 'Date' can be used to store values using the today+n, pickupDate+n or deliveryDate-n options. Other fields that you use this option on will show the actual text 'today+n' in them.
   * 'Zero' date is Unix Epoch or 01-01-1970. Should you run into problems and receive date values in 1969 or 1970, you are probably using a base date value of 0.

---+++ Simple Arithmetic

You can do simple arithmetic by starting a column reference with a plus (+) sign. Example:

| <img alt="" src="%ATTACHURL%/csvXlsArith1.png" /> |
Here three columns are multiplied by each other (i.e lenght, width and height) to get the volume.

*Examples*:

   * +@L/100*@W*@H
   * +@L*(@W + 2)*@H
   * +round(@L * @W * @H,2)

*NOTE:* You cannot combine arithmetic with xml paths. There is a way around this. The xml is converted to a csv which can be viewed when you use the 'test' button on the bottom right of the configurator screen. You can then use the columns of this csv for calculations. If you place new variables/fields *above* this column reference, you will need to check and re-define the column reference.

This may look like this:

<img alt="xml-csv-arithmetic.png" src="%ATTACHURLPATH%/xml-csv-arithmetic.png" width="100%" />
---+++ Translate column

The translate column is used to translate values into other values or for some special functions (described below).<br /> Translate values are separated by commas and the original value (the first value) is separated by the target value by =. Example:

| <img alt="" src="%ATTACHURL%/csvXlsTranslate.png" /> |
In the example above various delivery terms are translated into numeric values.

*NOTE:* You can add a *default value* by using an asterisk character

<literal>(*)</literal>

. For example **=000*, will translate unknown values into 000.

| <img alt="" src="%ATTACHURL%/csvXlsTranslate2.png" /> |
*Literals:* By using double quotes you determine that the matching value must be an exact *literal*. In other words, the whole value must match (without double quotes BEER would be translated into !BelgieER).<br /> A literal match is case insensitive.

| <img alt="" src="%ATTACHURL%/csvXlsTranslate3.png" /> |
*Regular expression:* By using forward slashes you determine that the matching value is a [[RegExp][regular expression]]. In this case any value matching the expression is translated. Please note that the comparison is always *case insensitive*.<br /> To match an *empty value* you can use /^$/=... (example: to translate an empty value for quantity to 1, use: /^$/=1).

*note:* The examples above can be combined. So any combination of translations with quotes, without quotes and regular expressions may me used in the same translation line.

---+++ Multi column definitions

Sometimes a CSV or XLS file contains multiple columns for quantity.<br /> Example:

| A | B | C | D | E | F | G |
| *orderID* | *From* | *To* | *Pallets* | *Boxes* | *Colli* | *Crates* |
| 12345 | London | Paris | | 2 | | |
| 12346 | Brimingham | Southampton | | | | 4 |
| 12347 | Glasgow | Amsterdam | 1 | 3 | | |
For order 12345 one goods line is requested for 2 boxes.<br /> For order 12346 one goods line is requested for 4 crates.<br /> For order 12347 *two* goods lines are requested, one goods line with 1 pallet and another goods line with 3 boxes.

To define this you can use the -or- character | (a vertical bar) between the column definitions.<br /> Example:

| <img alt="" src="%ATTACHURL%/csvXlsMulti.png" /> |
Also look at the second line for *packing*, here the value is translated into a text.<br /> The question mark means: if there is a value in this column then translate it.

*NOTE* If the packing name is in a column you can also use a column definition here. For example:<br /> *@D?=@H,@E?=@I,@F?=@J,@G?=@K*

---+++ Function: param:

The function *param:* retrieves the translate values from a parameter. Example:

| <img alt="" src="%ATTACHURL%/csvXlsTranslate4.png" /> |
The parameter must be defined in the *orderEntry* parameters.<br /> Example:

| <img alt="" src="%ATTACHURL%/csvXlsTranslate5.png" /> |
*notes:*
   * The parameter may also contain any of the functions described below, although it is intended for large code- or translation tables.
   * If the from value is between double quotes (") the matching value is a literal, meaning that for instance "BE" will not match with BELGIUM, but only with BE.
   * If the from value is *not* between quotes a partial replacement may take place. For instance in the example above BEER would be translated into BELGIUMER.
   * Matching of a literal (between double quotes) is case insensitive, in all other cases it is case-sensitive.
   * If the from value is between forward slashes (/) the matching value is a *regular expression*, meaning that for instance /B.*GIUM/ will match with BELGIUM. Reglular expressions are always case insensitive here.
   * See also the paragraph on *Translate column* above.

---+++ Function: preg_match

The *preg_match* function is an alternative method to take specific part from a value. It is meant for more experienced users, for example programmers who know what [[http://en.wikipedia.org/wiki/Regular_expression][regular expressions are, more specific ]] [[http://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions][perl compatible regular expressions]] [[http://en.wikipedia.org/wiki/Regular_expression][. ]]

*preg_match* matches the value with a certain regular expression and picks out the part defined between parentheses.

The *preg_match* must be entered into the *translate* column, for example:

| <img alt="" src="%ATTACHURL%/csvXlsPregMatch.png" /> |
This regular expression matches an address that looks like a street name together with a house number. It filters out the house number and only takes the street name, the part between parentheses (.*).

---+++ Function: preg_replace

The *preg_replace* function is an alternative method to do translations. It is meant for more experienced users, for example programmers who know what [[http://en.wikipedia.org/wiki/Regular_expression][regular expressions are, more specific ]] [[http://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions][perl compatible regular expressions]] [[http://en.wikipedia.org/wiki/Regular_expression][. ]]

*preg_replace* matches parts of a value with a certain regular expression and replaces those parts by a second value.

The *preg_replace* must be entered into the *translate* column, for example:

| <img alt="" src="%ATTACHURL%/csvXlsPregReplace.png" /> |
In this example the string ',000' or the character '.' (a dot) is replaced by nothing (since the second value in this example, the value between //, is blank).
---++++ Backreferencing

preg_replace/(\d{2})(\d{2})(\d{2})(\d{2})/$1-$2-$4/

This function will translate 10102012 into 10-10-12 using backreferences.
---+++ Function: toFloat

The function *toFloat* translates a string into a [[http://en.wikipedia.org/wiki/Floating_point][floating point number]].

Example:

| <img alt="" src="%ATTACHURL%/csvXlsToFloat.png" /> |

---+++ Function: min

The function *min* takes a minimum value. If the column value is less than the given min value then the min value is taken.

Example:

| <img alt="" src="%ATTACHURL%/csvXlsmin.png" /> |
If in this example the value of column AC is less than 1 then 1 is filled in into field palletPlaces.

---+++ Function: ceil

The function *ceil* rounds fractions up to the next whole (integer) number.

Example:

| <img alt="" src="%ATTACHURL%/csvXlsceil.png" /> |
If in this example the value of column AC is 31.2 then 32 is filled in into field quantity.

---+++ Function: condition

The function *condition* compares two values and depending on the result takes either the true-value or the false-value.

Example:

| <img alt="" src="%ATTACHURL%/csvXlscondition.png" /> |
If in this example the value of column G greater than 0 then "pallet" is filled in into field packing. If not then "colli" is filled in into field packing.

The column that should be evaluated must be in the 'value' (waarde) field, in this example : @G.

Other examples:
   * *condition(!@G &gt; 0)? !@G : !@H* if column G is greater than zero then the value of column G is taken, else the value of column H.
   * *condition(!@F &gt;= 12:00)? "afternoon" : "morning"* if column F is greater or equal to 12:00 then "afternoon" is taken, else "morning".
   * *condition(!@F &lt;= 10 )? !@A".00kg" : !@B"m3"* if column F is less or equal to 10 then the value of column A is taken plus an extra string ".00kg", else the value of column B is taken plus an extra string "m3".
   * *condition(!@F == "text string")? "true":"false"* if the operand that is tested upon is an alphanumeric, the value tested for must also be enclosed in double quotes!!! DO NOT USE SINGLE QUOTES!
   * *condition(!@F == "text string")? !@X : "or else"* a combination of litteral text and column references can be made. Texts go inside double quotes, references do not.
*NOTE* : please beware that comparing against an empty column may cause unexpected results.

*NOTE* : Instead of a column definition (e.g. !@A) it is also possible to use a field name, like: *{customerID}*. The field name must be between curly brackets and defined earlier in the configuration. Example:
   * *condition({pallets} &gt; 0)? {pallets} : {colli}* if the content of field pallets is greater than zero then the value of pallets is taken, otherwise the value of field colli. Please note that both fields -pallet and colli- must be defined earlier in the configuration (must already have a value).
*NOTE on regular expression* when using a tilde (~) a [[RegExp][regular expression]] is performed on the first operand. The regular expression must be between slashes (//). Example:
   * *condition(!@F ~ /KG|Kilo/ )? "kilograms" : "tons"* if somewhere in column F the text *KG* or *Kilo* is found then the string "kilograms" is taken, else the string "tons".
These methods can be combined like this
   * *condition({orderReference} ~ /^(2|7|8)/)? "2 (Import)" : "1 (Export)"* if field orderReference starts with 2, 7 or 8, the string "2 (Import)" is used, else the string "1 (Export)".
   * *condition({pickupTimeReqStart} ~ /./)? {pickupTimeReqStart} : "00:00"* if field pickupTimeReqStart holds a (any) value, field pickupTimeReqStart is used, else string "00:00". BEWARE that time values (with : in them) can only be used AFTER the separation mark.
Possible comparison operators:

   * <strong>==</strong> Equal
   * *!=* Not equal (text)
   * *&lt;&gt;* Not equal (number)
   * *&lt;* Less than
   * *&gt;* Greater than
   * *&lt;=* Less than or equal to
   * *&gt;=* Greater than or equal to
   * *~* Regular expression

---+++ Special function

Sometimes our programmers create special translation functions for specific clients.<br />These functions can be called by their name followed by an optional number of parameters.<br />The programmer will tell you the exact name of the function.

Example:

| <img alt="" src="%ATTACHURL%/csvXlsCalcDeliveryDate.png" /> |
If in this example a specially created function *calcDeliveryDate()* is called together with the values of a number of columns.<br /> The value in column *Value* must be a column definition and acts as a default value in this case.

*NOTE1* : In stead of a column definition (e.g. !@A) it is also possible to use a field name here, like: *{customerID}*. The field name must be between curly brackets and defined earlier in the configuration. See also the note on field names in the description above at Function: condition.

*NOTE2* : In stead of a column definition (e.g. !@A) it is also possible to use a literal, like : *Kilo* or *69*. Any literals should not be defined between quotes.

Please contact our development department if you need special functions.

---+++ Filter trick

Depending on the file set-up there is an option to filter certain record-lines out of your file.
   * First you create a [[EdiConfigurator#Function_condition][condition]], that creates empty values.<br />See example below: condition(!@M=="FR")? "FR" : ""
   * Then you activate the <b>Req.?</b>-box, defining that only record lines with non-empty values will be parsed.
   * <b>R</b><b>esult </b>of this example: only recorld lines from your CSV/XLS file, where FR is set in column M, will be parsed.
| <img alt="filterTrick.png" height="49" src="%ATTACHURL%/filterTrick.png" title="filterTrick.png" width="778" /> |
---++ Special field "orderID"

Special field *orderID* needs some extra explanation.

   1 If there is no orderID in the input file then cargooffice generates a unique orderID.
   1 If there is an orderID in the input file and the order already exists, then that order is *updated* with the data from the input file. Otherwise a new order with that ID is created.
   1 In stead of *orderID* you can also use *shipmentID*. Both field names are synonyms.
   1 In stead of *orderID* you can also use *trackingNbr*. This also uniquely identifies each individual shipment. The format is {CN}.{shipmentID}. (CN = carrier number, a unique number for each carrier using cargooffice).

---++ General settings

| <img alt="" src="%ATTACHURL%/csvXlsGeneralSettings.png" /> |
The general settings can be found below the field mappings.

   * *Use a specific sheet* (.xls files). When using an Excel (.xls) and the data is not on the first sheet, then select Yes and enter the sheet number where the data is.

   * *New shipment column(s)* (.csv and .xls files). Multiple lines in the same input file may belong to the same shipment. Generally to add goods lines to the same shipment. If this column changes then a new shipment is created. For instance a customer reference number, lines with the same customer reference number belong to the same shipment. If you leave this field empty, every line will be written into a new shipment.
      * Note: lines with the same data (name, address, postcode, city, country) are normally combined into one shipment with multiple goodslines. This setting may be used to *split* these goodsLines into multiple shipments too.
      * Note: it is possible to combine multiple columns for this function like this: @B&@O
      * Note: cannot be used when converting XML files!

   * *New order column(s)* (.csv and .xls files). An order may consist of multiple shipments. Since each shipment is on a separate line there must be a reference column to tell the program which lines belong to the same order. Mostly an *order reference number*. Cargooffice will use the *orderID* as a basis for each shipmentID and expands it with a sequence number .00, .01, .02 etc.
   * If you need the whole file to be put in one order but there is no suitable reference column, you can put a non-existing column in this field. The value of this column will (as it does not exist) always evaluate to the same and therefore all shipments in the file will be combined into one order.
      * Note: it is possible to combine multiple columns for this function like this: @B&@O
      * Note: cannot be used when converting XML files!

   * *Data starts at row* (.csv and .xls files). Most input files contain one or more header lines indicating what values are in which columns. The actual data starts in row 2 or later. The row number where the data starts must be entered here.

   * *Totalize goods?* (all files). Enter 'Yes' if there is no total quantity in the order. The individual quantities of the goods lines are totalized into a total quantity field. In fact, all numeric fields are totalized; number of pallets, volume, weight etc.

   * *Status to use* (all files). For EDI shipments we advise to use status '<strong>stdedibulk</strong>'. This automatically generates an output file for the carrier's own TMS system. If a different status is required, or an output file for the TMS system is not required, a different initial status can be chosen here. A frequently used status here is 'batchentry', meaning that the orders are added to a batch which can be send later. <br />If you do not want an automatically generated output or not status 'batchentry', then we advise to choose one of our other [[StatuscodesDefault][default statuses]].
      * Note: it is also possible to set the 'status' field in the field mappings, if the status needs to depend on a value in the incoming file. This is useful when creating/updating a shipment where you may want to set 'stdedibulk' for new shipments and 'orderchange' for updates. If you map the status field in the field mappings, leave the 'Status to use' field empty.

   * *Calculate volume based on lxwxh* (all files). Select *Yes* if you want the volume calculated using the formula length/100 * width/100 * height/100.<br /> *WARNING* Select *No* if you use your own formula like the example under [[#Simple_Arithmetic][Simple Arithmetic]].

   * *Calculate palletplaces* (all files). Select *Yes* if you want the number of pallet places calculated. The program will ask you for the column containing the pallet type and the quantity. The program uses param *palletsizes* to find the factor by which quantity is multiplied. The format is palletType=factor|description (example below).<br /><br /> _Example content for param 'palletsizes'_ :<br />EU=1|europallet<br />BL=1.25|blockpallet<br />CHEP=1.25|block-cheppallet<br />EC=1|euro-cheppallet

   * *Gather goodslines by packing* (all files). Select *Yes* if you want the number of goods lines with the same packing ID to be summed together into one goods line. If for instance there are two goods lines with packing 'pallet' and three goods lines with packing 'box' then just two goods lines will remain, one for 'pallets' and one for 'box'.

   * *Update on key field(s)* (all files). If you want to update an already existing order then specify here the key fields to find the order. If you specify more than one field then separate them by a plus (+) character. For example: *orderReference+pickupDate* will try to find an already existing order with the same orderReference and pickupDate of the current EDI record. If it exists then it is updated. If it does not exist then a new order is created, as is the case when you leave this setting blank.<br /><br /><b>NOTE1: </b>field *orderReference* is always used to update a shipment, so in the example above entering only *pickupDate* would give the same result as entering *orderReference+pickupDate*. However, if orderReference is the only field you wish to update on then you have to enter *orderReference* here to activate the update option.<br /><br /><b>NOTE2:</b> please be aware that using fieldname *refShipmentID* here could give unpredictable results. We use that fieldname as an index into the database table. Also be careful not to use fieldname *containerNbr* anywhere in the configuration, this also could give unpredictable results.

   * *Replace goodslines on update* (all files). If you want to update an already existing order and there is a possibility that the new file contains less goods lines then select *Yes*. Otherwise old goods lines will remain in the order and you end up with too many goods lines.

   * *Expand on key field* (all files). If you want to expand an already existing order with more shipments then specify the key field to find the already existing order. The order number will get an extension like .00 while the extra added shipments receive extensions like .01, .02, .03 etc.. Please note that the field name you specify here must have more than 5 characters.

-- Main.HenkRoelofs - 2012-02-24

   * xml-csv-arithmetic.png:
Attachments
Topic attachments
I	Attachment	History	Action	Size	Date	Who	Comment
png	csvXls.png	r1	manage	39.9 K	2012-02-24 - 13:13	UnknownUser	Example screen
png	csvXls2.png	r1	manage	35.3 K	2017-12-12 - 11:07	UnknownUser	Example screen
png	csvXlsAddMapping.png	r1	manage	4.4 K	2012-02-24 - 13:56	UnknownUser	add thsi field
png	csvXlsArith1.png	r1	manage	1.1 K	2013-01-14 - 15:56	UnknownUser	artimetic
png	csvXlsCalcDeliveryDate.png	r1	manage	2.5 K	2017-12-22 - 13:20	UnknownUser
png	csvXlsColumnRef.png	r1	manage	0.9 K	2012-02-24 - 17:08	UnknownUser	xls column reference
png	csvXlsConcat.png	r1	manage	2.2 K	2012-02-27 - 16:50	UnknownUser	concatenate
png	csvXlsFieldRef.png	r1	manage	1.2 K	2019-04-02 - 13:55	UnknownUser	field reference
png	csvXlsGeneralSettings.png	r9 r8 r7 r6 r5	manage	17.4 K	2020-03-12 - 12:42	UnknownUser	general settings
png	csvXlsMulti.png	r1	manage	2.2 K	2017-11-14 - 10:21	UnknownUser	multi column definition
png	csvXlsNewCust.png	r2 r1	manage	3.1 K	2012-02-24 - 13:40	UnknownUser	newmapping
png	csvXlsPregMatch.png	r1	manage	2.1 K	2012-02-27 - 17:38	UnknownUser	preg_match
png	csvXlsPregReplace.png	r1	manage	1.5 K	2012-02-27 - 17:51	UnknownUser	preg_replace
png	csvXlsToFloat.png	r1	manage	1.5 K	2012-02-28 - 16:52	UnknownUser	toFloat
png	csvXlsToday.png	r2 r1	manage	1.8 K	2015-03-27 - 16:06	UnknownUser	today function
png	csvXlsToday2.png	r2 r1	manage	1.9 K	2015-03-27 - 16:10	UnknownUser
png	csvXlsToday3.png	r2 r1	manage	1.9 K	2015-03-27 - 16:11	UnknownUser
png	csvXlsToday4.png	r1	manage	2.2 K	2015-03-27 - 16:25	UnknownUser
png	csvXlsToday5.png	r1	manage	4.2 K	2016-03-02 - 13:08	UnknownUser
png	csvXlsTranslate.png	r1	manage	1.9 K	2012-02-27 - 18:29	UnknownUser	translate column
png	csvXlsTranslate2.png	r1	manage	5.7 K	2012-06-04 - 09:10	RutgerRutgers	translateQuotes
png	csvXlsTranslate3.png	r1	manage	3.5 K	2014-01-31 - 16:49	UnknownUser
png	csvXlsTranslate4.png	r3 r2 r1	manage	2.5 K	2014-12-19 - 13:37	UnknownUser
png	csvXlsTranslate5.png	r1	manage	12.8 K	2014-12-19 - 13:46	UnknownUser
png	csvXlsXmlRef.png	r1	manage	0.9 K	2012-02-24 - 19:06	UnknownUser	xml tag reference
png	csvXlsXmlRef2.png	r1	manage	1.1 K	2014-04-04 - 12:25	UnknownUser	xml tag + regex
png	csvXlsXmlRef3.png	r1	manage	4.0 K	2014-11-19 - 16:11	UnknownUser	xml attribute
png	csvXlsceil.png	r1	manage	1.0 K	2012-02-28 - 17:00	UnknownUser	ceil function
png	csvXlscondition.png	r1	manage	1.2 K	2012-02-28 - 17:12	UnknownUser	condition function
png	csvXlsmin.png	r1	manage	1.3 K	2012-02-28 - 16:56	UnknownUser	min function
png	xml-csv-arithmetic.png	r1	manage	40.3 K	2020-07-07 - 17:42	WiljoVanLenthe
Topic revision: r74 - 2022-03-16 - HenkRoelofs