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 firstname.lastname@example.org. We are also available to create a Custom SQL script for you as a consulting service.
We’ve been tossing around the topic of segregation of duties within our team recently and one team member proposed a fun analogy. What if we explained segregation of duties within GP as if it were The Hunger Games!
We’re not suggesting you throw all your GP users into the conference room and see who makes it alive. Rather, humor us and pretend for the next five minutes that you find yourself in Panem, the futuristic civilization created by Suzanne Collins in her book trilogy.
For those that didn’t fly through the books or watch the movies, I’ll provide a little context. Panem is comprised of 13 districts. Each district is responsible for providing specific goods or services for the entire country, such as transportation, power, lumber, textiles, etc. This works because everyone sticks to their expertise and the result is harmony in the country. (That’s where the “Hunger Games” part comes in and things take a turn for the worse, but we don’t need to go there for this example.)
Just imagine what those 13 districts would look like if they were users in Dynamics GP…Payroll clerk, Payroll manager, Payables clerk, Payables manager, and so on. With so many users working on various overlapping tasks to keep the system running smoothly, it’s a wonder we ever keep anything straight!
Segregation of Duties is here to help create harmony between the metaphorical districts in your Dynamics GP system.
5 stages to harmony in Dynamics GP
1) Define the districts.
- For example, there are two districts in the system for Payables Invoices and Payables Checks.
- One district enters Invoices in Payables Transaction Entry and Purchase Order Entry.
- The other district enters Checks in Select Computer Checks, Payables Manual Payment
2) Create the districts.
- Based on the two districts listed above, two Group IDs would be created in Segregation of Duties. You can do this either manually, by importing in a Security Task, or by importing in a previously defined Segregation of Duty Group.
3) Define and assign the Exclusions.
- Because the Users in one district should not have access to the forms in the other district, an Exclusion ID needs to be created between the two of them.
4) Run and review the reports.
- The reports will help you identify where a conflict exists for the User(s) between the groups you have set up to be excluded from one another.
- The Segregation of Duties Conflicts report is per Exclusion and will tell all of the Users that have access to at least one of the Forms selected in each of the two districts that have an exclusion between them.
- The User Segregation of Duties Conflicts report is per User and Exclusion and will tell you when a User has access to at least one of the Forms selected in each of the two districts that have an exclusion between them.
5) Adjust Security as needed.
- Use Dynamics GP Security to change security where needed.
- Use Dynamics GP Toolbox Security Manager and Task Build to change security where needed.
For detailed steps on how to do this for a Payroll Clerk group and Payroll Manager group in Dynamics GP, see KB How to create a SOD Group ID, an Exclusion ID, and how to review the SOD Reports.
May the odds be ever in your favor!
If you have any questions, please email us. We would love to help!
Spring has sprung and Dynamics GP User events are in full bloom around the country and worldwide! Our sales team is lucky enough to travel to several of these events, meeting GP end-users, talking with them about their pain points in GP, and working together with these users and their partners to provide a solution.
We’re looking forward to spending time with our valued Partners and customers in the coming months. We’re even going international, attending the first ever Dynamics GP User Group in South Africa. Take a look at the events are on our spring schedule, we may be coming to a city near you!
Vision 2017 with InterDyn Artis
April 19th – Charlotte, NC
Vision 2017 is a day-long conference focused on educating attendees on how to get the most out of their Microsoft Dynamics software investment. Click here to learn more!
The Take Back Your Day Tour with Summit Software Group
April 27th – Omaha, NE
May 3rd – Duluth, MN
May 11th – Fargo, ND
Focused on Microsoft Dynamics GP, this year’s User Groups have been designed to help you get the most out of your software– saving you time and making your job easier. The purpose of the day is to transform the way you work—to expose efficiencies, reveal new features and showcase tips and tricks— we want to give you back time in your day to focus on other things (even if they aren’t work related). Click here to learn more!
The Future is Today – Spring GP User Groups with DFC Consultants
May 10th – Bismarck, ND
May 11th – Fargo, ND
Join DFC Consultants to discover how to embrace the technology you own and bring your business to the 21st century. Click here to learn more!
RG Connect 2017 with The Resource Group
May 11 – Bellevue, WA
This day-long conference is designed to help clients use technology, software and innovation to achieve business success. RG Connect 2017 will feature more than 20 sessions throughout the day focusing on how to utilize your existing solution to its fullest, gain more efficiency in your business processes and help you get to the information you need each day. The theme for this year’s event is “Innovate. Integrate. Elevate.” Click here to learn more!
Reignite GP with Braintree by Vox Telecom
May 22-24th – Gauteng, South Africa
Reignite GP 2017 is a peer-to-peer focused conference dedicated to education and networking. This first of a kind event in South Africa will bring together Microsoft Dynamics GP end-users, technical resources and partners in instructional sessions, showcases, and how-to’s, focusing on past, current and future versions of Dynamics GP. The event will take place from 22 to 24 May 2017 in Gauteng, South Africa with pre and post event training and education sessions with the main objective to educate and empower the Dynamics GP community in South Africa. Click here to learn more!
If you have an upcoming event this fall, we’d love to join you! Please send any pertinent event information to email@example.com.
I can’t believe it is here. I feel like I have been waiting for it forever. It’s been 10 years in the making, but finally, it’s here: Fabrikam Day!
I remember working at Microsoft and seeing this date in the sample company and thinking, wow, that day is so far away. I mean, we did see this day before in 2007 with The World Online, but 2017 just didn’t seem real. Now, we are ready to welcome it with open arms and patiently wait for the date to change to April 12, 2027. (Rumor has it that won’t be happening in the GP2018 release, but I look forward to the one after that.)
For those of you who don’t know what I am talking about, let me explain. Fabrikam is the sample company in Microsoft Dynamics GP. At one point, when the test company was The World Online, the date was 4/12/2007, but that date came and went and a new date was set for the sample company – 4/12/2017. Someone at Microsoft picked this arbitrary date to be the default. Little did they know how exciting it would be when that date rolled around. Or maybe they did know and have big plans themselves.
Having been in the GP world for quite a few years now, I have seen the true value Fabrikam has been to Microsoft Dynamics GP users. You just install the sample data when you install the product and you are off and running. You can test, create scenarios, and practice daily activities in real life. My personal favorite sample data is Baker’s Emporium and 128 SDRAM. I used them all the time. If I were training, the class always got excited when I used something else. But those two records will always be special. Maybe for Baker’s Emporium it was because most people didn’t move past the A’s so I wanted to give a customer in the B’s a chance. I love an underdog.
So here we are, on the brink of Fabrikam Day and I couldn’t help but wonder if my other team members were as excited as I was. So I asked. I wanted to know what Fabrikam Day meant to them and what their plans were to celebrate the important day.
Here’s what they had to say:
(An alias has been used in some cases to protect the innocent.)
For me, Fabrikam Day recognizes the community of partners, customers, ISVs, and Microsoft people with whom I have built long lasting relationships. I have new friends, and I don’t just mean on Facebook, but true friends I spend time with and get to know even better. That’s the best part of this community!
I’m looking forward to Fabrikam Day so I can celebrate with my team, reflect back on my years in the Microsoft Dynamics GP community, and look forward to what is coming in the future. Now, if only I can get our employer to recognize this as an important holiday and have the day off.
Happy Fabrikam Day everyone!
A couple of years ago I wrote a blog called, Give a Support Team a Script Log in Microsoft Dynamics GP. That blog outlined three methods for how to help your support technician help you troubleshoot your problem by providing them with the best script log. Since that blog was published, Rockton Software has added a new window to our products which further simplifies the process of creating a script log using two of the three methods mentioned in that original article.
There are many types of log files, such as DexSQL logs, SQL Profiler trace files, and Windows Event logs, and they all contain different types of information. Sometimes one log is appropriate when another is not. Your support technician will let you know which type of log is needed.
A script log is a text file that lists all of the procedure calls that are being executed as the software runs inside of Dynamics GP. This can be invaluable to a developer who may get called in on tough cases, because she or he can compare the calls in this script log to the calls in their program source code. This will likely allow them to determine, with quite a bit of precision, what the program is doing behind the scenes.
Creating the best script log
The general idea in creating an efficient script log is that you want the log file to be as small as possible. At first, this may seem counterintuitive; after all, why not give as much information as you can? The reason is that these logs generate lots and lots and lots of data. If you can narrow down the log so that it contains just the activity that causes the error to occur, then you will save your support technician from having to slog through pages and pages just to find where to start looking. For this reason, there are three very important things to keep in mind when creating your log file:
- Begin logging at the last possible moment before the error occurs.
- While logging is active, perform the fewest possible actions necessary to make the error happen.
- Turn off logging as soon as possible after the error occurs, without doing anything more than is absolutely necessary in the application.
Following the above guidelines will, in most cases, give the support technician exactly what they need to quickly isolate where in the software the error is occurring.
Creating a script log from Rockton Software products
Two methods for creating a script log that I described in my original blog post (The built-in script logger and Starting script logging from the login) have been simplified and improved upon by means of the Enable or Disable Script Logging window.
The Enable or Disable Script Logging window can be found in the latest builds of our Dynamics GP Toolbox and SmartFill products. At the time of writing this blog, we are finishing up a release of our Omni Price product which will also include it. The next time we release Auditor and Dynamics Report Manager, you will see it in those products as well.
You can access this window by first opening the Setup window of one of our products, then clicking on the Troubleshooting tab. Click on the option labeled, “Enable or Disable Script Logging”, which you will see near the bottom of the list.
So, what are the benefits of this new window? Read on!
Immediate script logging
Follow these steps when you need to start logging at some point after you have logged in:
Open this window and have it off to the side so you can use it when the time is right.
- Perform whatever actions you need to perform to recreate the error, not including that final step that causes the error to occur. Keep in mind the three crucial steps in the Creating the best script log section, above.
- On the Enable or Disable Script Logging window, choose Immediate for Log Type.
- Enter or browse to a path and filename to use for your script log.
- Click the Start Logging button.
At this point, everything that you do in Dynamics GP will be logged to the file that you specified in step five. This is why you should not start logging until the latest possible point before the error occurs. Of course, if the support technician gives you specific instructions for where to begin logging, then follow those instructions. But in the absence of that, you should try to start logging as late as possible.
Once the error occurs, and you have captured everything that you need to capture, click the Stop Logging button, which will now be enabled. In some cases, you may not be able to choose this option because there is some error message or other dialog that is requiring you to respond to it. Go ahead and respond. Then turn off script logging at the earliest opportunity. Again, the objective is to put as little as possible in the log file, so don’t do anything that’s not absolutely necessary before you stop script logging.
It is a good idea to keep good notes for everything you did and what happened while script logging was active. This will also help your support technician understand what they will be seeing in the log that you are sending them.
As you can see, this process is much easier than updating the Dex.ini and logging out of and into Dynamics GP. It also does not involve adding that Debug menu, which would possibly be confusing to other users in a Citrix or Terminal Server environment.
Starting script logging from the login
If your situation requires you to start logging during the login process, you can use this same window to do that. Follow these instructions:
- Open this window and choose At Next Login for Log Type.
- Enter or browse to a path and filename to use for your script log.
- Click the Start Logging button.
- You will see a message telling you that logging will start the next time you log in to Dynamics GP.
- Exit Dynamics GP, then log back in again.
For this next login only, the script log file you specified will automatically be created in the specified folder. To turn off script logging, you can do any of the following:
- Choose Disable Script Logging from the product’s Setup menu
- Press Ctrl + Alt + Shift + D.
- Open the Enable or Disable Script Logging window and click the Stop Logging button.
Using this method allows you to specify the filename and location to use for your script log file.
We want your feedback!
Please let us know if we can clear up anything that might be confusing or if you have any other information that would be useful. And as always, if you have any suggestions for future blogs then please, suggest away!
We start almost every demo of our most popular product, SmartFill, by posing the same question to Dynamics GP users: How many times a day do you click on the magnifying glass icon?
The reply is usually a jumble of responses which include, but are not limited to, “a lot” and “too many”, always accompanied by a chorus of moans and groans.
Those frowns are quickly turned upside down when users see the time-savings SmartFill can provide. With SmartFill, a google-style search tool, users simply type in the information they know, and with the click of a button, the matches will be listed right in front of them. Many have referred to SmartFill as a must-have product, and we couldn’t agree more!
It’s never been easier to get started with a free trial. Click here to watch a quick video on the simple & easy SmartFill install. And then let SmartFill do the work for you!
If you have any questions, please reach out to our sales team at firstname.lastname@example.org.
Another FUN-tastic Rockton Software retreat is in the books! For this installment of our annual winter retreat, the entire Rockton team descended on picturesque San Diego, California. Our seven team members that a hail from Fargo, ND enjoyed the California climate most, as it was a welcome change from the negative temperatures of the frozen tundra.
The game plan for the week was a familiar one: work hard brainstorming, collaborating, and discussing topics as a team during the day, and play hard as a team during the evenings. As luck would have it, our first two days in The Golden State were rainy days, which tempered the urge to get outside and explore just a bit.
The sun showed back up on Tuesday night, just in time for a team outing to La Jolla for a cooking demonstration and delicious meal from a French chef. Pasta, beef tenderloin, and bread pudding will never be the same!
On Wednesday, we headed to the Gaslamp Quarter in San Diego for another scrumptious meal at Lou & Mickey’s. With clear eyes and full stomachs, we then proceeded to The Shout! House, a dueling piano bar. This was a fun experience for all and we highly recommend it for a night out. There’s no doubt you will be thoroughly entertained! It’s a shame our team doesn’t like to sing or dance… (insert wink emoji here).
By Thursday, the team had worked so hard that we earned early release and jetted over the San Diego Bay via the Coronado Bridge for an afternoon at Coronado Beach. A few splashed in the waves while others simply took in the spectacular scenery. After watching the colorful sunset, we finished our evening with a seafood spectacular, courtesy of the Bluewater Grill.
Unfortunately, all good things must come to an end, and on Friday we wrapped up our meetings, said our goodbyes, and the California sun set on another productive, inspiring, and rejuvenating Rockton retreat!
We can all admit that bad habits seem to work their way into our daily routines, even when it comes to tasks in Dynamics GP. Well, we’re here with good news: it’s not too late to implement a few New Year’s Resolutions to quell those habits and enhance your day-to-day use of GP! Let’s break it down by activity in Dynamics GP.
Bad Habit: Auditing everything.
Resolution: It is very important to Audit what you need audited rather than setting up Auditor to audit everything because you are unsure of what to audit. To help with this process, you will want to review the processes done in GP that your departments perform. You will also want to work with the experts in those departments to help you identify areas you need to audit. Here are some categories to help you and the experts identify areas you need to audit.
- Preventing fraud. For example, it may be important for your company to track changes to the Vendor Check Name field in the Vendor Maintenance window to help prevent potential fraud from happening. Auditing this change will help you identify the user(s) that is trying to write a check to someone else.
- Training opportunities. For example, it may be important for your company to audit changes to the Vendor Hold field in the Vendor Maintenance window because it keeps changing from hold to not on hold and you cannot figure out who or what process is changing it. Auditing this change will give you the information you are looking for and help you train that user(s) to only make the change when appropriate.
- Auditing for Auditor requirements. For example, it may be important for your company to audit who enters and who posts a transaction in the General Ledger. You can utilize the Journal Voucher Roadmap to do this. There is no set up for this. You just need to install and register Auditor. *Note: Auditor needs to be installed on all workstations that have GP installed on them. To learn more about the Journal Voucher Roadmap, click here.
Once you have identified what you need to audit, create your Audit Groups.
- Click here to watch a video on how to create an audit group to track PM Vendor Master File information & view Auditor Record.
- Click here to see a KB article on auditing General Ledger Journal entries and who approved the Batch ID.
Bad Habit: Not deploying SmartList Designer reports to Excel for the non-GP Users who need to see your Auditor SmartList reports.
Resolution: Click here for steps on How to Create an Auditor Report for Non-GP Users.
Bad Habit: Manually editing prices on Omni Price contracts instead of using the Omni Price Contract Mass Update utility.
Resolution: Click here to see how easy it is to update your Omni Price Contract prices using the Contract Mass Update utility.
Bad Habit: Not creating a Dynamics Report Manager (DRM) Launch Point for scheduling reports to run automatically.
Resolution: Click here to see how to set this up in DRM. A video is also included.
Searching in Dynamics GP
Bad Habit: Clicking the lookup button and scrolling through hundreds of master records.
Resolution: Enter part of the value you are trying to look up, tab off the field, and let the magic of SmartFill do the work for you! Click here to see a video on how it works.
Bad Habit: Forgetting to use Mentor to quickly change security for a User.
Resolution: Click here to see steps on how to use Mentor to quickly change security for a User. A video is also included.
While you may or may not be guilty of these habits above (no one is pointing fingers), we hope you’ve gleaned a few tips that will improve your everyday work in Dynamics GP. Here’s to a year of new habits inspired by efficiency and streamlined processes!
Rockton Software celebrated another successful year in 2016, which was made possible by all of our exceptional Partners! We want to recognize a few of our Partners by name for their outstanding effort in 2016.
Our top performing Partners were selected based on pre-determined criteria and have focused on selling our Microsoft Dynamics GP Add-On Product suite (Auditor, Dynamics GP Toolbox, Dynamics Report Manager (DRM), Omni Price, and SmartFill) all over the world!
“After reflecting on the past year, I am again reminded what makes working in the GP Channel so amazing– our Partners! I have had the pleasure of working with some of the best in the industry so many times throughout 2016 and I am so honored to call many of you not only Partners, but friends! Thank you for constantly championing Rockton to your client’s and colleagues – we couldn’t ask for better!” – Alicia Weigel, Technical Sales Team Lead
2016 Partners of the Year
- Australian Partner of the Year: Professional Advantage
- Canada Partner of the Year: Cogsdale
- EMEA Partner of the Year: Braintree by Vox Telecom
- United States Partner of the Year: RSM US LLP
We extend our sincerest thanks to all of our amazing Partners and we look forward to another fantastic year of building relationships and strengthening our partnerships in 2017!
As we wish each other a Happy New Year, it’s easy to focus on one word from that simple three word phrase: new. New resolutions, new work-out routines, new adventures, new goals, etc. Here at Rockton, we didn’t want to miss out on the excitement that accompanies this time of year, which is why we’ve started off 2017 in the best way we could think of: adding new tools to our Dynamics GP Toolbox!
Throughout our 18 years in the Dynamics GP channel, we’ve always let user feedback drive our development schedule. We are dedicated to making your work simpler & easier® in Dynamics GP, and our latest release is no exception. With our two new tools, we have addressed recurring frustrations that have been expressed by our customers and everyday GP users.
- Batch Review Window – One central location to view, approve, edit, post, and manage all batches across your major GP modules (GL, RM, SOP, IV, PM, POP)
- Security What If? – Ever wonder what the impact will be when you make a security change? Review security changes before making them
We’ve added the new Batch Review Window and Security What If? tools to an already powerful collection of 25 tools that help users with everything from administration, to security, to business processes, to enhancing the complete user experience within Dynamics GP.
That’s right, now 27 tools in one convenient package. You can check out the full list here. But, don’t just read about it, actually give it a try with our FREE 30-Day Trial. And here’s the cherry on top: if you trial the new Dynamics GP Toolbox, you’ll receive a 17% discount in honor of the new year, if you purchase the product by March 31st.
Want to learn more? Join us on Tuesday, January 24th for an in-depth look at the latest additions to our Dynamics GP Toolbox with our development and support team! Click here to register for the webinar.
Send this to a friend