Skip to content
+1-888-319-3663

COMMUNITY FORUM

SmartConnect For GP Vendor Inserted

Jack Liu asked 7 years ago
Hello,
I have a question about vendor migration using smart connect map for GP.

Basically, in my source file, no vendor id, only vendor name exists. Vendor id is calculated column in the map.  Vendor id is in the format "MCV00"+integer such as MCV0056.  Because the vendor name could contain single quotation or double quotation, I have to reformat the name.  Then I will check whether the vendor exists based on the modified vendor name. 

The vendor id is calculated based on the following rules 1) if existing, use current vendor id; 2) if not, look up the max number in  existing Vendor ID . For example, the latest VendorID is MCV00560, the 560 is returned. The new calculated Vendor ID will be MCV00561.

I did it in the following ways:
I created three tasks which are ones that run before document:
1)taskrename: reformat the vendor name in the source file and set it to one global variable, GBL_Vendor_Name
2)taskexistingcheck: check whether the vendor existing using above global varible, GBL_Vendor_Name. If existing, updating one global variable, GBL_Vendor_Exists
3)taskmaxnum: get max number in the partial vendor id in database, assign it to one global variable, GBL_Vendor_Maxnum.


As I said, the calculated column vendorid will be set up based on two global variables: GBL_Vendor_Exists and GBL_Vendor_Maxnum.

The problem is calculated column vendorid always is before the three pre-document tasks. In other words,
1) the first record always be inserted into or updated on vendorid= "MVC001" because it always uses default global variable GBL_Vendor_Maxnum which is 0. GBL_Vendor_Exists always is 0 which is default value. 
2) Global variable GBL_Vendor_Exists is useless because it is reset after calculated column set.


Could anyone give suggestions how to solve the problem?  I know the processing sequences in smart connect are:
  1. Pre-Map Tasks
  2. Data Read
  3. Transformation
  4. Pre-Document Tasks
  5. Document Send
  6. Post Document Tasks
  7. Post Map Tasks
What I look at is how to let Pre-Document tasks ahead of Transformation (in this case, calculating Vendorid after pre-doc tasks). 

Any suggestion welcomes. I would like to thank everyone in advance!
Answers
Best Answer
lorren answered 7 years ago
Jack,

You can do all of that code in your Vendor ID Calculated field.  Return the final value that you have calculated to the Vendor ID field.

Everything that you are trying to do as a pre-map task can be done as a calculated field.
Jack Liu replied 7 years ago

Hi Iorren,
Thanks for the reply!
But how to do the sql queries in the calculated field?
I know vendor name transformation is ok. But Check whether the vendor name exists and get the max number from VENDORID need to query the database before new vendor id generated. I cannot use MS SQL lookup column because it requires only source column as input parameter instead of Global variable. Could you give me some scripts example how to do the queries in the calculated field in C# or VB? do I need to add external library for the sql queries?

Thanks again!

lorren answered 7 years ago
Jack,

Here is a link to a blog article that discusses creating Script Templates but contains the sample .NET code to execute SQL.

http://blog.eonesolutions.com/2013/11/tech-tuesday-creating-and-using.html

You don't need any additional libraries.
Jack Liu answered 7 years ago
Thank you for your fast response!
This forum is really helpful.
I will check it.

Thanks again!

If you would like to submit an answer or comment, please sign in to the eOne portal.