FileMaker may be easier to use than most other DB systems, but databases are by their very nature complex structures and there's no avoiding that complexity once you get beyond a solution of just 2 or three tables of data and just a few relationships. It's a good idea to plan on investing in some time and effort learning about FileMaker and dataase design if you plan to create your own solution.
Obviously, you'll need a table for listing data on your students and a table for listing professionals. What you do then depends largely on the type of information you have to work with. (Example: Is proximity based on distance, travel time or located in same city, or region?) and the procedures you need to follow in evaluating and approving the assignment of each student mentor pair. It'll probably be possible for a mentor to be paired with more than one student if they are willing. perhaps the reverse is also possible (one student with more than one mentor). All of these details have to be looked at so that you can rough out a design "on paper" before you can start figuring out the exact combination of tables and relationships you'll need to make this work.
Is it possible for a mentor to have multiple students and students to have multiple mentors?
If not the simplest method would be to have a table for each, then include a mentorID field in the student table and relate the tables by this mentorID. That way you can assign a student to a mentor by placing that mentorID in that students::mentorID field. This will work for a one to one or even a one to many ( a single mentor having multiple stiudents).
If you have a many to many situation, mentors have multiple students and students to have multiple mentors, you would need an intermediate join table. That table would contain the mentorID and the studentID for each combination of student to mentor.
Thanks Phil. You made some great points. I have in fact started with tutorials (and after 10 hours I feel I have only expanded my vocabulary, not my actually skill in creating/implementing the ideas.) Do you suggest I start from scratch or should I try and "tweak" a starter solution?
I have looked at some of that information and drawn it out. (From example I have drawn out the one to many relationship of Mentors to Students (Students may only have one Mentor per year, but Mentors may be paired with more that one Student.) However that does lead to the question, since this will be happening annually, there is a chance that the next year the same student will apply and then be paired with a different Mentor and visa-versa. raising the question, should their be a third table that hosts the Pairing data and allows me to match the two and also assign a year that they are being matched for.
Secondly, when it comes to the actual pairing, I would love it to be automatic (can it even do this?), in which case it would be closest by travel, but as I have only done this manually in the past, I don't even know the beginning of the type of scripts that would need to be entered to make that happen, especially because the databased will be filling slowly over a two month period, so it is not a first come first paired option.
The problem with making it autmatic would depend on how you set the matching criterea. I would assume you would use something like zip code to come up with proximites to each other. But you would not necessarily have both groups homogenously distributed to where it would work out.
I would think that using something like student::zipcode + 5 > mentor::zipcode > student::zipcode -5 (or whatever # works) to get a list of possible students for a mentor then picking the best match manually from there would be a good start.
Hmm... So maybe I should still match it by hand.
Even if I could get the Students in the same city as the Mentor to show up in a portal window on the screen (and visa-versa Students with Mentors in there city show up through a portal window) then have it be that I could assign some of the students to that mentor through the portal window. I would just need to have some way of sorting out the paired verses unpaired Students.
a cartesan join will give a portal to show all students, you can then use portal filtering to narrow them to the same city. Phil showed me a way to filter out already selected choices as well, I just have to find that post.
I've been working on a dwindling value lists/ many to many demo file. I brought it up and played around with an "all" portal to see what filter will work.
I came up with this expression:
Not ValueCount ( FilterValues ( List ( Scout_Events::ScoutID ) ; AllScouts::ScoutID ) )
Then update the script you've assigned to the button in the portal that assigns a scoutID to the join table to include this final scirpt step to force the portal to update:
Refresh Window [Flush cached join results]
In this solution I was tracking scouts to events, kind of like a roll call. I had two portals one showing all scouts in the troop and the other showing scouts that attended that Event. As each scout was selected as having attended the filter removed them from the All scouts list.
That thread was the inspiration for a demo file you can download and examine if interested: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
It starts with the basic set up for a many to many relationship and then explores several interface design techniques for linking records to each other.
Do you suggest I start from scratch or should I try and "tweak" a starter solution?
That's a tough one. I've seen some new users take a starter solution and adapt it to their needs with great success and many other have gotten 'lost' in the complexity of the starter solution design and were better off starting from a blank page so that they could add one new feature at a time and get it working before adding the next new feature.
I have seen posts here in the forum where people have explored using two zipcodes to approximate a distance between them. I don't know if they can be easily found here in this forum, but they may provide you with some ways for using zipcods to approximate distances if you can find them. I think they were able to download a table of zipcodes with a latitude and longitude which they could then plug into a formula to get a straight line distance between the two zips.
I was thinking of using zip codes just to roughly approximate the proximity. Just had a thought, what if you sorted by Abs (mentor::zip - Student::zip)
Shouldn't that roughly order them by distance?
With the emphasis on "rough", yes. I guess it depends on how close is "close enough".
I just checked a zip code map I've added to one database (You can see sales totals for a specified date range distributed on the zipcode map with a total for each zipcode.)
I can see a small town completely enclosed in a rural area's zipcode. The difference in zipcodes is 38. I think you'll also see an issue if there is a state line nearby. Locations that are part of the same metropolis, but straddle a state line could have very different zips.
I guess it is "rougher" than I was thinking. Sorting by the absolute value of the difference in date codes gets you roughly there, though even where I live there are areas in town that are in a different zip code that are closer than areas in the same zip code. A way to look up the coordinates based on the address would be much more accurate. A site like http://stevemorse.org/jcal/latlon.php can get those numbers. Not sure if it is possible to get filemaker to input the address and extract that data though.
Maybe with a web viewer window where you could copy and paste the address into the web page, then copy/paste the lat and long from the page to your fields?
Not sure if it is possible to get filemaker to input the address and extract that data though.
It's not a simple thing but you can use that webviewer to query a site and then use:
GetLayoutObjectAttribute to extract the page content from the webviewer. Then you can parse the data so extracted from all the other text, HTML tags, etc that get extracted with it.
"GetLayoutObjectAttribute to extract the page content from the webviewer. Then you can parse the data so extracted from all the other text, HTML tags, etc that get extracted with it."
Unless their HTML code uses frames and hides the data. I played around using a web viewer next to the address fields and lat and long fields, you can drag and drop the values back and forth, but gets tedious if you have very many addresses to look up.
Upon some more testing I was able to use a viewer set to google maps using the address fields from a contact DB. Then wrote a script that uses the GetLayoutObjectAttribute to get the code and a couple of start Variables to find the lat and longitude.
Set Variable [ $GM; Value:GetLayoutObjectAttribute ( "viewer" ; "content") ]
Set Variable [ $startLat; Value:Position ( Addresses::webpage ; "lat=" ; 1 ; 1 ) ]
Set Variable [ $startLng; Value:Position ( Addresses::webpage ; "lng=" ; 1 ; 1 ) ]
Set Field [ Addresses::Latitude; Middle ( Addresses::webpage ; $start + 4; 16 ) ]
Set Field [ Addresses::Longitude; Middle ( Addresses::webpage ; $startlng + 4 ; 16 ) ]
You could then set up a portal on a self join and filter it by
Left(TO1::latitude;X)=Left(TO2::latitude;X) AND Left(TO1::longitude;X) = Left(TO2::latitude;X)
substituting for "X", the number of places for each coordinate to get your desired radius.
Cool! What did you put in the URL sent to the web viewer to get back the lat and long? (others have asked for this in the past and I'd like to have that info on tap for the next time it is asked.)