No internet connection
  1. Home
  2. Questions

Extract Field from Lookup From a Database

By @MattA
    2022-11-23 10:01:19.370Z


    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


    Solved in post #2, click to view
    • 3 replies
    1. S
      Stephen Parker @stephenparker
        2022-11-23 10:26:36.423Z

        Hi Matt,

        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.


        Reply1 LikeSolution
        1. S
          In reply toMattA:
          Stephen Parker @stephenparker
            2022-11-23 11:16:47.899Z

            You can also use SQL parameters instead of hard-coding the %like% value.

            For example:


            1. M
              In reply toMattA:
                2022-11-23 12:44:27.362Z

                Hi Stephen,

                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.