many to many, enter relation

[MichelMichel]

I am a beginner . I did not find an answer to my problem in the Forums.

I have a Person.fp5, Document.fp5 and a Join_Pers_Doc.pdf

The Person contains Names and all associated data as well as a ID

The Document contains text, date, container, ID .....

I want to create a Document record and later be able to associate a selection of Person records to it (displayed in a Portal in Document). The Document Records associated to the Person are displayed in a portal in Person.

I want to make a selection of Person with the usual tools in file maker (sort, find) at a later stage and associate the relation to the Document record. I need to be able to change that association at any time (add or take out single Person record association or make a new selection of Person records.

How do I put the information in the Join file?

How is this problem addressed normally?

Is there something else i should know about this issue?

I asume it is done with a script?

[CobaltSky]

Yes, it is done with a script - or if you prefer, several scripts.

In fact you don't necessarily have to use a join file, as you can achieve the same effect using multi-key fields. However with either approach, it is possible to make the addition and deletion of associations work seamlessly by using scripts.

Typically this is achieved by creating calculating constant fields in both files (ie with the formula set to 1, so that they always match) and then relating each file to the other based on a match between these constant fields.

This enables you to place a portal on a layout in each file which lists all the available entities in the other file (eg a list of all persons in a portal in the documents file) from which to select additional persons to associate with the current document (or vice versa in the persons file). Alternatively, value lists in each file can be used to achieve a similar effect.

If using a join file, you will require relationships to the join file from global fields in either file.

The script which adds/deletes an association should:

1. Check if an association already exists, and delete it if it does.

2. Set the global key field for the relationship to the join file (if used) to a unique value (eg using the Max(JoinFile::AssociationID) function to generate an incremented ID value for the join record)

3. Write the current record ID and the selected associated value ID to their respective fields in the join file (or add the relevant ID to the multi-key field if a join file is not being used).

With a single script of this type in each file, selecting a value which is already associated will dissociate it, whereas selecting a value which is not associated will associate it. An indicator (eg an asterisk or highlight) in the portal or list which shows which values are currently associated makes the process easy to manage from the user's perspective.

Although there are many variations on the theme, the above should give you the basic elements from which to work.

[MichelMichel]

thanks Ray

I only got part of the idea.

....In fact you don't necessarily have to use a join file, as you can achieve the same effect using multi-key fields. However with either approach, it is possible to make the addition and deletion of associations work seamlessly by using scripts.....

Lets assume i want to use a Join file.

....Typically this is achieved by creating calculating constant fields in both files (ie with the formula set to 1, so that they always match) and then relating each file to the other based on a match between these constant fields....

Between the Person and Document file or with the Join file?

....This enables you to place a portal on a layout in each file which lists all the available entities in the other file (eg a list of all persons in a portal in the documents file) ....

o.k.

....from which to select additional persons to associate with the current document (or vice versa in the persons file).....

What do you meen by selecting?

....Alternatively, value lists in each file can be used to achieve a similar effect....

That would be the multy-key field?

....If using a join file, you will require relationships to the join file from global fields in either file....

I get a bit lost here. From a global field in Person and Document to a corespondng global field in Join?

....The script which adds/deletes an association should:

1. Check if an association already exists, and delete it if it does....

How?

....2. Set the global key field for the relationship to the join file (if used) to a unique value (eg using the Max(JoinFile::AssociationID) function to generate an incremented ID value for the join record).

3. Write the current record ID and the selected associated value ID to their respective fields in the join file (or add the relevant ID to the multi-key field if a join file is not being used).

With a single script of this type in each file, selecting a value which is already associated will dissociate it, whereas selecting a value which is not associated will associate it. An indicator (eg an asterisk or highlight) in the portal or list which shows which values are currently associated makes the process easy to manage from the user's perspective.

Although there are many variations on the theme, the above should give you the basic elements from which to work....

I am rather lost here. Please, if you can, explane in more detail.

thanks

Thomas

[CobaltSky]

Hi Thomas,

The techniques we are discussing are a little complex, so I'll try to address each of your questions in turn.

1. Since you want a join file, I'll base all my further comments on that.

2. The calculating constants should be used for relationships which directly link the persons and documents files. This is so that it will be possible to access a list of available documents from which to choose when setting up document associations for a person record (and vice versa if you want to set up person associations per document).

3. Re: "What do you meen by selecting? " I mean that you must provide a means by which the user can identify which documents are to be associated with each person. Thus they must have a means to select documents to associate with a person record.

4. Re: "....Alternatively, value lists in each file can be used to achieve a similar effect....That would be the multy-key field?" No, value lists or portals are two possible means by which the user interface for selection of associated values can be set up, regardless of whether multi-key fields are being used.'

5. Re: "....If using a join file, you will require relationships to the join file from global fields in either file....I get a bit lost here. From a global field in Person and Document to a corespondng global field in Join?" No, the relationships will be from global fields in the persons and documents files to a single data entry (number) field in the join file. The purpose of this is to generate new join records via the relationship (the 'Allow creation of related records" option must be turned on) by placing a unique valaue into the global field in the current file, then writing values via the relationship. Since the value is unique, no related record exists, and therefore FileMaker will create one. So, the key field in the join file should be an AssociationID (or 'JoinID') field, and the global fields in the other files should be set with Max(JoinFile::AssociationID) + 1 in order to generate unique values.

6. Re: "....The script which adds/deletes an association should:1. Check if an association already exists, and delete it if it does....How?" There are several ways to do this. One I would suggest would to have a relationship to the join file based on current record identifier (person number from the person file or document number from the documents file) to the corresponding field in the join file. Then set up a value list which uses related values only, to return related documents per person (or persons per document). You can then set up a test along the lines of:

If ["PatternCount(ValueListItems(Status(CurrentFileName). "DocumentsPerPerson"), gCurrentDocument)"]

where the name you have given to the value list is 'DocumentsPerPerson' and the gCurrentDocument is a global field which temporarily holds the ID# of the document that the user is currently trying to add or delete to the current person record.

7. Re: "[rest of reply post] I am rather lost here. Please, if you can, explane in more detail." You will need to decide on a user interface method for users to add and delete associations. Once you have decided what that is, yopur script will need to be taylored accordingly. What I was suggesting is a single script which dynamically deletes or adds a join, using a test such as the one I suggested at 6 above. I also suggested a method for adding the join record, and have described that in greater detail at 5 above. My final comment was about the possibility of making the interface more 'friendly' by showing the user which values are already associated with the current record. There are several ways to approach this, but flagging or highlighting associated values (using a calculation which produces an asterisk or highlight color in the list or portal you are using) is one option.

I hope the above comments will help you. However be aware that what you are attempting is advanced and, as a developer, it takes many hours of work to set one of these systems up for a client. Describing the entire process in step-by-step detail would be more the stuff of a book chapter than a forum post.

In fact the 'system' you are contemplating requires the simultaneous use of a half-dozen different advanced technmiques working together. Each of these you will find discussed at length separately (eg calculation of unique IDs, creating records using relationships, highlighting portal rows, testing for the existence of related records and so on) on the FileMaker Knowledge base and in many differnt forum threads - as well is in several of the better books on advanced FileMaker programming.

I suggest that you break the process up into its stages and check for related forum or kbase topics (or post specific questions) if you get stuck on particulars along the way.

[MichelMichel]

thanks Ray

I almost suspected that this would be a bit of a leap.

Never the less, I am determined to get this database built up this year. But I promise to do my homework first.

Thanks for your help. I made a considerable step ahead with your help, for having started only a few days ago.

Regards

Thomas Michel