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

 

 

 

 

 

 

 

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

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.

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.

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