I have a contacts table of records (CCcontax) with a self join relationship based on whether those contacts are also accom providers.
What I would like is a drop down to select the accom provider's name, then for address fields to populate on the record of the contact wanting accom - typically authors and writers coming to town for a Writing Festival, staying with local residents.
So all contacts are in one table, and are tagged as Author, writer, artist, accom, whatever - and yes some residents are also authors/writers/artists.
So far I've got the self join (Accom_providers as a second instance of CCcontax) based on: a calculation field which returns a boolean yes if the tag field contains accommodation; and if the contact ID does not equal the accom provider ID (don't want people staying with themselves) , then a value list based on the name fields from Accom_Providers, including related values only from CCcontax.
I could use a portal to Accom_Providers, but I'd like the Accom info...address, phone, etc to populate once the name is chosen.
As always I've checked Phil's excellent resources first, but it doesn't seem to cover a self join with a subset of records.