Easiest way to link two different "Parent records" in set

[rmcgaffic]

What is easiest way to create a link between two different "parent" tables on a single form? Both easiest on the developer and on the user?

For example, the following set has been defined:

tblPerson (key=PersonId, autoincrement)
----tblPersonOrganization(fields PersonId, OrganizationId)
---------tblOrganization (key=OrganizationId, autoincrement)

This modeling approach is used because one person can be associated with many organizations and one organization can be associated with many people.

When I create a form based on this set it works beautifully when records are displayed only.

However, when I attempt to add a Person record, I run into a somewhat expected problem:

When I place a type in field on my form for the organization name, the following error message results:

Field is required: Organization_Id

When I place the field Organization_Id on my form, because it has been defined as autoincrement, I can not type in a value for OrganizationId

I wish to link or connect Person with Organization rather than simply showing the Organization as an attribute of the Person. The latter approach is typically taken by all the Alpha demos that I have reviewed. For example, the Alpha Five contact manager has a table for both People and Activities, but an activity can be associated with only one person.

I assumed that this was a rather generic requirement, but could not find an ActionScript to address it.

Ideally, I would like the screen to behave like this:

1. Enter person information: name, birthday, etc.
2. Enter organization name
3. Find the organization name and create the link:
Create record in tblPersonOrganization
4. If organization not found, create a new record in the organizaiton tabel and link the person and organization record:
Create record in tblOrganization
Create record in tblPersonOrganization

Bob McGaffic
Pittsburgh, PA

[Stan Mathews]

Re: Easiest way to link two different "Parent records" in set

The documentation describes Many-to-Many sets just as you have constructed. Then they warn.....

Note : Alpha Five does not support forms or browses that will display data from this type of set. However, you can use Xbasic routines to read from and write to the set.

[rmcgaffic]

Re: Easiest way to link two different "Parent records" in set

Stan,

Are you aware of any demos or sample code for writing to many to many sets?

My set structure mirrors the documentation reference you provided.

I'm surprised Alpha stated that many-to-many sets could not be used for reading from the set. I was able to do this successfully. It's the writing the presents the problem.

Thanks,
Bob McGaffic

PS: I found the Students and Classes template in the Alpha Five templates, which deals with a similar situation: one student enrolled in many classes, one class attended by many students.
I need to have a scalable solution and using combo boxes to select either students or classes with not scale up -- even for a community college.

PPS: Found what I was looking for. The combo box which is the pull down for a student to assign to a class also has a [Find] button to select the desired student and write an intersection record. Perfect! Now all I have to do is reverse engineer it!