Sage 100

I have an import for Sales Order Invoice.  In the Quantity Ordered on the line item, if for some reason, it is not numeric, it returns 0 instead of failing the line.  Without writing any code, is there anyway to do this?  I have tried a couple things, and the result is every line returning 0.

------------------------------
Patrick Genualdi
Consultant
MBA Business Software, Inc.
Atlanta
------------------------------

I'm not sure this is possible "without writing any code".  I am certainly not a professional programmer, and the process below has not been tested.  There is most probably a more elegant solution, but the below may be worth trying if you haven't already found another solution:

In VI, create a temp variable of type string (we'll refer to this variable as Temp001$). The Operation is Replace, and the Col/Pos is the position in the input file of the Qty Ordered, Assign on Line record.

In VI, create another temp variable of type string. (we'll refer to this variable as Temp002$). The Operation is Calculated. Click the Expression Builder icon in the right margin next to the Calculation field. Enter the following formula, then click Accept: NUM(Temp001$, *)

In VI, Column Name is Quantity Ordered, Operation is Calculated. Click the Expression Builder icon in the right margin next to the Conditional field. Enter the following formula, then click Accept: Temp002$="1"
Click the Expression Builder icon in the right margin next to the Calculation field. Enter the following formula, then click Accept: NUM(Temp001$)

You should then get either a 0 or the correct numerical amount in the Qty Ordered field. You can use the Select tab to filter out the 0 records if necessary.

FYI
The NUM( ) function returns the numeric value of a numeric expression in a string. The string is evaluated and converted to a numeric value.

If an * asterisk is used, the NUM( ) function does not convert the value of string$ but rather validates it can be converted to numeric. It will return a '0' if the value in string$ is not a valid numeric string, or 1 if it is a valid number.

NUM("1.34",*) Yields 1

NUM("A",*) Yields 0



------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

Every attempt i've made to use the errval argument with NUM has failed on version 2018 or lower. I haven't tested yet on later versions.
Patrick, what is the end goal? To force the line to fail and not write?
If so, something like this should work.

1. You need one temp field set to the lines and string, point it to your column containing the quantity ordered.
   1. 
   2. This is how it should be set up. Make sure it clears on each record.
      1. 
2. You need two ItemCode fields added. This is how the first ItemCode is set up. Point it to your column containing the item code.
   1. 
   2. This is what the conditional expression should be. Similar to what Doug posted but uses the EVN function which does support an "error value" in older versions. Make sure to substitute the temp field number suffix with the correct one. If the result of the EVN formula is not equal to zero, the item code will be set to whatever is in the source column.

      EVN("NUM(Temp001$)", 0)<>0

3. This is how the second ItemCode is set up. Assign any value you want to it but make sure it is not a valid item code. I used "INVALID QTY" for this example.
   1. 
   2. This is what the conditional expression should be. If the value in the source column is zero or is not numeric, than the assigned value will be used.

      EVN("NUM(Temp001$)", 0)=0​

Here are results.

1. With an non-numeric value.
   1. 
2. With a zero.
   1. 
3. With a quantity not equal to zero.
   1. 

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 05-04-2020 10:50
From: Doug Higgs
Subject: V/I Import to S/O Invoices

I'm not sure this is possible "without writing any code".  I am certainly not a professional programmer, and the process below has not been tested.  There is most probably a more elegant solution, but the below may be worth trying if you haven't already found another solution:

In VI, create a temp variable of type string (we'll refer to this variable as Temp001$). The Operation is Replace, and the Col/Pos is the position in the input file of the Qty Ordered, Assign on Line record.

In VI, create another temp variable of type string. (we'll refer to this variable as Temp002$). The Operation is Calculated. Click the Expression Builder icon in the right margin next to the Calculation field. Enter the following formula, then click Accept: NUM(Temp001$, *)

In VI, Column Name is Quantity Ordered, Operation is Calculated. Click the Expression Builder icon in the right margin next to the Conditional field. Enter the following formula, then click Accept: Temp002$="1"
Click the Expression Builder icon in the right margin next to the Calculation field. Enter the following formula, then click Accept: NUM(Temp001$)

You should then get either a 0 or the correct numerical amount in the Qty Ordered field. You can use the Select tab to filter out the 0 records if necessary.

FYI
The NUM( ) function returns the numeric value of a numeric expression in a string. The string is evaluated and converted to a numeric value.

If an * asterisk is used, the NUM( ) function does not convert the value of string$ but rather validates it can be converted to numeric. It will return a '0' if the value in string$ is not a valid numeric string, or 1 if it is a valid number.

NUM("1.34",*) Yields 1

NUM("A",*) Yields 0



------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

A couple of typos / edits have been made to my last post.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

​Thanks to all for the help.  An end user gets order info from their customer in an excel spreadsheet.  One column is quantity ordered.  Sometimes the clients will enter e.g. 3 cases instead of 72.   Instead of failing "3 cases", V/I imports it as zero.

I had the end user modify the excel spreadsheet she sends out to validate that quantity ordered is a valid number and not alpha.  This worked.

------------------------------
Patrick Genualdi
Consultant
MBA Business Software, Inc.
Atlanta
------------------------------