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

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.

Thank you

These examples are fantastic. I will give this a try and see if I can make it work for a single column. We have a python script already built on the FTP server configured to give the 3 columns the needed headers.

Thank you very much!

If you want column headers, you can click the "Options" button on the "Configuration" tab and check the option to export the column names as first record.

We have two warehouses. Is there a way to modify this to use the warehouse with the highest amount available, but put a zero if both are in the negative?

We want to be able to sale the product if we have some in either warehouse. Sometimes the big warehouse which has larger quantities runs out, but we'll have enough to complete a small order in the smaller warehouse.

Not with that code. Since the temp field, while being processed, will only see the warehouse values for the current warehouse being read. It is possible to use two other approaches to do what you want but both involve even more ProvideX code to make it happen. 

Perhaps you should consider using VisulCut, you design a crystal report and then configure VisualCut to run and export it. It has options for emailing the exported file too.

Another approach would be using any programming language capable of querying data via ODBC and query against the SOTAMAS90 DSN and execute a SQL statement that will return the recordset containing the data you want and then write the data to a flat file with your choice of a delimiter.

You may not be able to do it with VI, but you should be able to create a script on sales orders that performs this logic after it is imported.