Yet Another Drop-Down List Problem

[dawn26]

Hi everyone,

I posted a similar question earlier, the response I got contained good recommendations but I do not understand most of it, so I thought I would repost.

My problem is common, simple to many I'm sure, but try as I may - I couldn't figure it out. I have a drop-down list with values, I want these values to auto-fill at least five fields with various records through look-up. :

Main table = "Employees"

Field 1 = "OCCCode" (OCCCode is a drop-down list with values)

Value List = "OCCCode"

Field 2 = "JOB TITLES" (Job Titles contain records)

Field 3 = "STATE" (Is another drop down list. I want "STATE" to show related record from "OCCCode" and "JOB TITLES". When a choice is selected from the drop down list "OCCCode".

Field 4 = "MEAN DATA" (this is the mean data by STATE and by JOB TITLE")

Field 5 = "MINIMUM DATA"

Field6 = "MAXIMUM DATA"

I think I am having problems with relationships.

I tried hard to fix my table relationships while following the reference from the book "the missing manual" - to no avail. I have been racking my brain since thanksgiving, I am on the verge of going mad.

I can't get the drop down list and lookup to work. What am I doing wrong? Can anyone please tell me what's wrong with my table relationships, values, and fields?.

I wanted to attached a zip file to show my database, so you could see exactly what I'm talking about --- but I don't know how to do that here either.

Thank you so much in advance.

[Fenton]

Welcome to the forums, and congratulations for posting in the correct forum -] To attach a file to a post, first zip the file (Right (Control)-Click on the file, Create Archive), in the Finder. Then, after writing your post here, click the Preview button (below the post, lower left). Then, in that window, on the lower left is a choice to Manage Files, where you can attach a file(s). Choose the file, click the buttons to add and save it, etc.. Then Add the post.

If your file is very large, with lots of data, we might be able to figure it out from just a clone of the file. In FileMaker, Save as Clone. But it's better to post one with data, so we can see what results we're getting.

The concept about filtering value lists that is a little confusing is that you generally filter things one step ahead of the values you actually want. So the relationships are one step ahead of what you think of as the normal relationship.

I want "STATE" to show related record from "OCCCode" and "JOB TITLES". When a choice is selected from the drop down list "OCCCode".

This is a little confusing to me. Because you don't say much about "Job Titles" (you say it has "records", hence it may be another table, but you don't say). It is really helpful if you always specify what the relevant tables are, as a field does not mean much unless we know what its table is (and the general idea of what the tables records are supposed to be).

[dawn26]

Hello Fenton,

Thank you for responding to me (and for being online).

First, I hope you had a good thanksgiving.

I am attaching the archived file here, which was about 30 mb with records and all, I had to remove all other fields and graphics to make the file smaller after zipping. As I write I am hoping it will now load.

Yes, the field "job titles" is a separate table with records.

I have several tables that quickly became extremely convoluted to me relationship-wise (no pun intended here).

Anyway...I am hoping you'd be able to help before you became confused like me.

Elena

p.s. Your pic is cute.

Welcome to the forums, and congratulations for posting in the correct forum :-] To attach a file to a post, first zip the file (Right (Control)-Click on the file, Create Archive), in the Finder. Then, after writing your post here, click the Preview button (below the post, lower left). Then, in that window, on the lower left is a choice to Manage Files, where you can attach a file(s). Choose the file, click the buttons to add and save it, etc.. Then Add the post.

If your file is very large, with lots of data, we might be able to figure it out from just a clone of the file. In FileMaker, Save as Clone. But it's better to post one with data, so we can see what results we're getting.

The concept about filtering value lists that is a little confusing is that you generally filter things one step ahead of the values you actually want. So the relationships are one step ahead of what you think of as the normal relationship.

This is a little confusing to me. Because you don't say much about "Job Titles" (you say it has "records", hence it may be another table, but you don't say). It is really helpful if you always specify what the relevant tables are, as a field does not mean much unless we know what its table is (and the general idea of what the tables records are supposed to be).

HRIS_simple1B.fp7.zip

[dawn26]

Here is the sample record to go with the file.

Thank you so much.

Elena

mockup_database.xls.zip

[Fenton]

Well, I think we're going to have to back up a bit. I don't think we can progress to filtered value lists until we establish an adequate relational structure. Which I cannot see that we have; possibly much of it is there, but I can't tell yet. Perhaps if you imported more data into each of the relevant tables we could see better. We only need a fake Employee or two. Nor do we need a lot of data, and only in the relevant tables. The data would help us to see whether values are unique to a table or not.

One thing for sure, the OCCCode should not be a global field. It should be a regular field, number or text. You cannot use a global field as the target of a relationship for one thing, as you have done.

I've straightened out the Relationship Graph a bit, according to fields sorted by name. So I will upload the file again.

It is likely that what you wanted to do is fairly simple. But still a little confusing for us (me anyway) to figure out.

HRIS_simple1B.fp7.zip

[dawn26]

HI again Fenton, Thank you for reviewing my file and for fixing my relationship structure. I am attaching the same file with a couple of records. I also wrote the things that I would love to happen with the database.

If we can fix this and I am able to finally understand how to move forward from here, I don't know how to repay your kindness. Maybe a donation or something.

Thank you so much.

Elena

HRIS_simple1D.fp7.zip

[Fenton]

Here is something. Whether it's the correct something remains to be seen. The general problem, for me, is that you have several tables that I do not know what they are. I imagine you know what they have to do with each other. It seems to me that there is redundant data. I cannot see any reason why several of the key fields are duplicated in several tables. That makes it very difficult for anyone to guess what their relationships should be.

As far as Job Titles, it is has a unique auto-enter serial ID. That is what should be used in relationships, not the job title name. That is how I'd do it anyway. It does not seem like you want people to type in the names here. You could possibly use some other "data id" field, but:

1. Best practice is to not use a data field* for relational ties. Data fields can change later, breaking your relationships.

2. There is no real reason to use data fields. A hidden ID field can look them up.

3. Since you created an auto-enter ID field, you should use it, or lose it. Otherwise you'll be continuously confused as to which is the "real" ID field.

*By "data field," I mean either a "user entered" field, or data supplied by someone else (codes, etc.). Yes, the later may be more stable. But you have no control over that data, and could be unpleasantly surprised later.

The ID can be hidden from users by specifying "Show only values from the 2nd field" in the value list itself.

So, in Employee, the user chooses the Job Title ID. A relationship to the Job Titles table based on this id is then able to Look Up the Client Job Codes (which you label as Job Code for some reason, why "client"?, why plural?). It can also look up the Job Families (why plural?).

I suppose it could also lookup the OCCTitle and OCCcode, if the JobTitle ID field was also in THOSE tables. But I don't know whether that is a valid logical structure, as I don't know what they are, nor whether JobTitle ID is UNIQUE to their tables (which it would have to be to LOOK UP their data; though it could be used for a filtered value list if it is not unique to their table). Nor do I know why OCCTitle and OCCcode are 2 separate tables; seems like 1 entity to me.

So, it would be very helpful if you could import some data into those tables. And/or explain exactly what they have to do with Job Title. Obviously there is some connection. You have many tables here, but it is not clear why/what they are, nor what their connections are.

HRIS_simple1E.fp7.zip

[dawn26]

Fenton, thank you so very much. This is exactly what I wanted to happen.

I apologize for not deleting the other tables before sending the file over, I should have taken them out. I will take your technical advice about data fields relationships.

Thank you so much again.

Elena

Here is something. Whether it's the correct something remains to be seen. The general problem, for me, is that you have several tables that I do not know what they are. I imagine you know what they have to do with each other. It seems to me that there is redundant data. I cannot see any reason why several of the key fields are duplicated in several tables. That makes it very difficult for anyone to guess what their relationships should be.

As far as Job Titles, it is has a unique auto-enter serial ID. That is what should be used in relationships, not the job title name. That is how I'd do it anyway. It does not seem like you want people to type in the names here. You could possibly use some other "data id" field, but:

1. Best practice is to not use a data field* for relational ties. Data fields can change later, breaking your relationships.

2. There is no real reason to use data fields. A hidden ID field can look them up.

3. Since you created an auto-enter ID field, you should use it, or lose it. Otherwise you'll be continuously confused as to which is the "real" ID field.

*By "data field," I mean either a "user entered" field, or data supplied by someone else (codes, etc.). Yes, the later may be more stable. But you have no control over that data, and could be unpleasantly surprised later.

The ID can be hidden from users by specifying "Show only values from the 2nd field" in the value list itself.

So, in Employee, the user chooses the Job Title ID. A relationship to the Job Titles table based on this id is then able to Look Up the Client Job Codes (which you label as Job Code for some reason, why "client"?, why plural?). It can also look up the Job Families (why plural?).

I suppose it could also lookup the OCCTitle and OCCcode, if the JobTitle ID field was also in THOSE tables. But I don't know whether that is a valid logical structure, as I don't know what they are, nor whether JobTitle ID is UNIQUE to their tables (which it would have to be to LOOK UP their data; though it could be used for a filtered value list if it is not unique to their table). Nor do I know why OCCTitle and OCCcode are 2 separate tables; seems like 1 entity to me.

So, it would be very helpful if you could import some data into those tables. And/or explain exactly what they have to do with Job Title. Obviously there is some connection. You have many tables here, but it is not clear why/what they are, nor what their connections are.

[Fenton]

I didn't want you to delete the other tables (unless they are not needed). What I thought would be helpful would be to see some data in them. For one thing, I cannot see why you need OCCTitle and OCCcode to be 2 different tables. Nor can I see why some of the fields, like Client_job_codes are in so many tables.

I have often seen this "shotgun" approach by beginners to relational design. But redundant fields are not a good idea, and just add much confusion; it's difficult for someone who is unfamiliar with your industry to figure out the logic, when it is in fact not logical. You should have the fields you need, and ONLY the fields you need, in each table.

Perhaps someone who knows this industry better could figure this out. But I don't know exactly what these other things are, their purpose, or their relationships (even in a general sense). So I can only advise re: Job Titles and its ID, the rest is just a kind of vague.

[dawn26]

It was really hard to provide a comprehensive representation of what I needed done with the database given the file size restriction in the forum. However, I've entered more data here to make it a bit more easier to see.

You may look at this as a salary survey, where one Job Title can appear several times in several states because each state has their own salary levels for a job title.

"JobTitle_ID" is connected to "Client_job_codes" and "Job_families" because this is the initial point of reference for researchers (you've already accomplished this).

Then "JobTitle_ID" is also connected to external data references which are the results of survey and research.

[color:purple][color:brown][color:red]I have developed a process that I use to match my client's internal Job Titles with the industry's Occupatinal Title (OCCTITLE), it is based on a statistical analysis. Calculations, criteria, factors, I've already built using Filemaker.

The external references that I am speaking of, are the following fields:

1) OCCCODE

2) OCCTITLE

3) ST

4) AnnualMEAN

5) AnnualMEDIAN

6) AnnualPCT10

7) AnnualPCT25

8) AnnualPCT75

9) AnnualPCT90

I would like for the users to be able to find "AnnualMEAN" (and so on), by first selecting from "JobTitle_ID" and then selecting from "ST".

Also, by selecting fromk the "JobTitle_ID" the field for OCCCODE and OCCTITLE should be automatically filled or entered (just like a "look-up").

On the multiple tables bearing fields that also appear on other tables, I was under the impression that this is necessary in terms of relationship modeling. I have depended extensively on the book called "The missing manual" and the explanations on several pages have relationship tables similar to what I've done.

The whole solution have other database (modules) that must refer to the tables that you're seeing here, for example the OCCCODE, OCCTITLE and JOB TITLES should also appear on other database with different fields and different usage, albeit their purpose is to show other types of information regarding the JOB, base on JOB TITLES, OCCODES, and OCCTITLES.

I hope this shed a bit more light.

Can you email me on my personal email? Maybe if I send you more information (like the source data in exel sheet) you'd be able to see clearly.

HRIS_simple1F.fp7.zip

[Fenton]

The thing is, I do not need more "employee" records. Just a couple is fine, so you can remove those. What I (we) need is more data in the other relevant tables, especially OCCTitle and OCCcode tables. Since you say they could be looked up from just the Title_ID; but I am skeptical. That would only work, as a lookup, if TitleID was unique to each of those tables. It is easier for me to see that from the data.

Part of the problem here is that you are approaching this as best you can from reading about it. But the idea that you need to put several key fields in tables may or may not be correct. There is no way to generalize what fields you need in tables, other than "you need what you need for connectivity and functionality," which doesn't really help much; every database is different. Whereas having the extra fields just confuses anyone trying to help you, as they can only assume they belong there, when sometimes they do not.

The fields, "Client Job Codes" and "JobTitle_ID" (and others) are in several tables. Basically I am not convinced that you need all these tables; I am fairly certain you do not need the same fields in so many tables; that would be a maintenance problem if nothing else. Perhaps you do, but I am unable to see which you do need from your descriptions. Just telling us that "this should look up this and that," doesn't tell us whether it actually can do that, given the current structure. If we could verify that the structure was correct, and could see some data to prove it, we would know (hopefully) what could be looked up and how.