This one souncs vaguely familiar....
Let's see if I have the basic relationships correct for what you have:
My field and table names may differ from yours...
Individuals::__pkIndividualID = Individual_Location::_fkIndividualId
Locations::__pkLocationID = Individual_Location::_fkLocationID
So far so good, but I'm not sure about the rest:
Locations::__pkLocationID = Join::_fkLocationID
Locations::ByProvince::ProvinceID = Join::ProvinceID ----> is that what you have?
Wouldn't it make more sense to link to a "provinces" table instead of another record in Locations?
I think that's right; I'm not completely sure what some of your notation signifies. "pk" and "fk"?
In noob terminology, an individual entry might convey that Bob visited Dallas, Houston, and Philadelphia, all of which are entries on the Location table, as are Texas and Philadelphia, the latter two being connected to the former three via a join table. I am trying to figure out how to access the information that Dallas, for instance, is located in Texas (information contained in (Locations-----<Join>-----LocationsByProvince) from the Individuals table.
I considered having separate tables for "Provinces" and "Countries." The problem with that is that the distinctions between these different geographical units are not always clear cut. For instance, Athens could be a city, a province, and a country. When I have an individual that comes from a village outside of Athens, for instance, it seemed more efficient to pull the provine data for that village from the Athens entry. Does that makes sense?
Part of my design challenge here is that sometimes I only know that an individual is from Texas, sometimes I know that he is from Dallas, but if I pull all records of people from Texas I want him to appear in the search no matter what the specificity of my data.
Don't get hung up to much on field names. I forgot to include this link: Common Forum Relationship and Field Notations Explained
pk is a serial number field (primary key) and fk (foreignkey) is the number field that matches to it from the child or "many" side of the relationship.
I'm most interested in what "match fields" you used for the location to location many to many part of your relationships.
That link was helpful, thank you.
"Locations::ByProvince::ProvinceID = Join::ProvinceID" - There is no separate table for Provinces; so the join table is for two instances of the Locations table. I.e. the fk of the first instance of the Province table matches with the fk of a second instance of the Province table. So ProvinceID=LocationID.
The other match fields are right, as far as I understand.
LocationsByProvince would be the second occurrence of Locations in my original post.
What I have so far is:
Locations::_pkLocationID = Join::_fkLocationID
But what match fields do you use to link the second occurrence of Locations to the join table? I'd guess that you have:
LocationsByProvince::_fkLocationID = Join::_fkLocationID_2
or some such other field in Join.
Yes, that is correct. They both use the unique id of the Locations table, but from different occurrences.
It's not the different occurrences that I am checking. It's what fields in the join table are used in the link. I will assume that I am correct in my last post or now...
This should be quite workable and you can actually "loop" through location records from the specific to the general if you link recorsd that way and if that is of interest.
You could log a location for an Individual as "the Bronx", in New York city. It could then link to a location record for New York City, and that could link to a record for the state of New York .... United States..... North America......Western Hemisphere.......Planet Earth.... (Just to take it to ultimate extremes )
The challenge lies in accessing this chain of locations records as you can't place a portal inside of a portal.
You could use a "master-slave" pair of portals where you click a row in the Master portal, the "once removed" location records for that row appear in the "Slave" portal.
How do you implement s master-slave pair of portals?
We'll start with a layout based on Individuals. The Master portal will be to Individual_Location.
Add more occurrences for this relationship:
Individuals::SelectedLocation = JoinSlave::_fkLocationID
LocationsSlave::__pkLocationId = JoinSlave::_fkLocationID_2
Make your Slave Portal a portal to JoinSlave.
Put a button in the row of the Master portal or format the entire row of fields as a button so that clicking it performs this script:
Set Field [Individuals::SelectedLocatation ; Individual_Location::_fkLocationID ]
I am still struggling a bit to follow the notation and logic; a few questions:How is <JoinSlave> different from <Join> in the notation above; LocationsSlave and LocationsByProvince are both occurances of the Locations table, and both <JoinSlave> and <Join> are linking two provinces together, correct?I also don't quite get what the end result of this would be if I successfully implement it. Say I have Bob who lives in Los Angeles, which was part of Spain, Mexico, and the US (i.e. a many-many relationship); when I click on that button, how would it show me that information? I think I am missing something conceptually about how a slave portal works; based on what you've written, I think that when I click the button it will bring up all the related records in that same portal at the next level... does it display them in the same portal?
JoinSlave is a second occurrence of the Join Table. LocationsSlave is an added occurrence of the Locations table.
If set up correctly, Clicking the button for Bob should display the additional location info: Spain, Mexico, US in the slave portal. (You can include fields from LocationsSlave in the row of the portal to JoinSlave.)