I just really confused myself - stuck.

[dmontano]

I have been creating all my tables, in about 20 databases, with a primary keyid field in each table. I understand the requirement of having a unique identifier. That's about as far as my understanding goes.

 

All has been going well - up until now. What I have been doing all along - and I think this is wrong - is creating my relationship joins by selecting an item like: company_name in one table and joining it to another table with a field: company_name. Not joining the primary_id_key with a foreign_id_key. They are in those tables - I just have not figured out how to connect and then get them to work.

 

I thought this was easy - has been working so far. However, I see in all sorts of references that the examples are being joined with primary and foriegn key_ids.

 

Have I made a big mistake? I primarily have used these joins to allow me to define a drop down value list based on that relationship I created to populate related fields in the other table layouts. As I build out - I am really getting hammered because I just dont get the relationship tying.

 

I am beginning to think that I have been basing my "unique" relationship on just that criteria (Company Name) and I have a feeling this is flawed.

 

Should I create joins at the very beginning of making relationships based on the primary to foreign id keys? If so, what do I do now? Do I create additioanl table occurrences and make the joins after the fact, or do I have to break my current joins?

 

I must admit I have spent hours and hours and days over this and in my quest to make the keyid joins I am destroying the functionality I had to begin with. I have read and reread the Missing Manual book I have and I am really confused. There has got to be a more intuitive way for me to connect this stuff.

 

I do not know if I have just worked too many hours every day and night in a row - or if I just uncovered my novice mistakes.

 

Any takers on this one?

[Inky Phil]

I am afraid that I made this error on my first attempt also.

 

You are correct in that you should use numeric id's (pref. auto generated) as your links for your relationships. This will allow you to store the company details in one place but display them wherever a record is related to that company.

 

The advantage of this is that should, for instance, the customer name change (unlikely I know but it does happen), you will only have to change it in the one place, rather than in every record that holds the name as you would at present.

 

All of your present functionality can be retained. You simply set up your relationship to be customer id no1=customer id no1 rather than smith and co = smith and co.

 

You will be able to utilise your current relationships and a simple script to transfer keyfield id no's and then all that you need to do is reset your relationships and you will be away.

 

Let me know if you need any help with the procedure to line up the the new id's

 

HTH

Phil

[dmontano]

Thanks Inky,

 

I only have dummy data populating the DB now. So, I am assuming I will not need to script anything to get the correct id joins - as long as I:

 

1. Delete all dummy records in all tables,

2. Reset the auto-increment serial number back to the correct starting point, for example: "0001_vnd_cmp_keyid"

3. Fix my relationships, value lists, and scripts, and layout definitions of fields.

 

I am really struggling with this - I have attached a jpeg of my most recent Relationship Diagram hoping that will help show my mess.

 

Any help is appreciated, Thanks.

[HBMarlowe]

Start with your unique, auto-enter number field in Table1. Call it ID. In Table2 create a number field called Table1ID. Define the relationship between the two tables as ID=Table1ID.

 

On a layout based on Table2 enter a number in the field Table1ID. That record is now related to the record in Table1 that has that ID number.

 

I hope that helps.

[Inky Phil]

It may seem foreboding at the moment but really it isn't.

 

You seem to have the procedure in your head and as HB says, start at the beginning and allocate an autoenter number to the keyfield in your first table. Then follow your own point 3 and you will find that after the first set of alts the rest come quite quickly. A couple of hours work max and you will have a much more secure and 'proper' system than you have at the moment.

 

However daunting this might seem at first I can guarantee that you will regret it , sooner rather than later, if you do not make these changes. Please trust me on this one. I know....I have been there.

 

 

Once you have done these changes all that you have to do is make sure that as related records are created , that they contain the correct id. This is also quite straightforward. Get back to us if you need help with this

 

HTH

Phil

[dmontano]

Thanks HB and Harlow,

 

This epsiode has been a great reality check for me - I thought I was actually making some major headway and on my way to having a good grasp on using FileMaker for my needs - and more importantly getting my solutions implemented.

 

I thought I understood the unique identifier, but I was wrong. Here is my current understanding:

1. It is required to uniquely identify records within a table. Understood.

2. I create my unique identifiers with alpha-numeric codes to start so I can always tell which table they have come from. For example: 0001_cmp_ven is the unique identifier for the record in my "Vendor Companies" table, and 0001_emp_ven is the unique identifier from the record in my "Vendors Employee" table. Understood (For my sanity - I know I do not need the alpha suffix in the identifier code)

 

Here is where I believe I have had a misconception:

1. Since I have created these unique identifiers as auto-enter in EVERY table I have ever created, I felt like I was done.

 

2. I thought all I had to do was draw join lines in the relationship diagram to "say" I need this field from this table - in this other table.

 

Here is where I think my mistake was and still is:

1. I have joined all my tables in a fashion similar to this:

Join the field (company_vendor_name) from "Vendor Companies" to the (company_vendor_name) field in "Vendor Employees". That was it.

 

I then populate the layout with the "related fields" I need. Has been working fine. I now understand it is the related record that will be flawed due to my "not-unique join" that will take the hit as Inky pointed out.

 

I think I understand his point in that my relationship join is flawed because these two table have to combine and create a unique identifier combination. For example, my join field should really behind the scenes be saying (0001_emp_ven) = (0001_emp_ven) -- in the related layout.

 

So, if that is the case, my mistake was using a field other than the unique identifier in each table join?

 

So my problem was / is that even though I need to display the vendor employees name so people can make the correct choice, I have to first have them select the unique identifier field that I have defined as my join, but they will have no idea what that means - either will I.

 

So, do I have to create a value list and use the second column to display their names after the unique identifier so they can see it in the proper context?

 

If so, I really hope there is a better way because "seeing" the unique identifier looks - unprofessional.

 

If I can wrap my head around this (with anyone's help) I already have questions (still) lined up. For example, what is the true first step when you begin joining tables?

 

Connect Primary Keys to Foreign Keys without any other criteria in that first join?

 

Then use another set of table occurences to get the vendor employee names to match up?

 

I have stopped working on my files and have been doing nothing but reading and searching for answers....

 

I have been trying to build an "Entity Relationship" diagram and how I feel so small now....

 

Any help is appreciated.

[dmontano]

I meant thanks HB and Inky,

 

Well if my assumption is correct, I am absolutely puzzled with what I should do with all of the other databases I have built. They are all connected via the method I describe (company name = company name). I do in each instance ensure that the field contents are unique via a unique qualifier - however, I can see that this is not the right way.

 

I want to do it the right way. So I am willing to rebuild everything if I have to. I just want to make sure I know what the right way is.

 

I am afraid what appears to be the answer from what I am reading is not very sophisticated - and that leads me to believe I am still not getting it.

 

For example, in each database layout, I have 2 drop downs that allow a selection as to which employee of ours is the "publisher" of this table, and which one is the "administrator" of the table.

 

Next to each name, is an email field for each name that auto populates with their respective email and a script button that you can select to send and email to either the "Publisher" or "Administrator". Works just fine.

 

However, I never created this with a table occurence that joins via the employee keyid - I simply joined using the employee full name field.

 

My layouts have been finished for quite some time - and there really isn't room to add another field and label for the "employee_idkey" to go in order to trigger the related data for the "Publisher" name and "Publisher" email fields - and it really seems absolutely inappropriate. I must be missing something here.

 

That would mean that I have to have 3 fields and labels just to display and properly relate the two fields that I really want - their name and email.

 

I know I am missing something here.

[LingoJango]

Value list: you use a value list that is based on the key field but displays only the name field. Look at the value list menu and you'll figure it out.

 

Put the key field in your layout under the name field, same size, and if the name field is transparent make the text color and fill color of the key field the same as the background.

 

Make the name field non-enterable in Browse mode and the key field non-enterable in Find. Make the key field a drop-down using the value list you just made.

 

You do get to see the ID for a moment, when you click into the field, but your attention is usually drawn to the names in the dropdown and that should be okay for most users.

[dmontano]

Thanks Lingo,

 

You are saying stack it and configure the two fields to display and provide interactivity as I see appropriate. I thought of doing something like that - my actual profession is graphic design and this is common to control elements via a layers approach. So, I can see where you are going with this.

 

What do most db designers do in these instances?

 

Option 1: Separate key field that is always visible on the layout

Option 2: The technique as you suggested

Option 3: ???

[LingoJango]

I think you should ask that question in the Interface Design section... or look at Harry Glos's latest megathread. smiley-smile There are differing opinions, but I've personally been convinced by David Head that the real key fields used in relationships should be hidden under the hood and never be seen by ordinary people.

[dmontano]

I would agree with your agreement with David Head about the ID field not being visible to end users. Pardon my expansion of my original topic into this area of conversation - I am learning as I go.

 

I am actually searching the forum now and seeing what you suggested appear in more than a few posts so it leads me to believe that must be the way to go.

 

I do appreciate your help.

[dmontano]

I have searched high and low to find David Head's "take" on creating related joins to key fields without the "user" ever seeing it, as referenced by Lingo earlier.

 

Anybody know where I can find that? I am continuing to post here only to get this last question answered as my question relates to earlier posts iin this thread.

 

Any help is appreciated.

[LingoJango]

It's this thread.

[LingoJango]

It's not a point specifically about related joins; it's a general point about primary key fields never being "out in public".

[dmontano]

Thanks, I read it. I also posted in a (hopefully) more appropraite locale.