Global Variable Value not showing up in Email
Pieter du Toit asked 5 years ago
I created a map that reads a value from an Excel spreadsheet, then runs a simple SP to update a value in a table. The value coming from Excel is the Doc ID and the SP finds that Doc ID in a specific table and add the word “ Void” to the end of the invoice number associated with that Doc ID. The map works well and I have set up an email under Document Tasks>Success to send an email using the _DocID variable to say “Invoice for _DocID changed to Void”. This also works fine. What I would like to do though is to show the new invoice number in the email so it will look like this: “Invoice for _DocID changed to 123456 Void”. To do this, I created a global variable (New_Inv_No). I then created a SQLCommand task in the map to run a select statement on the table (Select [Invoice Number] from [dbo].[AP_Main_Form] where [DocID] = _DocID) and marked it to send the result to the global variable that I created. The script runs fine in SQL if I replace _DocID with a real number. I run this task before I generate the success email. The problem is that it does not seem to populate the variable with the new invoice number so if I use it in my email message, it just shows up as a blank (“Invoice for _DocID changed to ” instead of “Invoice for _DocID changed to 123456 Void”). If I create the email in the Map Post Task>Success area it works but you cannot show the _DocID variable in that section so now I have a problem to the other side. Any ideas that I can try? I hope this all makes sense!
Answers
Ethan Sorenson Staff answered 4 years ago
Pieter,
It sounds like your task to run a SQL Select statement is failing to retrieve the DocID.
Make sure you are using single quotes around variables in SQL tasks as shown below.
Select [Invoice Number] from [dbo].[AP_Main_Form] where [DocID] = ‘_DocID’
If you don’t then the SQL task will look for the text _DocID.
It sounds like your task to run a SQL Select statement is failing to retrieve the DocID.
Make sure you are using single quotes around variables in SQL tasks as shown below.
Select [Invoice Number] from [dbo].[AP_Main_Form] where [DocID] = ‘_DocID’
If you don’t then the SQL task will look for the text _DocID.