I am trying to extract data from a Lookup From a Database so it gets imported into my Update a Database and i am unsure how to pull the result through. I am currently trying to migrate from v4 to v5 and as the Lookup From a Database is different to the old version now we use .NET etc, i believe i have to extract the variable
Here's what i have set up.
If i save the above i get this warning
and creating that variable makes it go away but means nothing gets pulled from the database so I leave it by clicking Ok.
What I want is the result that comes from "contractcustomeritemid" to be extracted and pulled through.
I have tried extract Field and look for SystemCare or leaving the Look For blank but do not know how to pull in the result of the Lookup From a Database.
The result i get from the above is the following
2022-11-23:09:41:05 Opened database using provider=MySQL;User Id=;Password=***********;Host=;Database=CNCTEST;Port=; Line: 13
2022-11-23:09:41:05 Field ContractCustomerItemID Set To 40196 Line: 13
2022-11-23:09:41:05 Database lookup returned 1 rows Line: 13
2022-11-23:09:41:05 Extracted field ContractCustomerItemID value No Data Line: 14
2022-11-23:09:41:05 Extracted field RootCauseID value 0 Line: 15
2022-11-23:09:41:05 Extracted field SubjectLine with built-in value %Msg_Subject% CNC IM CWA - Offline Servers - ORBDC- Failure Line: 16
2022-11-23:09:41:05 Extracted field queueNo value 2 Line: 17
2022-11-23:09:41:05 Opened database using provider=MySQL;User Id=***;Password=***;Host=;Database=CNCTEST;Port=; Line: 18
2022-11-23:09:41:05 *-2147467259 Incorrect integer value: '' for column 'contractCustomerItemID' at row 1 From Insert Line: 18
2022-11-23:09:41:05 *Database update failed. Transaction rolled back. Line: 18
Appreciate any assistance in this, it's my first one and if I know how to do it I can do the others in future (hopefully!).
Thanks in advance
- 3 replies
- SStephen Parker @stephenparker
You can ignore the '%SystemCare% is used on line 13 but the variable has not been created' warning message. ThinkAutomation thinks you are referencing a TA variable in your SQL statement instead of hard-coding the LIKE value. We will fix this incorrect warning for SQL LIKE statements.
Remove the SystemCare SET variable action (its not needed).
You dont need the Extract Field action. So remove this also.
Simply create a variable using the Set Variable action called ContactCustomerItemID (before your lookup).
After the lookup this will contain the contactcustomeritemid column value returned from the lookup.
- MIn reply toMattA⬆:@MattA
Many thanks for the input, I adjusted accordingly and then had to change to use an Update a Database Using Custom SQL so I could add that variable in and it has successfully imported and updated the contract field as required.
Very much appreciated.