Sage 100

InStr({CI_Item.ItemCode}, Chr(10))​

InStr({CI_Item.ItemCode}, Chr(10))​

1. When setting up the VI job, use ODBC as the source, point it to the SOTAMAS90 DSN. 
   1. If you want to save credentials, you do so by clicking the green key icon and enter the user and company separated by a vertical pipe character ( "|" ) and then enter the password.
      
   2. Click Tables, then go to the SQL tab and click Modify and paste the following.

   3. Select 
      CI_Item.ItemCode,
      {fn Replace({fn Replace({fn Replace(CI_Item.ItemCode, {fn Char(13)}, '')}, {fn Char(10)}, '')}, '0', '0')} As NewItemCode
      From
      CI_Item
      Where
      {fn Locate({fn Char(13)}, CI_Item.ItemCode)} > 0 
      Or
      {fn Locate({fn Char(10)}, CI_Item.ItemCode)} > 0 

   4. On the Data tab;
      1. The LineNo should be set to Incr Assign and have a blank default value.
      2. The StartingItemCode and EndingItemCode should be set to Replace and use column 1.
      3. The Operation should be assigned to 2.
      4. The NewItemCode should be set to Replace and use column 2.
      5. The AllFiles should be assigned to Y.
2. Because VI doesn't handle reading inline line feeds or carriage returns well, you could use your crystal report to replace the actual characters with placeholder values that use can replace in the VI job, so in crystal, change line feeds to something like ~LF~ and carriage returns to ~CR~ in one column for the original number and in another column have crystal reports replace both carriage returns and line feeds with blank strings separately (use two cascading Replace functions, each looking for one character instead of one function looking for them combined) just in case you have instances where only one of the two made it into the data.  Then in the import, first start with a temp field on the original item code column, then add the start and ending item code fields using the calculation operation and use the SUB function to replace the placeholder values (~LF~ & ~CR~) with the actual values using the CHR function.  All other fields should be set up as they are in the SQL example above.

   1. SUB(SUB(Temp001$, "~CR", CHR(13)), "~LF~", CHR(10))

InStr({CI_Item.ItemCode}, Chr(10))​

1. When setting up the VI job, use ODBC as the source, point it to the SOTAMAS90 DSN. 
   1. If you want to save credentials, you do so by clicking the green key icon and enter the user and company separated by a vertical pipe character ( "|" ) and then enter the password.
      
   2. Click Tables, then go to the SQL tab and click Modify and paste the following.

   3. Select 
      CI_Item.ItemCode,
      {fn Replace({fn Replace({fn Replace(CI_Item.ItemCode, {fn Char(13)}, '')}, {fn Char(10)}, '')}, '0', '0')} As NewItemCode
      From
      CI_Item
      Where
      {fn Locate({fn Char(13)}, CI_Item.ItemCode)} > 0 
      Or
      {fn Locate({fn Char(10)}, CI_Item.ItemCode)} > 0 

   4. On the Data tab;
      1. The LineNo should be set to Incr Assign and have a blank default value.
      2. The StartingItemCode and EndingItemCode should be set to Replace and use column 1.
      3. The Operation should be assigned to 2.
      4. The NewItemCode should be set to Replace and use column 2.
      5. The AllFiles should be assigned to Y.
2. Because VI doesn't handle reading inline line feeds or carriage returns well, you could use your crystal report to replace the actual characters with placeholder values that use can replace in the VI job, so in crystal, change line feeds to something like ~LF~ and carriage returns to ~CR~ in one column for the original number and in another column have crystal reports replace both carriage returns and line feeds with blank strings separately (use two cascading Replace functions, each looking for one character instead of one function looking for them combined) just in case you have instances where only one of the two made it into the data.  Then in the import, first start with a temp field on the original item code column, then add the start and ending item code fields using the calculation operation and use the SUB function to replace the placeholder values (~LF~ & ~CR~) with the actual values using the CHR function.  All other fields should be set up as they are in the SQL example above.

   1. SUB(SUB(Temp001$, "~CR", CHR(13)), "~LF~", CHR(10))

InStr({CI_Item.ItemCode}, Chr(10))​

1. When setting up the VI job, use ODBC as the source, point it to the SOTAMAS90 DSN. 
   1. If you want to save credentials, you do so by clicking the green key icon and enter the user and company separated by a vertical pipe character ( "|" ) and then enter the password.
      
   2. Click Tables, then go to the SQL tab and click Modify and paste the following.

   3. Select 
      CI_Item.ItemCode,
      {fn Replace({fn Replace({fn Replace(CI_Item.ItemCode, {fn Char(13)}, '')}, {fn Char(10)}, '')}, '0', '0')} As NewItemCode
      From
      CI_Item
      Where
      {fn Locate({fn Char(13)}, CI_Item.ItemCode)} > 0 
      Or
      {fn Locate({fn Char(10)}, CI_Item.ItemCode)} > 0 

   4. On the Data tab;
      1. The LineNo should be set to Incr Assign and have a blank default value.
      2. The StartingItemCode and EndingItemCode should be set to Replace and use column 1.
      3. The Operation should be assigned to 2.
      4. The NewItemCode should be set to Replace and use column 2.
      5. The AllFiles should be assigned to Y.
2. Because VI doesn't handle reading inline line feeds or carriage returns well, you could use your crystal report to replace the actual characters with placeholder values that use can replace in the VI job, so in crystal, change line feeds to something like ~LF~ and carriage returns to ~CR~ in one column for the original number and in another column have crystal reports replace both carriage returns and line feeds with blank strings separately (use two cascading Replace functions, each looking for one character instead of one function looking for them combined) just in case you have instances where only one of the two made it into the data.  Then in the import, first start with a temp field on the original item code column, then add the start and ending item code fields using the calculation operation and use the SUB function to replace the placeholder values (~LF~ & ~CR~) with the actual values using the CHR function.  All other fields should be set up as they are in the SQL example above.

   1. SUB(SUB(Temp001$, "~CR", CHR(13)), "~LF~", CHR(10))

InStr({CI_Item.ItemCode}, Chr(10))​