Additional Information Screens in Dynamics SL SDK

Goal 

You need to create a Dynamics SL 2011 Custom Screen using the Tools for Visual Basic.  Your Screen is an “Additional Information Screen”, as they are usually called, for say Customers. Additional Information means that you are creating a companion table for the Customer Table. The relationship is one to one for Customers: For each Customer in the customer table you can have one and only one matching Customer record in the additional information table.  This Additional Information screen is an extension of information you need to record for Customers.  

You want this screen to have a look up to the Customer Table when selecting a record to create/edit/delete. That means when the user hits the F3 button on the Customer Field you want to see a valid list of customers.  

Environment

SQL 2008, Windows Server 2008 for SL and SQL Server, Windows 7 workstations, and Dynamics SL 2011.

We are developing in Visual Studio 2010. Please see my other post on how to get around the 64 bit issue.

Issue

            The issue is that if you are looking up the customer table then the standard method to return records will return a customer record, not a record from the additional information table. The way around is to change some of the function calls you use and properties that are set.

The SL VB Toolkit easily allows you to navigate a table and edit it by just using a few concepts:

1)      Set your table as the Normal Level of the Update Control.

2)      Set a field or fields as the key. Put a PV stored proc in the PV Property of the key field.

3)      In the chk event of the key field’s control call the PVChkFetct Function

That is it. The basic components for editing a table in a screen that you add to the sl menu and behaves just like any other screen.   

           The way it works is that in the form load you have to set address and a cursor call for each table. In an Update control you list the tables and what levels they are. You put controls on the screen saying what is key and what level it belongs to.

            You supply a key field, say customer ID, that has a PV (stored procedure) to the Customer Table to get a list.  Then in the check event of the key field you call the PVChkFetch1. This call to the pvchkfetch forces the record to point to the selected customer record. The prerequisite is that the composite record returned from the pvchkfetch function must match the level that is being displayed on the screen. 

So if you have a screen that edits the Additional Customer Table, but lookup to the Customer Table to select a key, when you call the pvchkfetch it will return the customer record structure and not the Additional Customer Table Structure.

            You will get the System Message 10202 Error, saying The application buffer size is less, or more, than the cursor select statement requires. 

 

The Table:

CREATE TABLE [dbo].[CustAdd](

      [Custid] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [SettingOne] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [SettingTwo] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [tstamp] [timestamp] NOT NULL

) ON [PRIMARY]

 

GO

Unique Index called CustAdd0 on Custid.

 

The PV:

Customer_All – Standard PV that is used on the Customer Screen.

 

The Screen:

The property:

The code behind the custid field on the screen:

Private Sub SafMaskedText1_ChkEvent(ByRef ChkStrg As System.String, ByRef RetVal As System.Int16) Handles SafMaskedText1.ChkEvent

        serr1 = PVChkFetch1(PNULL, csr_CustAdd, ChkStrg, bCustAdd)

 

End Sub

Resolution                                             

            The Resolution is to use the PV property of your key field to lookup in the customer table just as before, but use the dbnav property to hold a stored procedure that returns a record from your additional information screen.  Instead of calling the PVChkFetch function in the check event of the key field, call the DBNavFetch function, they do the same thing but PVchkfetch uses the procedure in the pv property, dbnavfetch uses the procedure in the dbnav property.
You need to create a dbnav stored procedure that filters the CustAdd Table:

CREATE Procedure [dbo].[CustAdd_All] @param1 varchar(10) as

Select * from CustAdd where Custid like @param1 order by Custid

go

 

The Setting (leave the pv property set as before, just add a dbnav property with new procedure):

 

The Code(just change function call to DBNavFetch1):

Private Sub SafMaskedText1_ChkEvent(ByRef ChkStrg As System.String, ByRef RetVal As System.Int16) Handles SafMaskedText1.ChkEvent

        serr1 = DbNavFetch1(PNULL, csr_CustAdd, ChkStrg, bCustAdd)

 

End Sub

 

Conclusion 

You can have a lookup of the key value of your form populated from a different table then you are editing.  You just have to use the dbnav property of the key field and the dbnavfetch function in the chk event of the key field.