Looking up a particular repeating value

[budgy]

I am in the throws of setting up a new database with the current tables

Table 1

Engineer

Sample Number

Table 2

Engineer

Contract Number

Sample Number 10 no. repetitions

Sample Details 10 no. repetitions

Results 10 no. repetitions

Table 3

Contract Number

Sample Number 4 no. repetitions

Results 4 no. repetitions

I have managed to use the value lists and relationships to link all the data together so that only the sample numbers allocated to the engineer in Table 1 can be inserted into the Sample Number field in table 2. Also only the sample number allocated to a certain contract in table 2 can be inserted into the sample number field of Table 3. These use pop up menus with the values which match the relationships.

The problem I have is in Table 3 being able to lookup the result which relates to a particular sample number in Table 2 ie: Say the sample number is repetition number 3, the text entered into repetition number 3 of that record needs to be inserted into a field called Results in Table 3

Is using Lookups the best way to do this or am I making this a bit long winded.

I will need to be able to do a search for a particular sample number in Table 3 later on so if I use separate fields for each of the repeating lines I most probably will have to write a script to do the lookup. Well that will be my next hurdle once I sorted this one out.

Thanks

FileMaker Version: 7

Platform: Windows 2000

[Ugo DI LUCA]

Ian Budgen said:

Is using Lookups the best way to do this or am I making this a bit long winded.

I will need to be able to do a search for a particular sample number in Table 3 later on so if I use separate fields for each of the repeating lines I most probably will have to write a script to do the lookup. Well that will be my next hurdle once I sorted this one out.FileMaker Version: 7

Platform: Windows 2000

I wouldn't expand a lot as you're using 7 while I'm not, but it's more a matter of relational design IMO.

Wether you'd be using separate tables or files, you'd for sure get rid of repeating fields, which as you seem to have discovered wouldn't answer this need, and may be even less in 7 than 6.

FileMaker Version: 6

Platform: Mac OS 9

[budgy]

So you think I should go away from the repeating fields?

[CyborgSam]

Ian->

Welcome to the forums!

I would definately recommend moving away from the repeating fields for your case. If your sample tracking is anything like the one I did years ago, the amount of samples is so variable that repeating fields may need more than 10 reps.

By having the data relational you have a lot of flexibility in manipulating the data, when it's in repeating fields manipulation can be a messy process. One downfall of repeating fields is that to set a repetition in a script requires a dedicated step per repetition. There is no single step where a field can specify the repetition. So a script looks like:

If ["theRep2Modify = 2"]

Set Field ["theField-2", "theValue"]

Else

If ["theRep2Modify = 3"]

Set Field ["theField-3", "theValue"]

Else

... ad nauseum.

Yuck...

Sam

[Ugo DI LUCA]

Hi again,

Yes I'd recommand a move from repeating fields, as you'd be limited to the number of repetitions you'd have decided at the moment of the file creation.

It is possible though to lookup a specific repetition, but it requires another repeating field and a trigger calculation.

Another method would be a loop using the Status(CurrentRepetitionNumber) in it.

You may investigate the Forums for a bunch of demos about both methods, but, really, don't spend too much time with them. They are only workarounds.

Oh I quite forgot again...Welcome to the Forums too.

FileMaker Version: 6

Platform: Mac OS 9

[budgy]

Thanks for your help guys

Would the best option be to use seperate records for each set of sample results and then use a script to search for a match when I come to look up the results in the future?

Thanks for the welcome, I got the forum address from Filemaker Technical Support and I must say I ma very impressed already. This will certainly make the task of settijg up my databases in the future much easier.