Sage 100

How do you do a Select criteria in a VI job where the selection is based on the value of another field? I am trying to export IM_ItemWarehouse records when the warehouse code is equal to the Default Warehouse code in CI_Item. Both files are available in VI, but I just don't know how to compare the two in the Select statement.

I couldn't find a way to do that exactly, but could you force the record to fail instead? I'm thinking you could create a temp variable to hold the warehouse code from CI_Item using a file assign ( Key Assigns Only). Then assign the warehouse code in the import first followed by a conditional for the item code where you only populate the item code if the temp variable is the same as the warehouse code?

Unfortunately, this is an EXPORT, not an IMPORT, and the warehouse code itself is not being exported.

Sorry, I didn't catch the word export in your post. I don't see a way to do what you want other than to use the same logic by assigning two temp variables and conditionally leave all the fields blank. that leaves your export file filled with blank lines which is probably useless if you are trying to do some sort of integration. Seems like temp variables should be available on the select screen or add file assign logic

Why use VI? There are many more flexible ways to extract data.

@KevinMoyes - I know, but this export has two flipping fields in it and it needs to be scheduled and everything else just seems like overkill, ya know? This particular facet of VI has bugged me for years - this isn't the first time I've wanted to do a comparison between two fields in a Select criteria, and it just seems like it should be possible. (*Stomps foot petulantly.*)

I guess they don't already have Visual Cut then. Add a UDF to IM_ItemWarehouse?

Well, @KevinMoyes - I guess there's that option.

Our @DavidSpeckII figured it out. Millennials can't take ""NO"" for an answer. (And I love him for it.)

Here is a quick write up for reference.

Quick question... In your Temp variable calculation you start with """";IF .... what is the null string for? Curiosity got me here.

Because the VI job is expecting a value to be returned by the ""calculation"". ProvideX can do compound statements on the same line by separating them with "";"", similar to how VBA/VBS uses "":"" to separate statements on the same line. So when the VI job validates what you entered, as long as it is proper ProvideX syntax, it won't complain. This allows for some pretty nifty stuff to be done.

I think I get it... you could actually return anything at all to the Temp variable as it is not really going to be used here.... The meat and potatoes is the if then logic setting the select flags in this case ... pretty cool way of breaking into the program flow! Thanks for sharing...

That is correct. This could also be done using perform logic but it would require an additional file dependency so figured it was best to just put it in a Temp field. At one point i recall being able to specify a statement to be ""EXECUTED"" vs ""PERFORMED"" in the perform logic field if there was an ""!"" found in the field value but that didn't work in the export job created in 2018. It may work in prior version or it might only work on an Import job vs an Export job, i haven't checked.