Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 7101 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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.

Posted

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

Posted

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.

Posted

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.

Posted

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.

Posted

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

Posted (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 by Guest
Posted

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. :crazy:

Thanks again for the help on this,

Lynn

Posted

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.

Posted

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?

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.