SmartFill makes searching for a specific record in Microsoft Dynamics GP super easy. But what do you do when you have a more complex lookup required or a custom table? In that case, you may create a Custom SQL Object to do what you need. SmartFill has information available on creating a Custom SQL Object. Appendix D in the SmartFill User Guide is very helpful. Click here to download the SmartFill User Guide.
Let’s take this a step further though. Perhaps you have already created a Custom SQL Object for a specific table and it is working great when you type a word and search. In order to make the lookup process easier though, you want to allow the users to type in two words to search on in SmartFill. To do this, you will need to add the tokens to the Custom SQL script. See Sample Script 1.
Or, in another SmartFill Object, you have the correct results appearing. However, there are multiple records appearing for the column you need to select. For example, the field you need to select is the Customer ID and based on the table linking you have done, the Customer ID is appearing multiple times. You would like it to just appear once in the list. You can do this by adding the Distinct command to the Select statement in a Custom SQL script. See Sample Script 2.
To use either of the scripts below, you will need to modify them a bit to meet your needs. After it is modified, run it against the DYNAMICS database.
In Microsoft Dynamics GP, be sure you are using the ‘Search With Each Token’ option. To set this, follow these steps.
- On the Microsoft Dynamics GP | Tools | Setup | SmartFill | Objects.
- Double-click the Object you are working with.
- In the SmartFill Object Maintenance Details window, click Advanced.
- Mark Search With Each Token and choose OR or AND.
You are now ready to use SmartFill and search using more than one word.
Of course, if you have any questions or need guidance, contact support@rocktonsoftware.com. We are also available to create a Custom SQL script for you as a consulting service.
Resources:
– Download Sample Script 1
– Download Sample Script 2
