We have just added some new, fun stuff to our
In addition to bug fixes, we've added the following new goodies to Omni Price:
- Contract Override
- Improved performance inside Dynamics GP using the SQL API
- Show selected Contract without using the alternate SOP windows
- Archive Contracts and Transaction Log to History
There are a few other minor enhancements as well, which you can review in the Readme once you download the product. We have also added a What’s New option to the Setup menu so that you can easily review the new features at any time. Going forward, we will do this with our other products as well.
There are times when a salesperson may want to give a customer a one-time, special deal on a particular item. For these situations, we have added a new feature that will allow you to override the Contract that Omni Price had previously selected. You can turn this on by selecting Allow overriding Contracts during Sales transaction entry on the Pricing Setup window:
Enabling this feature will allow you to select a new Contract from a lookup window or from the Price Check window, or if you know the Contract ID you want to use, you can even just type it in.
The Pricing Setup window lets you supply an optional password that the user must enter when doing an override. This lets you restrict the ability to override to only the people that know the password.
Improve performance inside Dynamics GP using the SQL API
The pricing engine in Omni Price is a complex thing. It is so complex that Customers that have thousands of Contracts may sometimes experience a lag during transaction entry as Omni Price determines which Contract to use. Even if you don’t have a huge number of Contracts, you may have seen that the Price List report can take many hours to process. Both situations can be helped by installing the SQL API that we have on our website and then marking the Use SQL API for pricing lookups option on the Pricing Setup window:
For those of you unfamiliar with Omni Price’s SQL API, here is a little bit of technical background. We introduced this set of SQL function calls in 2007 so that other software products would be able to determine Contract pricing without having to make Dexterity-based calls into Omni Price’s dictionary (API = Application Programmer Interface; in other words, it’s a way for one program to “talk to”, or interface with, another program).
This was necessary because many of these products or import tools were not created using Dexterity, and back in 2007 there was no way for them to talk to a Dexterity-based product. While this SQL API has served the original purpose well, it turned out that this SQL code was a lot faster than the Dexterity code that was being used inside of Microsoft Dynamics GP. So, we have decided to make use of this faster SQL code inside of Dynamics GP as well.
If you have not installed the SQL API before, just go to the
You will then extract the contents of this downloaded zip file and open the file with the extension of .sql in SQL Server Management Studio. Run this entire set of SQL code on each company database where you have checked the Use SQL API… option above. That’s it! (Well, you should actually refer to the comments at the top of the SQL script file and also the readme that was included in the download for more information; there’s an additional step if your system database is not named DYNAMICS).
If this all sounds complicated, trust me: anyone with any experience using SQL Server will be able to do this in their sleep. And then you’ll sleep better because your users won’t be complaining anymore about how slow transaction entry has gotten.
Show selected Contract without using the alternate SOP windows
Omni Price comes with alternate Sales Transaction Entry and Sales Item Detail windows that can be used to show the Contract ID that was selected during transaction entry. Unfortunately, you may only use one alternate version of a window at a time for a given user. If another product has an alternate version of one of these windows that you absolutely must use, then you can’t take advantage of this additional functionality that Omni Price provides.
To solve this problem, we have added the Selected Omni Price Contract pop-up window:
You can open this window by choosing Customer Pricing – See Contract from the Additional button in the ribbon, or by simply clicking Ctrl+Y. This window gives you the same ability to override a contract as the alternate window does.
Archive Contracts and Transaction Log to History
As time goes on, Contracts become obsolete, taking up space in the Contract master tables. They also potentially result in slower pricing lookups as the pricing engine must filter through many more contracts than it would if these Contracts were removed. However, you may not want to simply delete these Contracts if you still need to refer to them historically.
For this reason, we have added the ability to move (archive) old Contracts to History. Although they will now be stored in separate History tables, these Contracts will still be viewable on the appropriate Inquiry windows. Since it just seemed like the right thing to do, we also did the same thing for the Transaction Log table.
Some of you may be familiar with our Auditor product’s Table Archive/Purge window. We basically took that window, added some additional functionality, and then made it work with the Omni Price Contract and Transaction Log tables. You can open this window from the Omni Price Setup window, Setup tab, by choosing Omni Price Table Archive/Purge:
Comparing to the Auditor window, the main difference you will see on the initial window is that we have added a Preview area on the bottom of the window that will show you the records that you are about to archive or purge. You will also notice, of course, the Archive to History button in the ribbon.
Clicking the Edit Criteria button will open the Query Builder window. Here you will see a list of all the fields and several operator buttons that you can use to specify which records you want to move to history.
This window is where you will build a SQL WHERE clause that will be used as the selection criteria for the archive or purge operation. Using this window may require a little bit of SQL experience, but once you get the hang of it, you’ll find that it is simple, yet powerful.
Remove Pricing History
One other related change: we have removed the Remove Pricing History window that used to be found on the Utilities menu because you can now perform that exact same function (and much more) using the new Table Archive/Purge facility. On the old window, you were simply allowed to enter a date and all Transaction Log records before that date would be removed from the system. To perform this same function now, use the following procedure:
- Select Transaction Log on the Omni Price Table Archive/Purge window.
- Click the Edit Criteria button.
- On the Query Builder window, scroll the list of fields until you see the field called Date.
- Double-click the Date The Date field will appear in the Where Clause box at the bottom.
- Click the Less than or Equal (<=) operator button. It will appear in the Where Clause.
- Double-click the date in the Show Values list box at the right that you want to use as your cut-off date. This date will appear in the Where Clause.
- Click the Validate button in the ribbon.
- Click OK to close this window.
- Back on the Omni Price Table Archive/Purge window, choose the Purge button in the ribbon.
*Note that you can now choose to archive these same log records to history where they will still be accessible on the Contract Inquiry window. You can even use a more detailed set of criteria to specify the records that you want to act upon. Since purging your Transaction Log is not a commonly used function, we hope that the extra steps involved in doing so will be offset by the additional flexibility and functionality that this new procedure provides.
We Want Your Feedback!
Please let us know if you have any questions about any of the above new features or anything else in Omni Price that I didn’t mention. And as always, if you have any suggestions for future blogs then I would love to hear them!
Written by Jim Peliksza, Developer,