coffeec Posted August 8, 2005 Posted August 8, 2005 I have a question about field lookups. I have a equipment layout where there is a company that is associated with each piece of equipment. I have the list of companies appearing in a drop down list from a related client table. I cannot figure out how to add a new piece of equipment and have the associated ClientID appear in the equipment table. Is there a way to do this, I have researched matching field and lookup fields but cannot seem to figure it out. Thanks in advance.
SlimJim Posted August 8, 2005 Posted August 8, 2005 When you are adding a new equipment record you say you have a drop-down list of company names - is this attached to a Company Name field in your equipment layout? If it is then you will need a relationship matching Company Names (maybe as well as one matching ID numbers). Once you have matched the names then you can lookup the clientID via that relationship
coffeec Posted August 8, 2005 Author Posted August 8, 2005 I have the company names coming from the Client table. I do not have company names in the equipment table. Do I need to add the company names field to the equipment table as well as the clientID? Right now, the relationship is setup using CLientID only.
SlimJim Posted August 8, 2005 Posted August 8, 2005 No you do not need names as well. I misinterpreted you statement that you had a drop down list of names as meaning that yu were choosing a company name. If you have an ID field then the most straightforward way is to setup a value list of ID numbers showing values from the name field (to help identification) If you set up your ID field as a drop-down list with values from the that value list then the ID will drop into the field.
coffeec Posted August 8, 2005 Author Posted August 8, 2005 Thanks for the advice SlimJim. That worked for me. I appreciate the help.
coffeec Posted August 9, 2005 Author Posted August 9, 2005 Is there any way to avoid showing the ID Numbers. I want to show just the client names associated with those ID's?
SlimJim Posted August 10, 2005 Posted August 10, 2005 Does the Client name determine the ID? If that is the case then you could define a second relationship by Client Name, purely for the purpose of finding the ID, and Lookup the ID once a client name is chosen. There is then no need to show your user the ID and no need for it to be on the layout. Don't replace the ID relationship by the client name relationship it is simply an aid in picking the ID.
lcurrie Posted August 10, 2005 Posted August 10, 2005 SlimJim, I think this conversation applies to my question as well. I'm hoping you can help push me in the right direction for my solution. I have two tables, projects and companies. projects contains (among others) project_id company_id project_number companies contains: company_id company_name company_code I've related the tables using the company_id. I'd like to have a pop-up menu on the projects table of company names. This popup would be populated by the companies table. Once i pick the company name, the project_number will be assigned from a calclation of company_code and and a serial number. From a structural standpoint, I'm not sure how to accomplish this. I can get the company drop menu using a value list made up of company names, but that doesn't seem to actually create the relationship link that i'm looking for in order to use the company_code value in my calculation for project_number. I assume i could use a lookup to pull that information in, but i'm guessing there is a more efficient way to do this. Can you shed some light for me? Thanks for any help you can offer! Lynn
SlimJim Posted August 11, 2005 Posted August 11, 2005 (edited) It's pretty much the same answer. If the company name determines the company Id then you set up a second relationship between your two tables by company name. The drop down picks a company name and this relates to a company record from which you can LookUp the company ID into your projects table. That then sets up your main relationship and now you can do your calculation. If the company name does not determine the company Id then you have to produce a field which does identify the company ID (assuming that you do not want to use the ID itself). EDIT What I perhaps didn't make clear is that you will need a company name field in your projects table to do this. Edited August 11, 2005 by Guest
lcurrie Posted August 11, 2005 Posted August 11, 2005 Thanks for the follow-up. A couple questions though - If i have the relationship between projects and companies through company_name, is there any reason to have a second one with company_id? Do I create the drop menu in the projects table from a value list made up of company_name in order to create that menu? In other words, what populates the menu? Selecting the name as stated above doesn't seem to actually create the 'link' between the two tables. I've setup another company_id field set to lookup the value based on the company_name and it doesn't seem to work. I'm sure I'm missing something very basic here, I'm just not sure what. Thanks again for the help on this, Lynn
SlimJim Posted August 11, 2005 Posted August 11, 2005 To answer the first question. The company name field is to help find the ID. Companies can change their name (become Inc or ltd gain partners etc) and if you relate by name you would either have to have a new company record (which would make keeping track very difficult) or you would have to go through changing the name in the other tables and if you miss any - things can go very wrong. It is generally felt and with good reason that where you should relate by IDs with no meaning to your data. OK set up a field CompanyName in your projects table. Make a relationship to your company table by Company Name. Make a ValueList of the values of CompanyName from your companies table and set up the CompanyName field in the projects table as a Pop-up list using that value list. When you have chosen a CompanyName your project is related to a Company record by name. Setup the CompanyID field in Projects as Auto-enter by calculation = CompanyName::ID (or you can do LookUP of CompanyName::ID) This sets up the main relationship between IDs and the other relationship is now largely irrelevant. It does mean that you have a record of the company name at the time the record was created which can be useful.
lcurrie Posted August 11, 2005 Posted August 11, 2005 I think I have this working now, but i had to do it slightly different than I understood you explaining. When I created the second relationship between the projects and company table, it became an AND relationship and kept the other fields from populating. When I got rid of the relationship between the company_id fields and just created the relationship between company_name and company_name, it worked. does that sound right?
SlimJim Posted August 11, 2005 Posted August 11, 2005 Yes exactly right. To be certain we are on the same wavelength I am attaching a pdf of what I think that piece of the relationship graph should look like CompanyProject.pdf
Recommended Posts
This topic is 7101 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