(773) 860-0643  

  • "It’s been really nice working with you, Jamie and Ruby! Thank you for your continued support to Frank, Rimerman Consulting."

    Beverly MCSM (GP Partner)
  • "Thanks for the late-night support the other night!... Client was operating as it should this morning!   WOOO HOOOO !"

    Maureen ACAE (GP Partner)
  • "Have I told Alba-Spectrum this year yet how F A S T the newsletter selection is now ???? Wow. Thank you very much!"

    Dennis Westbrook
  • "We were successful last night in completing the production deployment of Terminations WF. Ricardo was fantastic and we could not have done this without him"

    Tawna KProject Manager (Credit Management)
  • "[We are] so grateful for Jeff’s help, and Jeff has been so good to...ZTrim in all of his support."

    John ECFO (Agricultural Ingredient Technology)
  • "First off, thanks for all the great suggestions. I’ll let you know what we find out. Many thanks for your help"

    Mark BDeveloper (Telephone Company)
  • "I will e-mail you back to let you know I was successful with HJE – which I expect to be. I really appreciate how you helped me."

    Michele DurkinConsultant (GP Partner)
  • "...thank you both for taking some time out to help me troubleshoot the issue of creating a project directly into the GP tables without the benefit of using the web services."

    Jesse CPrincipal (GP Partner)
  • "For example, when an AE calls to confirm rates, there’s not a 30 second pause waiting for the billing schedule to open. It’s absolutely immediate, literally, click, click, click. It’s a huge difference."

    Vince FCredit Manager (Media Data Service)
  • "So far everything seems to be working fine. We have had it in production now for almost a month and have not experienced any problems."

    Mark MFinance Manager (Custom Woodwork)
  • "Alba will always be my choice for quality innovation on ANY project you put in their lap from websites to Great Plains implementations, customizations and people who think 'outside the box'. "

    Robert DCEO
  • "Rod, your team’s flexibility and quality of service have been appreciated."

    Nicholas NController (Financial Trading)
  • "I most heartily and sincerely recommend him and his firm for any software implementation task."

    John PCFO (Tourism Association)
  • "[the] team delivered several enhancements to Great Plains, significantly improving our sales order entry process, shipment routing, and handling of catch-weight items."

    Eddie HPresident (Food Products)
  • "You have come very highly recommended by other companies. I have been impressed with your professionalism and attention to detail."

    Timothy CPresident (Uniform Manufacture)
  • "I am very happy that Andrew was able to fix our invoice system. Your company is very professional and performs excellent service."

    Allen ZCFO (Oil Company)
  • "I don't know who your company president is, but you guys did great job and I hope he/she sees this email."

    Nathan M.Finance (Marketing Communications)
  • "I heartily recommend Alba to any developer or end user seeking to make the most of their GP investment."

    Brett GPresident (Software Development)
  • "Thank you for all your patience and hard work getting this all processed. Here at Friedr Dick Corp we are looking forward to a wonderful professional relationship."

    Jill DAccounting Manager (Home Furnishings)
  • "I think we are going to be a great team working forward on the Miami project."

    Jesse CPrincipal (GP Partner)
  • "As usual, superb product and service!! Everything is working great and all my users love the modification. Thanks again!"

    Michael LController (Healthcare Storage)
  • "[W]e thought your product GP Posting Server was the better solution. The interface is better, it is easier to understand, and there are more options considering the auto transfer feature. We compared it to the Envisage Post Master Enterprise software."

    Tom BSenior IT Administrator (Medical Implements Company)
Customer Feedback
  • "It’s been really nice working with you, Jamie and Ruby! Thank you for your continued support to Frank, Rimerman Consulting."

    Beverly MCSM (GP Partner)
  • "Thanks for the late-night support the other night!... Client was operating as it should this morning!   WOOO HOOOO !"

    Maureen ACAE (GP Partner)
  • "Have I told Alba-Spectrum this year yet how F A S T the newsletter selection is now ???? Wow. Thank you very much!"

    Dennis Westbrook
  • "We were successful last night in completing the production deployment of Terminations WF. Ricardo was fantastic and we could not have done this without him"

    Tawna KProject Manager (Credit Management)
  • "[We are] so grateful for Jeff’s help, and Jeff has been so good to...ZTrim in all of his support."

    John ECFO (Agricultural Ingredient Technology)
  • "First off, thanks for all the great suggestions. I’ll let you know what we find out. Many thanks for your help"

    Mark BDeveloper (Telephone Company)
  • "I will e-mail you back to let you know I was successful with HJE – which I expect to be. I really appreciate how you helped me."

    Michele DurkinConsultant (GP Partner)
  • "...thank you both for taking some time out to help me troubleshoot the issue of creating a project directly into the GP tables without the benefit of using the web services."

    Jesse CPrincipal (GP Partner)
  • "For example, when an AE calls to confirm rates, there’s not a 30 second pause waiting for the billing schedule to open. It’s absolutely immediate, literally, click, click, click. It’s a huge difference."

    Vince FCredit Manager (Media Data Service)
  • "So far everything seems to be working fine. We have had it in production now for almost a month and have not experienced any problems."

    Mark MFinance Manager (Custom Woodwork)
  • "Alba will always be my choice for quality innovation on ANY project you put in their lap from websites to Great Plains implementations, customizations and people who think 'outside the box'. "

    Robert DCEO
  • "Rod, your team’s flexibility and quality of service have been appreciated."

    Nicholas NController (Financial Trading)
  • "I most heartily and sincerely recommend him and his firm for any software implementation task."

    John PCFO (Tourism Association)
  • "[the] team delivered several enhancements to Great Plains, significantly improving our sales order entry process, shipment routing, and handling of catch-weight items."

    Eddie HPresident (Food Products)
  • "You have come very highly recommended by other companies. I have been impressed with your professionalism and attention to detail."

    Timothy CPresident (Uniform Manufacture)
  • "I am very happy that Andrew was able to fix our invoice system. Your company is very professional and performs excellent service."

    Allen ZCFO (Oil Company)
  • "I don't know who your company president is, but you guys did great job and I hope he/she sees this email."

    Nathan M.Finance (Marketing Communications)
  • "I heartily recommend Alba to any developer or end user seeking to make the most of their GP investment."

    Brett GPresident (Software Development)
  • "Thank you for all your patience and hard work getting this all processed. Here at Friedr Dick Corp we are looking forward to a wonderful professional relationship."

    Jill DAccounting Manager (Home Furnishings)
  • "I think we are going to be a great team working forward on the Miami project."

    Jesse CPrincipal (GP Partner)
  • "As usual, superb product and service!! Everything is working great and all my users love the modification. Thanks again!"

    Michael LController (Healthcare Storage)
  • "[W]e thought your product GP Posting Server was the better solution. The interface is better, it is easier to understand, and there are more options considering the auto transfer feature. We compared it to the Envisage Post Master Enterprise software."

    Tom BSenior IT Administrator (Medical Implements Company)

How a SQL View can be treated as a Dexterity Table

Introduction

When you have a custom application built in Microsoft Dynamics GP and the customer created a SQL View for you to get the information needed for the custom application, you have several options on how to code this in Microsoft Dexterity.  One of the options is to create a Dexterity Table that is mapped directly to the SQL View. This method allows the customer to modify the SQL View’s conditions without changing the Dexterity Table or the need to recreate a new chunk.

This post will explain the method on how a SQL View can be treated as a Dexterity Table.

Compatibility

Microsoft Dexterity 10

Microsoft Dexterity 11

Microsoft Dexterity 12

Development

For this example, first we create a SQL View that will only return all customers in the USA. Make sure to always include the column DEX_ROW_ID.

CREATE VIEW CUSTOMERS_USA

AS

SELECT CUSTNMBR, CUSTNAME, CUSTCLAS, ADDRESS1, ADDRESS2, DEX_ROW_ID

FROM RM00101

WHERE COUNTRY = 'USA'


Create the Dexterity Table.  The Physical Name of the table should match the name of the SQL View.  The fields added to the table should also have the same physical field name with the ones in the SQL View. DEX_ROW_ID does not need to be added anymore into the Table Fields section.

Creating Dexterity Table

Now you can use the SQL View as if it is a normal Dexterity table.  But since it is a SQL View, you cannot add or delete records. You can only access this for information purposes.  Also, the customer can change or add more conditions to the SQL View. In this example, we will add a condition on showing only customers that starts with the letter ‘A’.

ALTER VIEW CUSTOMERS_USA

AS

SELECT CUSTNMBR, CUSTNAME, CUSTCLAS, ADDRESS1, ADDRESS2, DEX_ROW_ID

FROM RM00101

WHERE COUNTRY = 'USA' AND CUSTNMBR LIKE 'A%'

 

Hopefully this post has given you an alternative solution on retrieving information from SQL Views as Microsoft Dynamics GP Tables.