Sage 100

Sage 100c Standard v2016. Importing sales orders via Visual Integrator from csv file. The import file shows the extended price but not the unit price. For example, qty 2 / $51.00. Need to calculate the unit price (example $25.50), however no matter what conditional formula I use, I get error 40 (divide by zero). I am also having trouble overriding the Sage 100 calculated price directly. Can anyone help me?

maybe unit of measure conversion factor issue?

I am wondering if you set the extended price the Business Object it will calculate the unit price. Have you trying not importing the price? ( You will need to import the Quantity.)

@Jim, yes I have tried that, but it doesn't do it.

do a calculation... RND(Imp$[x] /imp$[y] ,2) (where x is the Extension column and column y in the quantity column Also put a conditional logic line like this imp$[y]<>0

@JonParkinson edited line above

How about reading in the extended price and quantity prior to the calculation? If not, I'd try reading the extended price into a numeric temp field and then doing the calculation. I'm actually surprised that @JimWoodhead 's suggestion to have the system calculate the price didn't work. That's how it works if you do it manually.

Here is an order with no price imported.

I have tried multiple temp field calculations. As soon as I enter a divided by formula - the VI job errors #40.

@Jim I tried: RND(Imp$[{SO_SalesOrderDetail.ExtensionAmt}] /imp$[{SO_SalesOrderDetail.QuantityOrdered}] ,2) but it does not pass the syntax check. Variable type invald.

no use RND(Imp$[2] /imp$[3] ,2) the number references the column in the import file. it is a direct read of the data file

above assumes your data file looks like Itemcode,extension,quantityordered

What am I doing wrong? FYI - Item code in column 22, Quantity 24, Ext Amt 26.

rnd(num(imp$[26])/num(imp$[24]),2) try this.

the formula needs numbers to do math. the num function turns the string into a number and the RND rounds to 2 decimal points

@Jim - Appreciate the help! But still no luck

oops .. change RND to PRC too many languages!!

Partial good news, it now passes the syntax check, but I get this error when running the import job.

Make sure you put record logic in to skip the Header line of the file if it has column headers or remove line from the file.

YES! Thank you very much @JimWoodhead