Looked up relationship problem

[Corey-Troy]

I have a table of info for customers that has a contact field that has 10 repetitions(10 contact names). Each name has a field for each contact Phone number(also 10 reps).

My problem is i have a layout that has a relationship between the Customer field in each table.

The user selects the Customer, and the field for Address populates with the address from the customer table, as required.

I placed a field(contact) on the layout now, and all contact names for the related Customer are listed, as required.

But the next field is the contact phone number, where i am having difficulty in have the data displayed.

If i select say contact name 3, than i would need phone number 3 to be displayed.

I selected the phone field to Lookup data from the customer table::Contact Phone field, but i do not get data displaying.

I guess my Q is: Can Lookup's be used with repeating fields?

I cannot see how i can get away from repeating fields in the customer table, as there is always >1 contact name and number for a single customer.

I tried a Calculated value and used the IF and CASE calc's, to try to display the data, by refering to each individual Repetition to no avail either.

[iF(Table2::Contact=(GetRepetition(Table1:Contact;1);

Table2::ContactPhone=(GetRepetition(Table1:ContactPhone;1);

IF(Table2::Contact=(GetRepetition(Table1:Contact;2);

Table2::ContactPhone=(GetRepetition(Table1:ContactPhone;2);"")) etc....

How do i solve this?

Corey....

[Corey-Troy]

Solved it with an Auto Enter Calc.

[Søren Dyhr]

Your take on population isn't a healthy one!

Phone numbers are not a fully justified candidate for lookups since they only changes rarely, your data-model is storing redundant data ... could be fine with you, but is not what is to be expected from a normalized solution.

Refrain from the use of repeaters for other purposes than utilitarian. A proper structure uses when talking filemaker a portal to show related data ... and the lookup functionality should solely be used to store historic data which might expire soon after the record is created, e.g. Invoices where the price level might fluctate now and again ... but where the live update a geniune relation would cause the invoices would adjust the total after being send to the customer, invoices are historic not phone rosters, if they were to change over night why bother have them in the first place?

--sd