Visual Integrator

Question

We have an export for item quantities. At the moment we're exporting 3 columns of data which include "Item Code", "Temp001" (which is a calculated field), and "Suggested Retail Price". 
 We're having negative numbers that are exporting in the "Temp001" field which is calculated by taking the "IM_ItemWarehouse01.QuantityOnHand" and subtracting "IM_ItemWarehouse01.QuantityOnPurchaseOrder" & "IM_ItemWarehouse01.QuantityOnSalesOrder". We don't want to update our site with negative numbers. This export is created and pushed over to an FTP server which pushes it out to the website. 
 Is the a way to say that if "Temp001" is less than "0" then export only "0" with visual integrator? 
 Would I use a conditional expression to do so? 
 If it's possible, how? I can't figure out the syntax of the conditional expression.

BigLouie · Answer

You would use two Temp fields and on one if the calculation is less than "0" and one if it is greater.

David Speck · Answer

If you want to have the value exported to a single column, you can do it one of two ways, with a single temp field set up as a string or two temp fields, the first as a string and the second and a number. 
 You can tweak the first part of the expression (seen in the below examples) within the STR() function but everything after the semi-colon needs to stay the same. In short, the expression is taking the results of the equation within the STR() function and using the STR() function, converts it to a string, this value is stored into a variable named VAR$ which is referenced in the latter portion of the expression to convert it back to a number using NUM() and check if it is less than zero, if it is, then set the VAR$ variable to "0". 
 
 If using one temp field because you don't mind the output being treated like a string, the temp field should be set up like this. 
 
 STR(IM_ItemWarehouse01.QuantityOnHand + IM_ItemWarehouse01.QuantityOnPurchaseOrder - IM_ItemWarehouse01.QuantityOnSalesOrder); IF NUM(VAR$) < 0 THEN VAR$ = "0" END_IF 
 
 If using two temp fields so the output is treated like a number, the first should be set up like this. 
 
 STR(IM_ItemWarehouse01.QuantityOnHand+IM_ItemWarehouse01.QuantityOnPurchaseOrder-IM_ItemWarehouse01.QuantityOnSalesOrder); IF NUM(VAR$) < 0 THEN VAR$ = "0" END_IF 
 The second should be set up like this, where the Temp002$ equals the string temp field that has the above expression. This example had another temp field in as 001 so you'll need to adjust accordingly.

Sage 100

Visual Integrator

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING