Sage 100

I thought I read somewhere that runtime prompts using a temp fields was being added to VI in the v2026 release.  Probably wishful dreaming, though.

HI Kevin,  so the ODBC connection is reading the Sales Order Detail, correct?   You should be able to put that in the SQL statement in the ODBC connection

You could use a UDT with a single row 7-character key for the sales order and join that to the sales order detail table in the ODBC Table selection. They edit the UDT and not the V/I import job.

Thanks for the idea Dan.  I'd probably go with an SO header UDF checkbox, with overridden SQL filter, over a UDT (which I find awkward to work with).

You could use a UDT with a single row 7-character key for the sales order and join that to the sales order detail table in the ODBC Table selection. They edit the UDT and not the V/I import job.

This is possible by using perform logic on the "On Execution" event to override the SQL$ variable.  The easiest way to do this would be to put a placeholder in the SQL statement's WHERE clause, something like this.

SELECT    * FROM    SO_SalesOrderDetail WHERE    SalesOrderNo = '$SONo$' 

You'll then replace the $SONo$ text string using the SUB function with the value in the runtime assigned variable, which starts as OPTION1$ and increments for each field on the Data tab that uses a runtime assignment.

The actual perform logic can be entered inline in the field as long as it contains a ' character and Sage 100 will EXECUTE it instead of PERFORM'ing it.  So it should look something like this.  Keep in mind that thee SUB function is case-sensitive so make sure you get the case of your placeholder correct.

SQL$ = SUB(SQL$, "$SONo$", OPTION1$) ! ' 

Thanks for the idea Dan.  I'd probably go with an SO header UDF checkbox, with overridden SQL filter, over a UDT (which I find awkward to work with).

You could use a UDT with a single row 7-character key for the sales order and join that to the sales order detail table in the ODBC Table selection. They edit the UDT and not the V/I import job.

This is possible by using perform logic on the "On Execution" event to override the SQL$ variable.  The easiest way to do this would be to put a placeholder in the SQL statement's WHERE clause, something like this.

SELECT    * FROM    SO_SalesOrderDetail WHERE    SalesOrderNo = '$SONo$' 

You'll then replace the $SONo$ text string using the SUB function with the value in the runtime assigned variable, which starts as OPTION1$ and increments for each field on the Data tab that uses a runtime assignment.

The actual perform logic can be entered inline in the field as long as it contains a ' character and Sage 100 will EXECUTE it instead of PERFORM'ing it.  So it should look something like this.  Keep in mind that thee SUB function is case-sensitive so make sure you get the case of your placeholder correct.

SQL$ = SUB(SQL$, "$SONo$", OPTION1$) ! ' 

Thanks for the idea Dan.  I'd probably go with an SO header UDF checkbox, with overridden SQL filter, over a UDT (which I find awkward to work with).

You could use a UDT with a single row 7-character key for the sales order and join that to the sales order detail table in the ODBC Table selection. They edit the UDT and not the V/I import job.