Prompting for data value
gparker asked 10 years ago
Hello,
I was wondering if anyone has ever created a map that had SmartConnect prompt for a value and pass it in as part of the Data Source. Take for example a customer in another system – say you have a map that creates or updates new customers in GP. The datasource is looking at another source and I want it to prompt the user running the map for the customer number. That way it is clear what customer you are creatingupdating in GP.
Seems simplistic but that value entered in the prompt could be put into the where clause or restriction of the datasource. I see there are system and user variables but what if you wanted to create your own and having prompt when the map is run. I tried to use the example for prompting for a text file but could not get the script example to validate.
Any advice is appreciated.
Thanks,
Greg
I was wondering if anyone has ever created a map that had SmartConnect prompt for a value and pass it in as part of the Data Source. Take for example a customer in another system – say you have a map that creates or updates new customers in GP. The datasource is looking at another source and I want it to prompt the user running the map for the customer number. That way it is clear what customer you are creatingupdating in GP.
Seems simplistic but that value entered in the prompt could be put into the where clause or restriction of the datasource. I see there are system and user variables but what if you wanted to create your own and having prompt when the map is run. I tried to use the example for prompting for a text file but could not get the script example to validate.
Any advice is appreciated.
Thanks,
Greg
Answers
Best Answer
Lorren answered 10 years ago
kevin replied 10 years ago
Lorren answered 10 years ago
Greg,
1. Create a Global Variable. Maintenance->Global Variables. For example GBL_PROMPTFORVALUE
2. In your mapped data source, it will have to be a data source that creates a query, such as an ODBC Connection to SQL or Excel. The query syntax may end up being a little different depending on the source itself.
3. In your query, use the Global variable created in step one. Here is my example.
SELECT * FROM PA30101 WHERE EMPLOYID = 'GBL_PROMPTFORVALUE'
SmartConnect will replace the value we define in our prompt script, so if you are looking for a non-string value, then you can eliminate the Single Quotes. Again, Excel or other ODBC connection may have a different syntax by the replacement process is the same regardless.
4. Create a Pre-Map task
1. Create a Global Variable. Maintenance->Global Variables. For example GBL_PROMPTFORVALUE
2. In your mapped data source, it will have to be a data source that creates a query, such as an ODBC Connection to SQL or Excel. The query syntax may end up being a little different depending on the source itself.
3. In your query, use the Global variable created in step one. Here is my example.
SELECT * FROM PA30101 WHERE EMPLOYID = 'GBL_PROMPTFORVALUE'
SmartConnect will replace the value we define in our prompt script, so if you are looking for a non-string value, then you can eliminate the Single Quotes. Again, Excel or other ODBC connection may have a different syntax by the replacement process is the same regardless.
4. Create a Pre-Map task
Try
Dim s As String = Microsoft.VisualBasic.Interaction.InputBox("Enter Employee ID","Employee Select")
GBL_PROMPTFORVALUE = s
return true
catch
return false
end try
5. When you run the map, you will be prompted to enter the value which will be used in the data source query.
I hope that helps.
Lorren
5. When you run the map, you will be prompted to enter the value which will be used in the data source query.
I hope that helps.
Lorren
kevin replied 10 years ago Please note: there is currently a 3 minute timeout setting on scripts running in SmartConnect tasks. If the user takes longer than 3 minutes make a selection the map will fail due to timeout issues.