Relationships

[fmnovice]

Hi,

 

My first post on this forum so hi.

 

Im a bit stuck as I have been playing around with filemaker pro 12 and I have come up against an issue.

 

Can you advise me please?

 

I currently have two tables, People and Criteria.

 

The people table consists of something like this;

 

Person ID (unique serial key)

Name

Age

address

Criteria 1

Criteria 2

Criteria 3

 

The Criteria table is a bit like this;

 

Criteria Number (unique serial key).

Criteria detail.

Criteria Year

 

Basically I want to be able to enter the criteria number in comment1/2/3 and then create a report on this showing the Criteria detail. The issue is as I am sure you can see I have a many to many relationship, so I was going to create a join table but I am unsure whether this will work.

 

First of all I thought it was quite simple until started trying to join up all of the joins

 

How would you deal with this?

 

Thanks

 

 

 

 

 

 

 

[Kris M]

Person ID (unique serial key)

Name

Age

address

 

The Criteria table is a bit like this;

 

Criteria Number (unique serial key).

Person ID

Criteria detail.

Criteria Year

 

 

join on Person ID and perhaps enable creation of criteria in the join

[fmnovice]

HI thanks a lot for this.

 

The trouble I have is that each of the criterias entered could be in any of the criteria fields so as a consequence I cant really have a person tied to a specific criteria. Hope that make sense.

[eos]

Basically I want to be able to enter the criteria number in comment1/2/3 and then create a report on this showing the Criteria detail

 

For a number of uses you could get away with using fields; but reporting is not one of them.

 

but I am unsure whether this will work.

 

You won't be able to create (correct) reports unless you store Person/Criterion associations as records, instead of fields; it doesn't matter if you use an actual join table (which you will, since you already have a Criteria table), or just a child table People --< PeopleCriteria, where the criterion field fields holds a text value instead of a foreign key (and the value list described below would hold static values, instead of being populated with field values). 

 

The trouble I have is that each of the criterias entered could be in any of the criteria fields so as a consequence I cant really have a person tied to a specific criteria. Hope that make sense.

 

If you mean you're not sure how to convert your existing structure into a join table, then try the following (after you've closed your file and created a backup!):

 

Create your join table (say, PeopleCriteria) with (at least) these fields (the actual names are of course up to you):

 

• PK_joinTableID (auto-enter serial)

• FK_personID

• FK_criterionID

 

In People, find all people with a value in criterion1; import that found set into PeopleCriteria; match People::PK_personID with PeopleCriteria::FK_personID, and People::criterion1 with PeopleCriteria::FK_criterionID.

 

Lather, rinse, repeat for criterion 2 and 3. There's your populated join table.

 

For a larger number of fields, you'd write a script to create the join records, but for just three fields, this works probably much faster …

 

If you don't have foreign keys in your criterion1, …2, …3 fields, but the actual criterion name, then create a text field criterionText in PeopleCriteria and import the field into that. After you've done all imports, create a temporary relationship between PeopleCriteria::criterionText = Criteria::criterion, and use that relationship to add the foreign key to PeopleCriteria::FK_criterionID.

 

Display a portal into the join table on a People layout, and allow creation of related records via the relationship. Delete fields criterion1, …2, …3.

 

Simplest method of data entry for the moment would be to

 

• create a value list with Criteria::PK_criterionID (first field) and Criteria::criterion (second field; showing values from second only),

• put PeopleCriteria::FK_criterionID into the portal, and

• format it as a popup with the new value list.

 

Now build your report layout(s) in the join table.

[fmnovice]

thanks a lot, ill have a go with this tonight and fee back my finding. Appreciate you taking the time on this.