"Dis/re-associating records by simple key manipulation seems like such a fundamental advantage of relational db's but I can't for the life of me figure out how to do this. "
You create a value list called Addresses from the House table (usually called Addresses). First create a calculation which concatenates street, city & Zip (as Address1 & ", " & City & " " & Zip). Next, select 'use values from field'. In the left pane, you select the pk_houseID and in the right pane, you select the calculation. Below, specify 'all values' and select 'only show values from second field.
Then go to your People, fk_houseID field and attach Control Style (Inspector > Data) of Pop-Up Menu. Select your newly created value list in Values From. When you pop the field it will display the addresses. When you select one, it inserts the ID into the field. After you leave the field, it displays the address.
This is how we re-associate the IDs with their real values. If you want the person to select the state (or zip) and then the addresses filter down to only those associated addresses, it is called a Conditional Value List and it filters. One good example would from Comment (go to post #5 in that thread):
Wow, LaRetta I think you just blew his mind. LOL I am trying to look at this from a beginner level and thinking I never would have understood that even though you expalined it perfectly. hehehe
I think perhaps we need to point our friend in the direction of some reading on basic structure of a relational database, because LaRetta...you know as well as I, if someone can't grasp the whole "relational" part of a database and think in 3D they will never get it. Sounds to me if he just had a good read on the basics of a relational database the ole lightbulb just might pop on.
Just in case...going to go see if I can dig up a really easy to understand basics tutorial that will get him up and running.
The biggest mistake we all make (in my opinion) when we first start with relational database development is we try to make it waaaaaay more complicated than it really is. Once you get the "OH!!!" of understanding everything will just start to make sense to you.
Primary Key - All tables have a primary key, which is a way to make that record unique in it's own way. This keeps us from getting data mixed up, keeps things all organized.
Foreign Key - Ok, bare with me here...remember how I told you we use primary keys in our tables? Ok, and remember how this is a RELATIONAL database? So I need the two tables to get along with each other...so we need to introduce them.
Table 1: my primary key is 1234 ( example field name: kp_Table1_ID)
Table 2: my primary key is 7896 (example field name: kp_Table2_ID)
So their ID is almost like their name isn't it?
So I introduce Table 1 to Table 2 by giving eachother their name like so...
Table 1 will now have its primary key of kp_Table1_ID and a foreign key of kf_Table2_ID
He holds on to Table 2's ID or name, now they are related.
Just a very VERY basic little info on keys...now going to find you a good tut on relating tables within a database.
And here is an excellent tutorial...
It has two links that explain things in further detail depending on which area you want to know about next. But we're not chasing you away now...still feel perfectly free to ask questions here at any time. I know LaRetta or Phil either one can always offer some great advice and assistance, and I am here often to help whenever I can with what I can.
And we're just the regulars here...you have an entire community here so don't get frustrated, we all are here because we love and believe in FileMaker and we are happy to help you if we can.
Thanks to both of you for your very thoughtful and thorough responses! You renew my belief in the intrinsic goodness of fellowship : ). I am obligated to try to be just as thoughtful in expressing my appreciation.
LaRetta's solution was perfectly clear to me, but, rjlevesque, thank you for taking me under your wing (you are right to assume that there are gaping holes in my understanding of RDB's). In fact, I had already implemented that solution in several variations. Unfortunately, I used a dropdown and I never thought to try the pop-up conrol (I knew the answer must be simple!). Now I have a new problem. If I include in the Value List the option of "Other...", it presents a dialog with the "House_Id" field value which is meaningless and untenable as an option for creating a new, complete record in the Houses table. Can I trigger a script from the selection of the "Other..." option to change that behavior? Should I use a scripted button or is there a better solution?
@ rjevesque: I appreciate your defense of simplicity. As an experienced pragmatist, I'm sure you'll appreciate my beginner's idealism; as you said, "Been there, done that". Call me stubborn, but I'm approaching this from the point of view of reducing the amount of duplicate data, decreasing the propensity for runtime error, extensibility and providing the user with an elegant, usable interface. A category table just makes more sense to me than a category field. I'm aware that this approach could become fractal ad infinitum, but I think Idealism is a required course at the University of Pragmatism. I'd be interested in you predictions about how I might feel about it three months from now.
Thanks again. I look forward to furthering.
Thanks for the edit before the quote ; )
Perhaps yours is just a case of "exitus acta probat" where your particular need warrants that particular frame of thought. Plus, I must admit it does make a huge difference as to how large you expect the database to be. If it is not a stable foundation, when the subject grows it will become unstable and throw spurious erros or even crash.
------ quote ------
Call me stubborn, but I'm approaching this from the point of view of reducing the amount of duplicate data, decreasing the propensity for runtime error, extensibility and providing the user with an elegant, usable interface. A category table just makes more sense to me than a category field.
----- end quote ------
- no duplicate data in the manner I suggested, that was my point exactly...not even any unneccessary extra weight to add to the application like extra tables.
- runtime errors should not exist regardless, FileMaker creates your runtimes...you have no control over that, the wizard does it all.
- Elegant User Interface not as important as User Simplicity and ease of understanding. End-users want to be able to open the software and just use it. (a web 2.0 look is always nice, very minimalistic)
Speaking from experience as a software engineer who designs GUI for his software for a living:
BSSE - OOP - UNA - DBA
But you see that is the beauty of relational database design, you are given a set of guidelines, then after that...pretty much your only limitation is your imagination for creativity.
Uh oh...I spoke to soon. LaRetta's suggestion works as expected with a Value List whose second field is "text" (and presumably other data types). However the "calculation" field in the Value List is not working. The list is populated with only data from the first record in the related table and selecting that value doesn't change the value of the foreign key field. Anybody?
Check your calculation and make sure that it is a store/indexed field. Use calculation fields in such value lists all the time...
BTW-I'm using FMP 11.0v3
When you're done teaching me FileMaker you can teach me how to post a link. ; )
Open up the calculation field's definition and change the return type from Number to text. (It's a drop down in the lower left corner of Specify Calculation.)
Oh man! You're the greatest! It would have taken me another week to find that and I only have three weeks left on my trial version. I'd like to buy you a beer!
DOH! Dang it Phil! One of these days I will beat you to the answer! LOL I was just about to tell him about his calculation.