I’m working on an AP tax workflow for a customer. I’m currently exploring a method to calculate line-item taxes based on a tax code. The line items are pre-populated with description, item price, total price (item price x quantity) and a tax code. The tax code describes which taxes are applicable. I am wanting to use the DataLink Field Group task to lookup the tax code and apply the proper tax rate. The tax rates are in an excel worksheet, and the datalink is just an ODBC connection.
The problem is that when I run the task, it will populate the tax rates ONLY for the first line, AND it will delete the pre-existing information in the field group. The 2nd, 3rd etc lines are just completely gone. My understanding was that DataLink Field Group was supposed to work on each line of a field group. Screenshots below to explain:
Only the top row is preserved, but the description, quantity, and price is gone! The second and third rows are just deleted.
This is the workflow itself:
The maddening part is I know I’ve managed to do this before, but unfortunately I don’t have that old workflow to compare. Any help would be appreciated, I’m on a huge time crunch
The way I always think of it is that the “To” option will wipe out the field group and replace it with the queried results, whereas the “From” option will update the field group with only the queried results as you direct it to. Field groups in general seem to have all sorts of nuances, subtleties, mysteries, etc.
OK I will keep that in mind. God, what a pain, lol.
Sorry to tack on an add-on question, but my next challenge is to calculate each item’s GL code based on two other fields. Each line has a Cost Group and a Cost Code, and together these make up the GL Code. The format of the GL codes is XXXXX-XXX.
I tried to use a simple join function with a dash delimiter, but it cleared out my whole field group - looks like “join” doesn’t have an option to act on a field group, and specifying the field group name in the inputs didn’t work. What would be the best way to do that?
When it comes to ECM, you’ll find there isn’t exactly a “best way” for anything lol It really comes down to if it works then it’s a success.
There’s two ways I would suggest to accomplish what you’re after.
Easier Method
Use the Replace task to put the field values into local variable values (Ie. $Field.FieldName as the input, zFieldName as the output)
Use the Join task as you did before, but this time with the local variables
Use the Set Field Group task to populate the ENTIRE field group with the existing data and where your GL Account field is, you would populate that with the local variable you used as your output in the previous Join task
Succinct Method
Use the Advanced Math task to collect your fields, join them together and populate the field group as needed
I am suggesting both as the Advanced Math task can be a bit tricky to get working. You may need to also do the whole Replace task thing as sometimes it doesn’t like when you use actual fields for a method like this.
Something along the lines of having the three inputs be the first portion of the code, the dash character and then the second portion of the code could allow you to do ‘{0}{1}{2}’ I think. Or maybe it has to be ‘{0}’‘{1}’‘{2}’ but you’ll have to test some options to see what works. Fortunately, there is the Test Input button which will enable you to do this without restarting the workflow each time.
So I’ve figured out how to join them together with dashes, but is there a way to have the 3rd field be “optional?”
The GL codes are usually composed of the first two fields, unless the first field is greater than 80000. So something like IF field {0} > 80000, concat {0}, {1}, {2}, else concat only {0} and {1}
Just leaving the third field blank is giving errors, and when I suppress the error it just doesn’t do anything, as expected.
The issue may be that it is evaluating the “80000” as a string and not a number so it wouldn’t work with a mathematical comparison. You may need to first format that section of code as a number to do that comparison. I’d suggest keeping the original and formatting the string into a number as a local variable, then introduce that into the Advance Math task.
Your Advanced Math task expression would be something like:
IIF({0} > 80000, ‘{1}{2}{3}’, ‘{1}{2}’)
Inputs:
{0} = zFormattedValue
{1} = Actual Value ($Field)
{2} = “-”
{3} = Actual Value ($Field)
One last thing - what’s the format code to convert string to numbers? There seems to be a distressing lack of documentation regarding format codes, advanced math functions, etc, on Epicor’s part.
The Format task may be able to do this for you. You could also try the “Don’t Infer Types” option in the Advanced Math task. There is a way to convert them in Advanced Math, but it’s using .NET code and it’s not the easiest to work with.
I very much wish that the Advanced Math task had a better guide to its capabilities and syntax.
Okay, I’ve almost got it (I think). Had to tweak that statement a lil. I have my fields set to Integer to avoid type mismatches.
When I put in test values, it works exactly how I want:
This is the task setup:
This is a test with a value less than 80000. The result is correct
This is a test with a value greater than 80000 (I used 79999 because I wasn’t sure if there was a “less than or equal to” symbol). Again, exactly how I want the result to work
But when I run the actual workflow:
I don’t get how it works in the test function but not when actually trying to run it.
