VI Export Job - Conditional export of two data fields into one column

Question

I want to export SO_InvoiceDetail data. In one of the columns, I need the value to be the ItemCode, unless there is an AliasItem, in which case I want the AliasItem to be exported in the column. My export currently looks like: 
 InvoiceNo - Col 1 
 ItemCode - Col 2 
 AliasItem - also Col2, with condition that len(AliasItem) <> 0 
 The export result is 3 columns of data, not 2. Is there a solution for this? Or is there an "if-then-else" type of function that I could use in the conditional calculation to map col2 to the ItemCode if the AliasItem is blank, else use the Alias Item? 
 Thanks for any assistance.

David Speck · Accepted Answer

It is usually easier to to query what you need from Access, Excel, Crystal Reports, or Business Insights Explorer but if you must use VI Export then you can try the following. I used sales order instead of invoice because of the records i had set up already but you can duplicate this with invoices. First, add just the columns you want exported so if you are exporting headers, the number of column headers and their description will match what you want. Then change the Operation of the field you need to conditionally set from Assign to Calculated. Then use the following in the Calculation but replace the fields with the correct field from your table. This is a compound statement. The first thing it does is assign the item code to the internal variable VAR$, the semi colon then indicates there is a separate statement following this assignment to the VAR$ variable. The next statement then checks if the AliasItemNo$ field is not blank, you could also use LEN([field]) <> 0, if the field is not blank, it then assigns the field to the VAR$ variable. SO_SalesOrderDetail01.ItemCode$; IF SO_SalesOrderDetail01.AliasItemNo$ <> "" THEN VAR$ = SO_SalesOrderDetail01.AliasItemNo$ END_IF

Sage 100

VI Export Job - Conditional export of two data fields into one column

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING