Repeating fields & relationships

[Joe Troxtel]

I am trying to enter data into a repeating field and return data from a related record in a external table occurance.

1 - The related external table is a list of job records. Each job is is own record.

2 - The current record is in the "time sheet" database. I have the job number and the info. about the jobs all setup to receive data in repeating fields.

3- I am using this calculated lookup :

If ( IsEmpty ( Job Number ) ; "" ;

If ( IsValid ( OPMAPJS job number::Job Number ) ; OPMAPJS job number::Project Name ; ""

)

)

My problem is that the data I am getting is being pulled for the first record entered in the repeating.

I entered the job number into the repeating job number field in the Time Sheet DB of the reflated table/field. That works. When I enter the next job number in the Job number field it returns the info form the first entry.

I hope this is clear enough.

If anyone can help I would be great.

Thanks Joe T.

[douglasgifford]

Some clarification, please. What does the "time sheet" represent? Why are you using a repeating field for a relationship key?

As to the "I can only get the first record" problem, this is, it seems, normal and very irritating. For workarounds, see the "accessing related records by number" thread. The most standard workaround, I think, is to use a portal to pull your data into the current layout (I imagine that approach is dealt with in detail elsewhere).

[Joe Troxtel]

Thanks for your replay Douglas.

The biggest reason why I am using repeated fields in the Time Sheet DB is because I was using it that way in FMP6 and it worked. As I convert my DB's to 7 I am trying to get the same functionality.

I am just trying to get a simple lookup to function... Type in a series of job Numbers in a repeating field an return their related data in corresponding related fields.

It appears that when when multiple values of related data is required in 7 a portal should be used.

Repeating fields appear to be used only in stand alone instances when no relationship are required.

I guess I am going to rebuild the Time sheet db to work with a portal. This seams to be a better way for my solution.

Thanks for the help.

Joe T.

[douglasgifford]

The reason I asked about using repeating fields for a relationship keys is that there are usually better ways to do this. For one, you could enter the keys in a standard text field separated by paragraph returns (in other words, one line per key); this removes the limitation of a repeating field where you can only enter as many repeats as you have allowed in the field definition.

A more-elegant approach, though, in a one-to-many relationship, is to put the key field on the many side of the relationship. That is, if you have two files:

parentFile and childFile

where childFile can have only one parent but parentFile can have many children,

it is cleaner to put a foreign key field in the childFile that relates back to the single parentFile rather than put a foreign key field in the parentFile that has to relate to an arbitrary number of childFiles (using a repeating field or paragraph returns).

If the relationship is many-to-many, it gets more complicated.