This will only work if the first 30 characters of your descriptions are truly unique for every item, you could in theory import the description as the Alias item number and then set the import to only use the first 30 characters of your source column. Again, this would require that you don't have any items where the description from the beginning to up to 30 characters (or the end of the description, whichever comes first) does not repeat.

If you are experienced with scripting, you could use a script or perform logic but this would be an advanced solution and still subject to the same limitations on the descriptions noted above.

*Edited to be clearer.

thanks.  how do you use a script in conjunction with visual integrator?  can you give any advice or point to any docs?

My advice is: don't even try.  The kind of script David is suggesting is not for someone new to Sage scripting.

Trying to use a description is a bad idea anyways.  For anything to work (any method, including scripting) it must be a 100% exact text match, and if you are using a CSV there can be absolutely no commas as data.

If you absolutely must use descriptions, import the descriptions as alias codes, and you are done.  Set VI to your description column, and the alias will translate to the correct ItemCode.

(As David stated, this assumes the first 30 characters of the description are unique).

Something else I have found is VI Imports do not properly handle escaped double quotes in delimited files. For example, if you export from excel to a CSV and a cell contains one double quote, excel escapes the double quote with an additional double quote. This causes the VI import to see two double quotes. Off the top of my head, I cannot remember whether or not VI import properly reads an escaped comma enclosed in double quotes but this is an additional reason the description is not recommended for this since descriptions tend to have commas, single quotes, double quotes, and even carriage returns and line feeds. You can attempt to handle these kind of exceptions either in the Alias item number or script but you have to be cautious and I would suggest testing in a test company copied from your production company to make sure you get the results you are expecting.

No because the description is not a key field.

Thanks for your reply.

Your solution of importing the descriptions as alias codes will work.

I'm very good at scripting so I don't think Sage scripting should be hard to pick up.  Do you have any pointers on where the documentation/video's are?

There is a BOI (Business Object Interface) course on Sage U. This should give you a good foundation if you really want to get into scripting for Sage 100.

Most scripts are using VBScript. Perform Logic is typically done in ProvideX.

I have several posts on here with tips and there is also a document floating around on this forum and i think it still has a KB entry. It contains a lot of good information on basic methods, properties, and object handles.

You can also search on YouTube for some videos related to scripting for Sage 100 and Custom Office within Sage 100. I think Alnoor has a few good ones.

It is my experience that there is no logic in VI for escape characters within a delimited file.  Everything seems to be handled as pure text (except for the chosen delimiter and CRLF).

Just confirmed that a comma enclosed in double quotes is not treated as a delimiter in a VI import job, which is the expected behavior. However, the import does not properly parse two double quotes as i stated earlier. 

A work around to this is to use a calculated column with the SUB function for each field that might have a double quote in it.

https://manual.pvxplus.com/PXPLUS/functions/sub.htm

If you have a bunch of columns that might contain two double quotes or need to use the replace operation then you can use this perform logic on the after read event.

sDelimiter$=","
IF LEN(sColumns$)>0 THEN {
	IF MID(sColumns$,-1,1)<>sDelimiter$ THEN sColumns$=sColumns$+sDelimiter$
	FOR nCounter=1 TO POS(sDelimiter$=sColumns$,1,0)
		nDelimitersFirstPosition=POS(sDelimiter$=sColumns$)
		IF nDelimitersFirstPosition>0 THEN {
			nColumn=0;nColumn=NUM(MID(sColumns$,1,nDelimitersFirstPosition-1),ERR=*PROCEED)
			IF nColumn>0 THEN {
				sColumns$=MID(sColumns$,nDelimitersFirstPosition+1)
				EXECUTE "IMP$[nColumn]=SUB(IMP$[nColumn],"""""""""""","""""""")",ERR=*PROCEED
			}
		}
	NEXT
}

You use it like this. The sDelimiter$ variable in the above code should match the delimiter you use to separate the column source numbers in the sColumns$ variable used in the below perform command.

sColumns$="2,3"; PERFORM "..\CM\Script\RemoveDoubleQuotePairs.pl" ! '