Saturday, October 21, 2006

Creating Header/Detail-based Linkage On One Form

I have been struggling with a seemingly simple concept over the past couple days: How to setup a form that has two grids where one grid represents header-level data and the other grid represents detail-level data with the proper linkage. In all of the books, newsgroups, and blogs out there no one has yet to tackle documenting this seemingly simple task until now. I managed to get the form running tonight and I am posting this in case someone else may be struggling just like I have been. In my struggles I tried to mimic both the architecture and properties of both the SalesTable and SysEmailTable forms which have Header and Detail grids. Even after duplicating what these two forms use, my form still did not work properly. In all cases my detail form showed all records no matter which header record I placed focus on. Below is the steps that I have verified that successfully develops a header and detail grid. There may be other ways to accomplish this but this process definitely works.

1.) SETUP THE DATABASES: If you have not already, create your header records table and your detail records table.
a. Creation of your tables should include adding the appropriate Extended Data Types to your table for the fields you would like it to have.
b. Your Detail table should include the field that is in your Header table that you wish to use as the key between the two tables.
c. Add Indexes to both tables while including the key to your header table in the index along with including the same field that is in your Detail table as part of the index.
d. Add a relation to the Detail table to join the fields that appear in both the Header and Detail tables together.
e. If you wish to prevent database anomalies, add a DeleteAction to the Header table that performs a 'Cascade' DeleteAction on the Detail table.
2.) LINK DATABASES TO FORM: Drag the Header and Detail table into the Data Sources node of the Form you wish to use to display the two.
a. Set the Detail table's properties JoinSource property to the name of the Header table and verify the LinkType is set to Delayed.
b. If you do not want AX to automatically add a new record if no records exist, set InsertIfEmpty to 'No' in the Data Source properties of the Detail and/or Header data sources.
3.) DESIGN FORM FROM DATABASES: Add the appropriate container hierarchy to your form design and add the appropriate fields to each.
a. In my form, I created three groups within the form: Header, ControlSplit, and Detail.
b. The Header Group contains a Tab, within the Tab a TabPage, and within the TabPage a Grid control. The Grid control's datasource property is set to the Header table.
c. The Detail Group contains a grid control with the datasource property set to the Detail table.
d. Within the Header and Detail Group's grid, I dragged the appropriate fields/field groups into their respective grids.

Using these steps you should be able to correctly link Header Table data within a form to Detail Table data where only Detail Table data is displayed based upon the current Header Table data record.

I hope this helps those of you who maybe new to AX like myself and are struggling with a similar problem.

3 comments:

Gabriel Bakker said...

Thanks for the great tip, David! This not only worked exactally as you described it, but it worked even better! I made two tables, and after I followed your instructions once, I had a header/detail relationship, represented in one form. Then I was able to make a third table, and treat the second table as the new header, and the third table as the new detail, and I followed your instructions again, and now I have a 3 table relationship represented on one form. It works beautifully. Thanks!

James said...

A related problem I ran into is when you add 2 grids on a form both grids default to the first datasource in the list. You might expect the datasource to autmatically update itself when you drop columns from the second (third etc) datasource into it, but it doesn't change. You have to manually change the datasource property.

Haroon Attari said...

Hi Dave, I followed all steps which are mentioned in your blog but still the detail form is showing all records instead of record(s) related to selected record in header. Can you help. Will be grateful.

Thx