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:
Any suggestion welcomes. I would like to thank everyone in advance!
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:
- Pre-Map Tasks
- Data Read
- Transformation
- Pre-Document Tasks
- Document Send
- Post Document Tasks
- Post Map Tasks
Any suggestion welcomes. I would like to thank everyone in advance!
Answers
Best Answer
lorren answered 7 years ago
Jack Liu replied 7 years ago
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.
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.
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.