Sage 100

I need help with a V/I function in 2014. The source file has a column of values. Each value corresponds to the customer's GL departments. I can create a Temp field to read in the values. Now I think that I need another Temp field with a function to do something like IF Temp001$='9642' THEN '020'. I don't know how to achieve this with V/I functions.

What format is the file in and what file in Sage are you importing it to?

You can do a series of Calculations with Conditions, this can be a Temp or direct field assignment. In the Condition, test for the Temp001$='9642' and in the Calculate assign '020'. You will need to have one for each department. Also, make sure to mark to clear the field :)

The source file is .csv. I'm importing into AP_InvoiceHeader.

What Michelle said... If you need 20 assignments, you'll need to make 20 temp fields for testing, as you can only test one at a time.

Like this (see attachment)

VI does not have if then. You have to assign the GL account multiple times each entry with a condition. The first concern is if the client is going to add new departments, then someone has to maintain the VI Job. An alternative may be to consider a more elegant solution. This would incorporated a User Defined Table of departments and values and a perform logic to look up and assign.

Thank you all! It works with it set like Michelle's screen shot. There are a LOT of departments, so now I'll try Larry's suggestion. I really appreciate your quick responses.

So, @LarryBradford, I've created a UDT and populated. What can you tell me about how to create ""perform logic to look up and assign""?

Oh, that magic is something that I do not setup often. This is the scripting that is offered in the training by @AlnoorCassim and the like. I wish I did more of it so I could whip out that portion for you. I am sorry.

Ah, OK. Thanks, Larry.

I know it can be done. If I was a little more comfortable with it, I would work it out. I just do not do enough of it. I do know that if I had a lot of departments and expected more, I would suggest they either get comfortable with VI or build something like this at a fee.

Another alternative is to create a small routine in Access to pre-process the csv file to create a more VI friendly source file. Then you can have Access save the output as a new csv and then call VI to execute the job. This results in a process easier to maintain and does not create any additional work on the customers part. (You can also do this in Excel is you prefer. The process is similar but data manipulation is more elegant in Access.)

FWIW - I have had Jon Reiter at DSD create lookups for me just as @LarryBradford described using a UDT and added them to the VI logic.

I've done something similar by scripting the GL assignment. The VI job could populate a UDF and a script could read the UDF and get the correct GL account from the UDT.

Here's a good way to handle this, Karen, which will work. If we pretend that you have four values coming in, instead of 28, it will make it easier to describe. Here are the values I'm using in my example and what I want to filter them to: 9010 ==> 100 890 ==> 101 9201 ==> 102 391 ==> 103 This can be done with nested POS() and TBL() functions. It's a little messy looking but it works. First, assume that the original column value is stored in TEMP001$ and it is a maximum of 4 characters. Create a calculated field and put this in the calculation text: TBL(MAX((POS(PAD(TEMP001$,4)+""|""=""bad |9010|890 |9201|391 |"")-1)/5,0),""BAD!"",""100"",""101"",""102"",""103"") As I said, it's messy looking. The PAD() simply insures that the string we've got is padded to four characters. I added a pipe ""|"" after it to make sure we have a unique pattern to match. The POS() function looks for where the match string (TEMP001$ plus pipe) occurs in the longer string that follows it and it returns that position. So it TEMP001$ contains ""391"", the POS() function will return 21, because that's the number of characters over that ""391 |"" occurs in the string. We take that number and subtract one because we need to arrive an index that starts at 0 and increments by 1. Since each possible match value in our long string is exactly five characters long (very important) we divide by 5. So the position of 21 becomes (21-1)/5 or 4. The reason I put the MAX() function there is in case TEMP001$ contains something that does match anything in the possible values. In that case the POS will return 0 and we'll subtract one and divide by 5 and end up with a negative fraction which will cause an error. The MAX will make it come out to zero, instead. The list of arguments immediately after the MAX() are the converted values, in the exact same order that the original values show up in the search string. for my example, ""391"" would return ""103"".

First, thanks to everyone who responded. Second, a HUGE shout-out to @PhilipWhirley, programmer extraordinaire, for providing the above VI function, which works great! And thanks for sharing it with the group, Phil. This is a wonderfully collaborative group.

@PhilipWhirley This is a keeper! @RobertWood can this be saved for future generations?

I'll work to get it moved over to the knowledgebase.

Robert, if you add it to the knowledgebase, please add the word ""not"" in this sentence: ""The reason I put the MAX() function there is in case TEMP001$ contains something that does NOT match anything in the possible values."" Thanks.