larine Posted July 19, 2007 Share Posted July 19, 2007 (edited) Hi, I have a table of workers with contact info. Each worker is also associated with several keywords according to what they do. Should I make another table with workers and keywords? If so how should I lay it out? ie. should I have a Name field then Keyword1, Keyword2, etc? Or should I make a list of Keywords and put the names associated under each keyword? I want to have a page where people can search for a worker using various criteria such as keyword or city. FYI There are about 700 workers. Thanks in advance! Edited July 19, 2007 by Guest Link to comment Share on other sites More sharing options...
Ender Posted July 19, 2007 Share Posted July 19, 2007 Yeah, use a Worker-Keyword table, with fields WorkerID & Keyword. Link it to your Worker table via the WorkerID, and show the Keyword field in a portal. If you have a specific set of keywords you want to choose from, attach a value list to the field. The search itself can be as simple as selecting a keyword in the portal while in Find Mode. If you want more advanced searching, like with AND and OR options, you're probably best making an interface that makes that easy to do, then script the conditions for the Find. Link to comment Share on other sites More sharing options...
larine Posted July 23, 2007 Author Share Posted July 23, 2007 I have a table of workers/contact info and a table of workers/keywords associated with their work. I have created a layout that displays the fields for workers names and contact info. There are pop-up value lists for some of the contact fields and a search button which allows a person to find all workers in a certain city. On the same layout, I want to be able to search by keyword as well, but I'm confused as to how to do this because each worker has several keywords associated with them (Keyword1, Keyword2, etc). Thanks in advance for your help! Link to comment Share on other sites More sharing options...
JesseSFR Posted July 23, 2007 Share Posted July 23, 2007 If you want them to be able to search all keyword fields at once you may have to script your search. If you would like to have them be able to just search keyword1 or keyword2 etc. You can throw those fields on the layout and it will just work as you expected. Another thing that might be beneficial to you, is to make the keywords into their own table. It's tought to tell if this is the best option with the limited info at hand but it might be. Link to comment Share on other sites More sharing options...
larine Posted July 23, 2007 Author Share Posted July 23, 2007 Actually, I did create a separate table for the keywords. I'm not sure how to make a script that would search in multiple fields though. Also, I would like to be able to have on the original search page a drop down box with a value list containing all the values from the 7 Keyword fields. Is there some way of doing this? Link to comment Share on other sites More sharing options...
Fenton Posted July 23, 2007 Share Posted July 23, 2007 When you are "searching for multiple values" the first thing that comes to my mind is a relationship; which by default will match any of its lines. But that more or less assumes that all your keywords are in one field. I do not understand why you would have "Keyword1", "Keyword2" fields. It just makes everything more difficult. Link to comment Share on other sites More sharing options...
larine Posted July 24, 2007 Author Share Posted July 24, 2007 I do not understand why you would have "Keyword1", "Keyword2" fields. It just makes everything more difficult. I have 2 tables - one table with worker/contact info, and the other is worker/keywords. In the worker/keywords table, each worker has several keywords associated with them. Therefore I have named the keyword fields Keyword1, Keyword2 etc. I found this confusing, and if I've done this wrong, please let me know a more appropriate way to do this. Link to comment Share on other sites More sharing options...
Fenton Posted July 24, 2007 Share Posted July 24, 2007 If you have 1 Keywords field in each file, with values separated by returns, then it is very simple to match with another return-separated field (such as a global text field viewed as checkboxes). Or, if return-separated is awkward, or too much of a challenge for data entry, then you can enter as comma-separated, and let a calculation Substitute ( field; ", "; ¶ ); though I'd prefer just entering as return-separated. It is almost always a problem to have field1, field2, field3. It is usually a sign of "relationship phobia," a common but debilitating condition most often found in newbie database developers :-] Link to comment Share on other sites More sharing options...
larine Posted July 25, 2007 Author Share Posted July 25, 2007 Hello, I have two tables - one for workers/contact info and another with workers/keywords, and a relationship between the workers fields. What is the best way to set this up so that I can search through the keywords for workers? Currently I have 7 fields (ie Keyword1, Keyword2, etc) so that each worker has 7 keywords associated with them. I've been told this might not be the best way to set it up....does anyone have any advice? Thanks! Link to comment Share on other sites More sharing options...
mr_vodka Posted July 25, 2007 Share Posted July 25, 2007 Hi Larine. Please do not double post. All these forums are pretty much browsed by everyone even though the topic categories may be different. I am going to merge the posts as double posting confuses readers. Link to comment Share on other sites More sharing options...
mr_vodka Posted July 25, 2007 Share Posted July 25, 2007 As the others have already posted, it doesnt really make any sense to have keyword 1,2,3 etc. Since you now have a related tables of keywords to the contacts, have a seperate record for each keyword. You will no longer be limited to 6 keywords since you can have as many records as you want. Once setting it up properly, you can perform a find in the keyword field for what you are looking for. I would automate the find process by using a global text field to enter the keyword that the user would be looking for. Then as part of the find script you would perform a find in the keyword field of with the criteria entered in the global field. The go to related record matched on found set will display all related contacts with that found keyword record. Freeze Window Go to Layout ["KeywordLayout"] Enter Find Mode [] Set Field [KeywordTable::Keyword; gKeyword] Perform Find [] Go to Related Record [show only related records; Match found set; From table: "ContactTable"; Using layout: "Contacts" (ContactTable) ] P.S. Dont forget to compensate for error trapping. Link to comment Share on other sites More sharing options...
larine Posted July 25, 2007 Author Share Posted July 25, 2007 (edited) So sorry for the double posting. I'm very new at this and extremely grateful for all of your help. From what I'm understanding from your comments, I should be making a table with a record for each keyword then listing the workers associated with each keyword, not the opposite (listing all the keywords associated with each worker). I had thought of this but it seems that it would take a lot longer with about 700 workers and 7 keywords per user...however if this is the best solution I will just bite the bullet and begin making a table with these records. Am I understanding you right? Again thanks for the help. Edited July 26, 2007 by Guest Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6114 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now