WebSan Blog (121)
TSQL doesn't handle multiple transactions very well. In fact it doesn't handle it at all. So what do you do when you have nested stored procedures, all which require transaction handling?
TSQL has a built in function that will return the number of BEGIN TRANSACTION statements that have been called on the current connection.
Using this you can choose whether to begin a transaction or not based on the value it returns. If it returns a number greater than 0 then you are currently in a transaction.
However if it returns a 0 then there were no other transactions and you are free to start one. The best way to do this is to set a variable that indicates if a transaction is needed, since when you start your new transaction and you try to check again when you need to do a rollback or commit, @@TRANCOUNT will not return 0 since you recently started a transaction.
You can easily do it like this:
IF (@@TRANCOUNT = 0)
SET @transReq = 1
SET @transReq = 0
IF (@transReq = 1)
--Insert Code Here
--If no error
IF (@transReq = 1)
IF (@transReq = 1)
With this you can have multiple nested stored procedures and you won't have to worry about nested transactions errors.
By: Dustin Yee, Application Developer, WebSan Solutions Inc. a Microsoft Dynamics GP Partner and 2013 Canadian Channel Elite Awards Finalist.
As a follow up to my EFT for Dynamics GP 2010 blog this week, I thought I’d share a few common setup issues I’ve learned to overcome through my experience with various clients. It’s no secret that when dealing with financial institutions, time is often of the essence. Reversing an incorrect payment or attempting to stop payment to a certain vendor often requires hours of effort over the phone with representatives who may or may not be immediately helpful. As such, the following covers a few common setup issues when first setting up EFT for Dynamics GP 2010.
Having the Wrong Address ID Specified on Vendor Cards
One of the most common issues I’ve had clients ask me is why banking information for certain vendors shows up blank on generated EFT files. They’ve setup their file format correctly as per the specifications from their financial institution, setup the vendor card correctly to match the EFT file format (Where information is pulled from), and the transaction is posted and included in the EFT run with no issues. However when they generate an EFT file and upload to their financial institution website, they come to realize there is no financial information specified on the file.
This is a common issue which at first is a bit tricky to spot however can be resolved fairly quickly. Your first step is to double check whether you have actually entered banking information on the EFT BankMaintenance window. This may sound unnecessary (and obvious) however there are often cases where clients have two vendors with very similar names where one has EFT information specified versus the other which does not. To access the EFT Bank Maintenance window:
- Open the Vendor Maintenance window from the Purchasing Module in GP
- Lookup the vendor by Vendor ID and select the Address tab located at the bottom right of the window
- This will open the Vendor Address Maintenance window. Select the EFT Bank option to open the Vendor EFT Bank Maintenance window
- Here you will find all financial details from which your generated EFT file is pulling data from
If there is data specified here as you originally anticipated, close these newly opened windows and return to the Vendor Maintenance window. This brings us to the originally mentioned issue causing blank financial information to appear on your EFT file. While on the vendor card, check the Address IDs specified for different actions in GP as shown below:
If the Address ID specified on the vendor card for the Remit To field is different than the Primary Address ID specified on the vendor card, this is most likely the cause of blank information appearing in your EFT file. When you specify EFT information for a vendor, the information is saved based on the Address ID specified. Therefore if the Primary Address ID for a vendor is called Primary and the EFT information for this vendor is specified for this ID (You can check by selecting Address> EFT BANK – If the information is entered, this is the ID associated with these details). Thus, as you can see from the screenshot above, the Remit To field is referencing an Address ID for which the EFT information has not been entered. This is why GP is pulling blank information into the EFT file as it is looking for banking details specified for the Main address ID – which do not exist. Change this field to an Address ID with the details specified and this should solve the issue.
EFT File Formats for Different Companies
Another issue many clients tend to face when processing EFT within Dynamics GP 2010 revolves around specifying File Formats for every company setup with GP. It is not uncommon for companies to have more than one company setup within GP. As such, the need to process EFT in different companies requires a file format to be setup for each company. Specifying file formats manually can often be a time consuming task (GP provides you with some ready-made templates to choose from based on your region however depending on your financial institution you may have to manually specify this). Clients often find themselves making a slight error when manually specifying file formats which leads to their uploaded file being rejected by their financial institution. A solution to this is a neat feature within GP to Import/Export file formats.
Once you have a company with a File Format matching the exact requirements of your financial institution, log into that company and select EFT File Format from the cards menu in the Financial Module. Select the EFT Format ID you wish to copy to another company from the lookup menu. Once you have selected the file format, select the Import/Export tab to export the existing format.
Note – You will need to select a location to export the file format to in the field specified below:
Once you have successfully exported the existing format you wish to copy to a new company within GP, log into the new company and open the EFT File Format Maintenance window (Financial > EFT File Format). Enter a new EFT Format ID for the new company and select Import/Export to select the file previously exported. Once successfully imported, the new company will now have the same file format as your original company. This could potentially save you hours manually entering the same file format in multiple companies and avoid potential mistakes.
Praveen Parameshwaran, Application Specialist at WebSan Solutions Inc. and has helped multiple clients successfully implement EFT Processing within their Dynamics GP 2010 environment.
It will surely be!
Many business owners that are in the process of expanding their company will need a bigger and better ERP (enterprise resource planning) system to handle day-to-day business operations. According to a report called “ERP – The Cuckoo in the Cloud Land”, 84% of respondents said that they used at least one form of ERP. More businesses are moving to the cloud for a number of reasons which can include: Lowering the cost of ownership, becoming eco-friendly and reducing dependence on internal IT personnel.
The report also advocates 2014 will be the year of the ERP cloud. According to survey respondents, 14% have fully migrated an ERP system to the cloud, with the majority (31%) saying they will migrate in the next 12 months. A further 27% said they’d be in by the end of 2015.The report also conducted a survey of critical areas of the organization that were important to business owners and SQL databases got 69% of the vote, followed by ERP (66%) and accounting and finance apps (66%),Business intelligence (48%) and CRM (40%).
At WebSan Solutions, we offer an online (SaaS) hosted-enterprise business software solution, leveraging the vast capabilities of Microsoft Dynamics GP. Microsoft Dynamics GP (formerly Great Plains) is one of the top-rated accounting and Enterprise Resource Planning (ERP) software solutions on the market today.
Not everybody is convinced about moving to the cloud, but in time that will change. If you have any questions about ERP Cloud or migrating to the cloud, contact us for more information.
To read more about ERP Cloud facts click here.
By: Natalie Williams, Marketing Coordinator, WebSan Solutions Inc, a Microsoft Dynamics GP Partner and 2013 Canadian Channel Elite Awards Winner
If you have a lot of data that doesn’t quite fit into one of Dynamics CRM 2011’s existing entities, you may decide to create your own custom entity and fields to house that data of yours. If you take this route, don’t waste your time building the entity and creating all of your fields ahead of time! Instead, you can use CRM 2011’s Import Data Wizard, which allows you to create entities and fields on the fly as you import the data for this new entity.
After selecting the file you want to import in the Import Data Wizard, you will reach the Map Record Types step of the Wizard – here, you can select to “Create New” Dynamics CRM Record Type, which will prompt you for more information to create the new entity within the Wizard. Next, you’ll reach the Map Fields step – here, you can dynamically create new fields for each column in your import file (or ignore fields that you don’t want to bring into CRM). While doing so, it even defaults the Field Name to match the column header from your import file, so all you really have to do is select the type of field that it is. And if that wasn’t enough – if you select “Option Set” as the field type, the Wizard will automatically create values for your option set based on the unique values in that column of your data file! The only thing left to do at this point is to set any additional options on your new entity as necessary and configure the entity form that will be used to manage the data – both of which can be done through the usual customizations window.
So if you find yourself needing to create a new entity along with having to import your data into this new entity, save yourself some time by combining both steps into one using this helpful trick in the Microsoft Dynamics CRM 2011 Import Data Wizard.
When developing Dynamics GP customizations in our WebSan environment, our development phase is contained within a local server configuration to ensure that all modifications to the forms and reports dictionaries are local and will not affect our hosted users. However, there are a few more items to note when separating a development from a production environment.
When enabling and granting access to modified forms and reports in GP, administrative users would navigate to the Microsoft Dynamics GP menu >> Administration >> Alternative/Modified Forms and Reports and grant access to the modified item by selecting the ID, Product, and Type (Windows: Forms or Reports), then checking the box beside the version they would like accessible:
The ID field at the top of this window is used to specify which group of users has access to the forms and reports chosen in the list underneath. Users are assigned an “Alternate Modified Forms and Reports ID” in the ‘User Security’ window:
Therefore, if you are customizing the ‘Payables Transaction Entry’ window and want to ensure that current GP users cannot view these changes until they are completed, you can add your own GP login to a new “DEV” group that has the customized form selected such that further work and testing can be performed without effecting production users. While the ID field handles what version of the form or report the user can view, any VBA code that is applied to the customization is made accessible through the “EventMode” property in the ‘Visual Basic Editor’ (Alt+F11), if the form or report has been added to VBA.
Within the VBA code, you can select the form or report that is using VBA and set the EventMode property to either “0-emOriginalOnly”, “1-emModifiedOnly”, or “2-emNever”. The first applies the VBA code to the original (un-modified) object only, while the second applies it to the modified version. The third ensures that VBA code is not run when that object is accessed:
By utilizing these two properties, it is possible to contain a customization to only a particular group of users until testing is completed, and is ready to be deployed to a production system. If development is occurring within a production environment, configuring these two settings will ensure that other users are unaffected.
Saturday’s ice storm was one of the worst ice storms in Toronto’s history. A utility company described the ice story as‘ far worse than imagined. Forestry crews have been brought in to help cut up and move massive tree limbs littering streets and roadways. Over 225,000 homes and businesses are still without heat and light across the city. The city is currently working to restore power and heat across Toronto. (Source: metronews)
It was business as usual here at WebSan Solutions! We experienced no issues throughout the storm and our backup generating system was on standby if needed. Our Microsoft Dynamics GP Hosted environment was up and running without a hitch. You always have access to your data, through our secure network, in our SAS70/SSAE16 certified data centre!
WebSan Solutions is dedicated to helping all businesses make better business decisions by improving their access to important company data and information, in rain or shine!
Natalie Williams, Marketing Coordinator, WebSan Solutions Inc., a Channel Elite Awards Winner for 2013
The Top 100 are chosen from organizations specializing in the sale and implementation of Enterprise Resource Planning and accounting software.
The selection of mid-market accounting software resellers for Bob Scott’s VAR Stars rest on one basic principal: quality, not revenue, as the deciding factor in picking the 100 businesses that are selected for this honor.
A variety of criteria go into the definition of quality including including awards resellers have received from vendors, such as the Microsoft Dynamics President’s Club and Inner Circle, Million Dollar Club and Chairman’s Club of Sage North America. Other factors involved in the selection include performance, contribution to the industry and reputation.
“We are pleased to be recognized as a Top 100 VAR” said Andrew King, Managing Director.
Natalie Williams, Marketing Coordinator, WebSan Solutions Inc., a Canadian Certified Microsoft Partner