According to your source in the screenshot you attached, column 1 and 2 appear to be the only "header" fields that are actually repeated when multiple lines are present. You do not want to use the calculation operation on them, they must use the Replace operation so VI will be able to determine when a new header record is truly needed. You need to use the Calculation operation on any other header fields that you will be importing where they are not repeated when multiple lines are present. You would enter the condition like this.

IMP$[3]<>""

Before you go and do this on a bunch of fields, i would first test it out with a very small source file sample that contains at least 3 separate orders where order 1 and 3 might have just 1 line and order two in the middle can have 2 or more lines. Then set up a new import to include just the bare minimum fields with one header column using the Calculation and IMP$[x] reference.

That is an awesome trick David!

One SO.

David! My man! That is the Rosetta Stone! Last question for now. I'm a newb a t supporting Sage, so please bear with my lack of knowledge. I am using the Calculation Operand and the Max Characters is causing failure of things like my ShipVia field. There are more than 15 characters. How do I allow more than the max for a field when using Calculation as the operand?

Thanks once again! This has been an education.

Kevin and David,

Using IMP with the Calculation Operand is not working with my FreightAmt field I'm guessing because it's Data Type is Number and not String. I can't use the Replace Operand for anything else, so I'm guessing I''l have to convert the number to string or use an additional temp field. Do I have that correct or am I way off?

Thank you a thousand times over.

You cannot exceed sage 100's max length for a field, the database file will not support it. You can either truncate it down to the max length, modify it by abbreviating it to something else, or skip it. On the first tab (configuration) there is an option to skip truncation, do you have that checked? If it isn't checked, then if you source exceeds the max length, it should get truncated automatically although I don't recall ever needing to observe and confirm it will do that on fields using the Calculation operation. If you have to truncate it yourself, use the MID function.

MID(IMP$[3],1,15)

The first argument is the string to be modified, the second argument is the starting point (not zero based), the third argument is the number of characters to keep from the starting point.

For numeric fields, you have to convert the string variable IMP$[x] into a number. Try this.

EVN("NUM(IMP$[x])",0)

The NUM function takes a string variable or value in the first argument and converts it to a number but it will cause a hard error if the string cannot be converted to a number so we wrap it in the EVN function which evaluates an expression in the first argument and returns a number but if the evaluation causes an error, then the value in the second argument is returned instead, in this case, I used 0.

OK, I think I understand the conversion. Here is a screenshot of what I'm trying to do. I think I have the ShipVia problem licked. Thank you for that! The problem at hand now is the FreightAmt.

Since I cannot use Replace, and it FreightAmt is a Number that needs to be set to Calculation so that I can keep multiple line items on the same order and Next works appropriately on the SO field, here is what I have:

Where Column 10 is the FreightAmt, what is the Calculation and am I doing the Condition correctly. I cannot thank you enough. Our preovider who is migrating our Mas90 to Sage100 is stumped as well.

Take what you have in the Condition and copy it to the Calculation. 

Then add >0 to your existing condition so it should be

EVN("NUM(IMP$[10])",0)>0

Hallelujah! David, I owe you and Kevin M. a  great debt! I can't say thank you enough times to even cover this, but it has been a tremendous education. Much obliged! I hope this does it for this VI job. You have a wonderful weekend!

OK, so I wrote too soon. Your formula filled in the freight amout for the first order, but missed the subsequent orders. The remainder show no charge, and they are orders that do have a shipping charge associated with them.