Now I know you are all going to say “don’t use repeating fields to store editable data!” but I am running into all sorts of other problems with my solution using a separate table of records that I thought it may just be that repeating fields could offer a solution.

 

Contacts table with multiple addresses.

Pop List for field AddressType based on editable custom value list - Address 1 street, Address 2 Postal, Address 3 Billing, Address 4 - Shipping, Address 5 Other …Edit to generate a dynamic relationship.

 

The current solution uses a separate Contact_Addresses table and the dynamic relationship is based on a calculation field in Contacts - AddressTypeAbs = Abs(AddressType), which returns just the numeric value from the value list. A transparent portal then displays the related address fields on the Contacts layout. This is an elegant way to save space on the Contacts’ layout as only one address is displayed at any one time but unlimited addresses are possible.

 

My problem is however that many other tables in my solution have a relationship to Contacts, e.g. Invoices, Purchases, Quotes, Personnel and on and on and on. So in order to display address details in these other tables each has to have another relationship to Contact_Adresses. The deeper it goes the more complex it gets with relationships, workaround scripts and key fields.

 

I thought that maybe using repeating address fields in the Contacts table itself might simplify the database by drastically reducing the need for the extra relationships, key fields etc. but I cannot seem to find a way of achieving my goal to display each repletion in a portal.

 

What would fit my needs perfectly would be to have say 10 repetitions of each of the address fields in the Contacts table itself. The relationship key AddressTypeAbs would remain the same but relate to the address fields’ repetition number. It doesn’t seem possible to relate to a repetition number but I might just have missed something. Just thought to post this before giving up entirely. Any thoughts?

If your Contact record already has the relationship to the Contact_Addresses to pull the primary address, then you should be able to tunnel to the Contact_Addresses fields through any table that has a relationship to Contacts.

Copy the "related address fields on the Contacts layout" and then go to a layout that uses the Invoice table and paste them on the layout, Go to Browse Mode. Is there data in the fields and is it correct?

It should tunnel                Invoice ---- >  Contact -----> Contact_Addresses

Hope this helps!

Thanks dwdata

Yes, I have anchor buoy relationship structure but find it doesn't go both ways sometimes when using go to related record. Trying to use the one relationship does not allow the selection of the layout I want to display unless I put the same relationship in the other way.

I have put the repeating fields idea in the trash.