This article describes how you can make M:M relationships easier to use and hide their keys. It does not require any Xbasic or Action Scripting and is suitable for beginning users.
Background
Alpha Five does not directly support M:M relationships. But these can be modeled through the use of set using an intersection table such as the following:
setPerson
tblPerson (Indexed on Person_Id)
----tblPersonOrganization (Indexed on Person_Id and Organization_Id)
--------tblOrganization (Indexed on Person_Id)
The relationship of tblPerson to tblPersonOrganization is 1: M
The relationship of tblPersonOrganization to tblOrganization is 1:1
Screen 1 shows the default form that Alpha Five creates from this set.
Because we do not want to display record keys on our form, we will hide the Person_Id field.
To do so, right click on the field, then select Properties�
On the Setup tab, check the Hide checkbox.
Repeat this process for the label for field Person_Id.
Note that you can not remove or delete this field from you form, only hide it.
That was easy. But what about the Organization_Id field?
If you hide the field Organization_Id by setting its column width to zero, you will unfortunately prevent Alpha Five from creating the intersection record.
The default form shows a ^ button in the first column of our browse which allows use to select an Organization_Id. But we want our user to select a Company name instead.
So we must make five modifcations to the browse.
1. Move organization name to the second column
We move the Organization name column from its default position so that it is in the second column position. Note that Alpha Five has created a browse with columns representing the fields of the intersection tblPersonOrganization table first followed by the fields from the tblOrganization.
Click on the column heading for organization name until the entire column changes color then physically drag it to the second column position. Delete any other unneeded columns.
2. Remove the row selector
Click on the browse, Select Properties�
On the browse tab in the browse appearance frame, uncheck the checkbox for row selector
3. Narrow width of Organization_Id column
Click on the column heading for Organization_Id until the entire column changes color, the select Properties�
On the setup tab, enter the column width as .178 inches
4. Change background and font colors of the Organization_Id column
We want the organization column to look similar to the row selector. Unfortunately, if we set these colors to the same value, Alpha is smart enough to recognize that something fishy is afoot and does nothing. So the trick is to use colors which are different but visually similar.
On the font tab,
set the font color of the Organization_Id column to #235,232,217
On the format tab,
set the fill color of the Organization_Id column to #235,232,218
5. Change the column title to a single space to blank it out
Screen 2 shows the final result.
To create a truly finished application create a custom browse using tblOrganization which contains two columns:
Organization_Id � set the column width to zero
Organization_Name
Name your browse brwOrganization.
Then maintain the field edit rules for tblPersonOrganization.
Select field Organization_Id, then click on the Lookup tab. For the field Browse layout to display, enter brwOrganization.
Screen three shows that the field Organization_Id is now hidden on both browses.
Background
Alpha Five does not directly support M:M relationships. But these can be modeled through the use of set using an intersection table such as the following:
setPerson
tblPerson (Indexed on Person_Id)
----tblPersonOrganization (Indexed on Person_Id and Organization_Id)
--------tblOrganization (Indexed on Person_Id)
The relationship of tblPerson to tblPersonOrganization is 1: M
The relationship of tblPersonOrganization to tblOrganization is 1:1
Screen 1 shows the default form that Alpha Five creates from this set.
Because we do not want to display record keys on our form, we will hide the Person_Id field.
To do so, right click on the field, then select Properties�
On the Setup tab, check the Hide checkbox.
Repeat this process for the label for field Person_Id.
Note that you can not remove or delete this field from you form, only hide it.
That was easy. But what about the Organization_Id field?
If you hide the field Organization_Id by setting its column width to zero, you will unfortunately prevent Alpha Five from creating the intersection record.
The default form shows a ^ button in the first column of our browse which allows use to select an Organization_Id. But we want our user to select a Company name instead.
So we must make five modifcations to the browse.
1. Move organization name to the second column
We move the Organization name column from its default position so that it is in the second column position. Note that Alpha Five has created a browse with columns representing the fields of the intersection tblPersonOrganization table first followed by the fields from the tblOrganization.
Click on the column heading for organization name until the entire column changes color then physically drag it to the second column position. Delete any other unneeded columns.
2. Remove the row selector
Click on the browse, Select Properties�
On the browse tab in the browse appearance frame, uncheck the checkbox for row selector
3. Narrow width of Organization_Id column
Click on the column heading for Organization_Id until the entire column changes color, the select Properties�
On the setup tab, enter the column width as .178 inches
4. Change background and font colors of the Organization_Id column
We want the organization column to look similar to the row selector. Unfortunately, if we set these colors to the same value, Alpha is smart enough to recognize that something fishy is afoot and does nothing. So the trick is to use colors which are different but visually similar.
On the font tab,
set the font color of the Organization_Id column to #235,232,217
On the format tab,
set the fill color of the Organization_Id column to #235,232,218
5. Change the column title to a single space to blank it out
Screen 2 shows the final result.
To create a truly finished application create a custom browse using tblOrganization which contains two columns:
Organization_Id � set the column width to zero
Organization_Name
Name your browse brwOrganization.
Then maintain the field edit rules for tblPersonOrganization.
Select field Organization_Id, then click on the Lookup tab. For the field Browse layout to display, enter brwOrganization.
Screen three shows that the field Organization_Id is now hidden on both browses.
Comment