When you import with any of Import UD Fields or Import Extension tables ticked, the import process attempts to contact Epicor installation; if you import without them, then the import is solely based on the metadata from the assembly, and the first attempt to contact Epicor happens at runtime (Test Method or SC workflow execution).
If you tick Use Service License, that affects what SC requests Epicor installation to use for the session (web service license or regular user license). To be able to use web service license, that kind of session should be licensed in Epicor. If it is not, attempt to open the session with service license is expected to fail, though not with the object reference not set exception.
I would suggest to verify:
The Epicor client deployed to SC Server (the one from which you attempt the import the reference to SC) is functional – you can start the client, log on, open form and load data.
The .sysconfig you pointed SC to during the import of the reference is the one which the Epicor client in p#1 uses.
@AMS Right. The Test works perfectly fine now. Client had not been updated to latest version.
I have reimported the Reference and re-run the Workflow. Error at the same stage (the .Net Reference Call) but it now states record not found. I ran a test at the point before the Service Connect Workflow starts and the Test finds the record without problem, i think my workflow may be set up incorrectly with the ins and outs from the previous image.
@AMS I finally have a small victory here. I just had to change the first conversion input from the Response Schema to the Request Schema and everything went as i expected it to
I say small victory because if I Post 2 invoices at the same time the SC Workflow only runs once even though the BPM is set to run every time an Invoice Header record changes to Posted. It has exported an xml file for the Last Invoice in the list rather than both. (10399 instead of both 10398 & 10399)
Thank you for all your help on this matter. it is really appreciated.
Remember that Document Tracking is your friend, it allows to review the state of the message as it goes through the processing (the output of the previous step in DT is what arrived into the element as its input).
It is called once per service call, before the call returns to the calling party outside of the web server, and receives all records that were changed within the table.
The call to SC Workflow mot probably gets all the records.
Check in SC Document tracking, on Start node – how does the message look - does it contain multiple invoices?
If so – you may need to move calls to GetByID into a sub-wf and use synchronous or asynchronous cycling within SC (depending on whether you want to push a single XML per updated invoice or not, OR you could collect all invoice IDs you received and build a where condition for GetRows instead of GetByID – that would allow you to do a single call to the BO to retrieve the data, however it may not be the best idea if you need to post XMLs separately.
Note: if it might happen that 1000s of invoices are processed in a single call and trigger the BPM directive, something more creative might be needed if you need to consolidate them in a single XML file.
I will give the SubWorkflow a go now.
Just to note though the START node has 4 entries (TempRow’s). 2 for each invoice i post. (This was 1 entry when posting 1 invoice.
The Workflow is now working with multiple InvoiceHeaders
I also changed the DataDirective Rule to Matching instead of Existing and it only processes 1 request per Invoice Header.
Just 2 more small things if you wouldnt mind.
Changing the file name of the XML files from GUID to InvoiceNum
The AddressList from Epicor i need to split into individual fields into the XML file.
If you go into the Poster element on the workflow and click configure, you can get to the file name.
There, you can use XPath to pick data from the message to use within the file name.
You will need to do some advanced parsing in XSLT, I think – SC does not have a built-in XSLT functoid for splitting a string into a nodeset (though it probably should – feel free to report an idea).
When you are editing the XSLT conversion, you can switch from graphical to XSLT view and do whatever you need.
Note: be careful afterwards if you need to edit that XSLT graphically. I would put such a splitting in a separate XSLT that just copies everything else while doing the parsing.
Here are a couple of references to how people do value splitting in XSLT. Be careful to reference proper namespaces for source and target elements while doing the splitting:
I have looked at the XSLT splitting strings and if I am honest it is way above my skill set with the lack of time have.
I have come across a Functoid which can lookup an Epicor table and select a field based on the CUSTID from the workflow dataset. I am unsure on how to configure it correctly though…
Do you have any idea on how to correctly call the SQL database from here?
What is the AddressList you mention and what is the goal you need to achieve?
dbLookup functoid goes directly against the database you specify and returns data.
Each time it is called from within the XSLT transformation, it opens a connection, retrieves data, and returns it into XSLT.
It is not a good choice from perf point of view if multiple calls are expected to be performed; DB Operation element is a better one as it does a single hit against the database, however you may need more XSLT before/after.
Either way, hitting the database is the last resort, it is far better to go against BOs or any other kind of software-exposed interface.
For the dbLookup, if you need to apply a where clause, you need to build it using concatenation and feed it into the where clause of the dbLookup, I think – something like this.
Note – building SQL using string concatenation is error-prone, as it opens you for SQL injection.
Within DB Operation, when you build the SQL statement, you can use so-called safe links to the message data, which escape the value and wrap it safely for SQL.
Note that the table name for customer data in Epicor database is Erp.Customer.
The one without the schema (or, more correctly, dbo.Customer) is the view that only exists if there are UD Fields on the table and is joining the main table and the _UD one.
Mentioning this because I see Customer on your screenshot above.
@AMS Thanks for the explanation, i don’t really want to be having this workflow read from the database as it will have to do it 15 times per XML file and then 100 xml files will be produced.
Epicor uses Address Lists rather than Address1, Address2, Address3, State, ZIP (They are available in the database though) The XML im trying to create requires the Addresses as individual fields.
As per Below: Epicor Data on the left, XML schema on the right.
@AMS There is no separating character. when you push the whole Address List into Address 1 its just a long string with spaces. (I cant use a space as a separator because most address fields have spaces within them.
I put a . at the end of each address line and i managed to get the 1st and 2nd address lines using the before and after substring but the rest of the lines were just repeats of the 2nd address line. (These used the After Substring Functoid
This is how it can be split in SQL: (Top 2 Strings in AddressList)
