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.
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.