Skip to content


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!
Ethan Sorenson Staff answered 4 years ago
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.

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