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.
