Simple FM appn example please

[joseppic]

Hi,

Here's what I would like if possible:

I have been tasked to develop a basic db in FM but am having difficulty doing a basic thing. For example: I am assigning people to a group, so I have 2 tables/files - people.fp5 and group.fp5. Each file uses unique sequenced record IDs.

In the people.fp5 one of the fields is groupID. I want to add a person in the people.fp5 and then assoc that person to a group. I don't want to type a number in the people.groupID field, I want to select from a dropdown of groups. OK, simple stuff I know, but how do I do this. The files need to remain relational, can I use a list of group.groupNames in the people.fp5 file? Won't this give me a people.groupName field in the people.fp5 file - thus removing the need for a relationship people.groupID-->group.groupID and thus going against basic S/E principals.

Do I use match fields?, does this obsolete the people.groupID I would like to retain.

I've used portals in the past but they don't seem appropriate here.

Basically, I want to enforce a basic one-to-many relationship which is a simple thing to do in any other language/ide.

My next question is how I get many people in many groups. I've attempted to use a join file, but it expects you to open the people_group_join.fp5 and type in the personID and groupID in a record to enforce the many-to-many relationship - hardly ideal.

thanks for reading this.

Any solutions/examples appreciated.

[Chuck]

You definately need a join file to allow many people to be in many groups. There are a number of ways to easily allow the creation of records in this join file.

In people_group_join.fp5 you should have both a PersonID and a GroupID. In people, create a relationship to the people_group_join file based on the PersonID on boths sides of the relationship. Before leaving the relationship definition dialog box, click on Allow creation of related records.

In people_group_join, create a relationship to groups based on the GroupID on both sides. Create a text calculation called GroupName and use "groups::GroupName" as the calc.

In people, create a portal that views the people_group_join relationship. Place the related fields GroupID and GroupName in the portal.

Create a new value list. Set it to show values from a field and select the gorup file. On the left side select the GroupID field and click the "Also show values from" checkbox, then select the GroupName field on the right. Attach this value list to the GroupID field in the portal you just created.

Since you are allowing the creation of related records with this relationship, that portal will always have a blank record at the bottom. Clicking the GroupID field in that row and making a selection will create a new peope_group_join record and set the PersonID of that record to the PersonID of the current record in order to make the relationship valid. If you don't like the blank portal row, you can have a button used to create the related record instead. This is my preference when building such interfaces, but doing so requires a bit more work.

Chuck

[joseppic]

This sounds good, I'm now in the process of trying this.

Do I do a purist one-to-many in a similar fashion?

thanks Chuck.

Joseppic

[Chuck]

Do I do a purist one-to-many in a similar fashion?

If by that you are asking if you would use a similar technique to create the related records, yes. In fact, that's exactly what you're doing. The relationship between people and people_group_join is one-to-many.

Chuck

[joseppic]

Thanks for solving this.

J