Brewsterware

I was recently asked if I could add the forms to the data entity spreadsheet that I produce, but unfortunately this is not really possible. This is because a single field can be exposed on multiple forms and also many forms use temporary tables which are populated “on the fly” when the form is loaded – the “Accounting Source Explorer” is one such example. What I can do, however, is to show you how to create a list of table names, field names, associated form names and the form control string that the field is associated with.

There are several steps to doing this and the process requires access to a development environment. The first is to build all models making sure that the “Build cross reference data” checkbox on the “Options” tab of the “Full build” dialog is checked. This will make sure that all of the cross reference data is populated – this is stored in the DYNAMICSXREFDB database.

Once all of the models have built successfully, run the following query on the DYNAMICSXREFDB database using SQL management studio.

SELECT
		TableFields.Path
	AS	TableAndField,
		Forms.Path
	AS	Form
FROM
		[References]
INNER JOIN
		Names
	AS	Forms
	ON	Forms.Id = [References].SourceId
	AND	Forms.ProviderId = 2
	AND	Forms.Path LIKE 'Form/%/FormDesign/%'
INNER JOIN
		Names
	AS	TableFields
	ON	TableFields.Id = [References].TargetId
	AND	TableFields.ProviderId = 2
	AND	(TableFields.Path LIKE 'Table/%/TableFieldString/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldString/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldEnum/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldUtcDateTime/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldReal/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldInt/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldDate/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldInt64/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldTime/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldContainer/%'
	OR	TableFields.Path LIKE 'Table/%/TableFieldGuid/%')

This isn’t the most efficient query, and depending on the speed of your VM, this may take a little while to run. It took almost a minute on my VM. Once it has completed, copy the rows into notepad++.

This step will put the table names, field names, form names and form control strings into separate columns.

Press control + H to open the find and replace dialog. Make sure that the “Regular expression” radio button is selected, and copy the following regular expression into the find text box:

Table/([^/]*?)/[^/]*?/(.*?)\tForm/([^/]*?)/FormDesign/([^\?]*?)\?(DataField|PresenceDataField|FilterField)

Then copy this regular expression into the replace text box:

$1\t$2\t$3\t$4

Press the “Replace all” button. When the replacement is done, you will be left with tab delimited text file that has the table name, field name, form name and form string control as its columns. This can be saved and imported into Excel, or copied and pasted into Excel.

Here is a file I produced earlier from a 10.0.30 environment: Table.Field to Form mapping.zip