what type of fields to use?? (search related, also)

[joelmold]

hi all,

<

i'm new here, so let me know if this should be posted elsewhere on the forum. apologies in advance for the length, and for my probably basic questions. -)

i'm developing a database for my work (a university) to track awards and honors received by professors. i'm running into a conceptual problem (due to my inexperience with filemaker) and i'm hoping someone can give me some ideas. i'm a filemaker newbie, but i'm willing to do what it takes to get this right.

first, a little about the database: it will be used to track awards, publications, fellowships, etc for approximately 250 professors. i will be the primary user, but i want to make it user-friendly enough that my boss can run reports without me present, and i want this database to last several years (so it must be flexible with updates).

i'm attaching a screenshot of how it looks now so you can get a general idea of what we're trying to do.

i've prepared basic filemaker databases before, but this one has me stumped with the way the publications and honors are displayed and stored and accessed.

regarding the awards, at first i thought i would do a check box list, with a list of the awards possible. then i could just check off the award if a professor received it. as you can guess, this quickly got out of hand since there are so many possible awards.

next i thought i would do a set of drop-down lists-- maybe 10 fields total (since some professors will have more than one award), all with the same award options in the drop-down list. the drop-down option list is easily editable so this seemed like a great idea. i named the fields "award1", "award2", etc, and assigned all of them to the same option list. this seemed to be the best option since it limits the displayed data for each person only to the awards they have received (instead of clumsy-ing up the display with all possible awards).

i thought this would work great until i tested the search function: search only works if the search query matches the field it's typed in. in other words, if the "nobel prize" award showed in the "award1" field, but i typed the query in the "award2" field, no results would come up.

does anyone have ideas how i can get around this? the drop-down boxes work great and keep my database free to a bunch of extra data, but search is really important. i'm thinking there must be some way i can search for a single term in more than one field, but i don't know how to do it.

this is the same problem with the recent books section: i need to be able to search by title and have it search across all the fields, or i need to be able to put in a year and have it bring up all the books published that year.

finally, in the future i'd like to implement a "summary" page that shows totals by department or for the whole group. ie, i could show which awards the classics department has received, or how many books the french department published in 2004, or the types of awards received by faculty and the number of each faculty receiving that award. does anyone have suggestions on where to read to do something like this?

whew, sorry this is so long. hope this is clear, but i'm happy to clarify if needed, or to provide the actual filemaker file. i'm also very open to broad organizational suggestions.

thanks in advance for replies!!

thanks,

joel

Edited November 22, 2006 by Guest

[LaRetta]

Hi Joel, welcome to FM Forums!

You can create a calculation (called a concatenated field) result is text with:

Award1 & " " & Award2 & " " & Award3 ... etc then search on this field for any awards. But you are seeing already how having multiple fields to hold similar-type data can be problematic. And your next question solidifies the problem with fields here. If a Professor wins 3 awards, a report will NOT be able to show each of the awards as record lines nor will it be able to sort, summarize or count them.

I suggest then that you instead have another table called [color:green](changed to Recognitions from Awards to broaden the meaning of the table). There would be a category field which would hold: Award, Book and so forth. When searching, you would just search for Category = Award and Name = "Professor bla bla" and AwardName = 'whatever' and you will find all matching records; or you can GTRR to them (Go To Related Records). In addition, summarizing by Professor, by Award Category, by Date ... the world will be at your fingertips. There will be no more hard-coding fields each time you add an award, no more filtering long, long value lists and no fighting to get your data in the forms you require. If you go the second table route, be sure to use unique IDs for your relationships instead of the Professor's name.

LaRetta :wink2:

Edited November 22, 2006 by Guest

[joelmold]

i'm somewhat surprised you were able to make any sense of my post-- looking back now it's embarrassingly incoherent! -)

just to clarify your response: i've seen the table tab in the "define database" menu before, but to be honest i'm not sure what tables are or how to use them. i'm reading the associated help files now but they're not exactly clear. is there a better tutorial you could point me to?

if i use a table as you suggest, will a professor's individual record be able to show all the awards received by that professor? it seems to me that i would still have to put in multiple similar data fields, but i'm probably misunderstanding...

thanks!

[Tim W]

Hi,

The basic idea is relational database theory. Do a google search and there are several resources that can help explain the terms and how they work.

RDBS (Relational DataBase Systems) are not unique to any program, rather the underlying theory which provides the framework for the program to operate.

HTH,

Tim

[joelmold]

i'm reading relational database theory at wikipedia (http://en.wikipedia.org/wiki/Relational_model) right now, thanks for the suggestion.

does anyone happen to have a sample file i could look at that does something similar to what i want to do? i'm looking through the samples posted here: http://www.filemaker.com/solutions/starter_solutions.html

thanks!

joel

[Fenton]

Here is a quick hack of one of FileMaker's templates. It stores both Awards and Books in one table, Accomplishments. That way you can show them as different on the layout (in tabs), yet report on them together.

It uses a calculation field with fixed text in it to auto-enter the "type" of accomplishment. I also did a quick "report" layout, with Subsummary parts to report by: 1. Person, 2. Type of accomplishment.

People_Awards.fp7.zip

[joelmold]

fenton, this is great. thanks so much! you certainly do deserve the "coolest guy in the room" award. i'm going to work on this over the weekend and will report back with what i come up with. thanks again!

joel

[LaRetta]

Fenton? When are you getting back from maternity leave?

I am confident that, when you do, they will re-activate your Cool Dude Award. Hang in there ...

[Fenton]

Sample data. Always difficult -] Actually that was from FileMaker's left-over value lists, which I was too lazy to change.