Find form that searches multiple tables

[daiinc]

Okay...I'm in way over my head here. My very limitied DB exp is with Access, so I might be over thinking this database in Filemaker. I'm just following orders on how this thing is supposed to look and operate...so bear with me. This is going to be long. The REAL "find" question is coming, I swear.

The DB is to track resume data (pretty straight forward). The end result should be a single form with the general stuff at the top (name, retired rank, status, etc.). The bottom of the form has three tabs.

1. Experience - this is supposed to be a series of check boxes. We're tracking functional expertise, where they were stationed, specific aircraft, etc. [THIS IS THE PART THAT'S GIVING ME TROUBLE]

2. Contact Info - For both biz and personal, standard fields of Address, City, State, Etc.

3. Misc. - Large text field for education and other

At first glance, it seems easy. One table with Value Lists for each of the areas on the form as a checkbox set.

But they want one of the reports to be a huge nightmare checkbox summary...with all possible expertise stuff across the top, names along the side and checkboxes (filled in appropriately, of course) in each cell.

The only way I know to represent that is to have a separate field for each "thing" and create a "toggle" checkbox on the main form (I found the idea of doing a one item value list on this site). Then when I design the report, I can just line the fields up along the top of it. I created several different tables for the types of data (i.e., one table is called "Services" and the fields are Air Force, Army, Etc.) The main Employee table has the general stuff and an auto-serial ID number. That ID# is related to a number field in all of the other tables.

So I've done that and it looks beautiful, fills in records like a champ, report is just what they want. But solving that problem has created a FIND problem.

I can search the general stuff fine and get good results. But when I check the boxes on the first tab and hit FIND, I get a "operation cannot be performed because one or more of the relationships are invalid"

I'm stuck...should I just shove everything into one table? Then how would I do the report they want? How can I get my form to work? I'd like to be able to check a few boxes on the "bottom" of the form (from various tables) and get results returned on the entire form for employees that meet that criteria.

I hope I've made sense! : Thanks in advance for any advice!

Leslie

[aldipalo]

Hi Leslie:

Welcome to the forum. Is it possible to post a copy of your file so we can see what you are doing?

Al

[daiinc]

Thanks for the kind welcome!

A copy of the file is attached. Don't be too hard on me!

Leslie

Resume_Database_Copy.zip

[aldipalo]

Wow. Very nice GUI! I think the problem is that you are trying to use multiple yes/no fields in multiple tables and they are not related properly. It is a rather large TOG already. Can't you set up these tables as Value lists and the form just show the skill sets of the specific employee rather than ALL skill sets? You would have fewer tables and it would be MUCH easier to maintain.

Perhaps someone with a lot more experience than I can take a look and see if they can point you in the right direction.

[Ender]

I don't think the structure is appropriate. You have a one-to-many relationship for each group of Experience questions, and within those groups, a separate field for each question anyway. Your current layout could be accomplished with all the fields in one table. You can use a single field with a checkbox set for each group of questions.

Another structure that's useful for certain reporting requirements, would be to have an Employee table, with all of the Employee and Contact Info fields, and another table for Experience, with fields for EmployeeID, Group, and Experience. These might be entered in a portal of Experience, with the Group field being a list of the Group Types (General, Services, Functional Area, etc.) and Experience being a conditional value list of the possible choices from each area. This would result in a report that gives counts for each type of Experience, and maye subtotals for each Group.

[daiinc]

Thanks for your responses! I had a feeling that my many many fields with many many toggles was clunky and impractical. I did originally set it up as checkbox sets with value lists for each "category". But I just couldn't translate that into the "table-like" report they were after...a matrix with all of the experience, etc along the top and all of the checkboxes that were checked for each employee per row.

Wait, as I'm typing this, I had a Eureka! If I set this up using Value Lists as you guys suggest, couldn't I just create a report which took the checkbox sets and lined them up vertically in "rows"?

Okay, I've attached a quick picture of what I'm after. Will this work?

I'm sure there is some other fancy scripted way to do this, but I'm just too much of a newbie to execute it.

THANKS AGAIN!!

Leslie

report.bmp

[Ender]

couldn't I just create a report which took the checkbox sets and lined them up vertically

Yeah, that may work. You'd need to rotate the field with the checkboxes applied, then shrink it so it fits within the Body part of your layout.

[daiinc]

Is there a way to edit the layout within the checkbox set? I only want the labels to be in the header of the report. So really I just need each checkbox set to be a row of lined-up boxes.

[Ender]

That's what rotating and cropping the field should do.

[daiinc]

Ah, got it! I was putting the set on a single line and then rotating.

I think this does it. This won't ever be a huge database and only a handful or folks will have access to it. So I think if I implement all of your suggestions, I'll be good to go.

Thanks again.