Since you are trying to select from this value list to enter a value in the Jobs table, you need to link Jobs to an occurrence of Customers.
The relationship would be:
Jobs::CustomerType = Customers|ByType::CustomerType
Then you set up a single value list to list values from Customers|ByType with the "Include only related values, starting from Jobs" option selected.
You select a customer type in the customer type field and then your value list only lists customers matching that type.
Ok I'm making progress. I've set up the Customer Table occurrence for Customers|ByType and set the relationship
Jobs::_fk_CustomerTypeID = Customers|ByType::fk_CustomerTypeID and created a value list with "Include only related values, starting from Jobs"
Now on my Layout I have a field for Jobs::_fk_CustomerID that looks up customers from the Customer|ByType table. I get only the customers I wanted. Works great. I have a second field called CustomerName that I thought would display the CustomerName associated with the fk_CustomerID when I selected a Customer from the Customer|ByType table but it always displays the Name of the first customer in the table with the associated customer type. I have the customer name field defined as display data from Customer|ByType::CustomerName it is an edit box.
Is CustomerName a number field or a text field? From what you describe, I have to wonder if in Manage | Database | Fields, you might discover that customer name is either a field of type number or a calculation field with number selected as the result type.
Customer name is a text field is was displaying properly when I pulled from the customer table but I was getting all customers then. I'm sure I have done some tiny thing wrong.
The two fields I'm dealing with are Jobs::_fk_CustomerID and Customer|ByType::CustomerName
Customer table has __pk_CustomerID (serial number) and CustomerName (text). and Customer|ByType is an occurrence of that table.
Here's the new relationship diagram. I really don't have a clue why this doesn't work
Here's the value list I'm using…It returns a list of values for the proper customer type. I just can't get the Customer field to change.
A field of type number will still display the name correctly. But it's indexing won't work with this value list. So I'd click over to the Fields tab in Manage | Database and check the field type specified for CustomerName.
The other thing to check would be to temporarily place a portal to Customers|byType on your Jobs layout. If your portal does not correctly list all the records for the selected type, then you need to take a closer look at the values in your _fk_CustomerTypeID field in Customers.
All records show in the portal.
Here's two layouts that show whats happening. the field doing the lookup shows both the customer ID and the Customer Name. The field just showing the Customer Name does not change it always shows the first customer in the value list.
Here's the other showing both fields
I think the problem was in the relationship diagram. I don't know if this is the best solution or not but I added another table occurrence for customer and made a relationship to jobs using customerID I changed the field that wasn't working on the layout to display data from the new customer table occurrence and it now shows the correct data. I don't know if this will come back to bite me later but right now any solution that works is a good one.
Yep, I thought you were indicating that the value list wasn't correctly listing names. The issue is indeed your relationship. But I don't understand why you are selecting any customer id in a field in Jobs. When I take a closer look at your relationships, it seems that you should be selecting a customer ID in the Join table: Join_Customer_Jobs instead of in the Jobs table.
Right now that Join table is empty. I put it there when you suggested that as a way to resolve the many to many relationship. I haven't had a chance to figure out how to generate records into it. I started writing a script to run when the Job is created (by the operator hitting the Create Job/Folder button but have not been able to get the fields populated yet. I have global storage fields created during data entry into the Job layout. The script goes to the join table and sets each field with the global equivalent. A record is created but all fields are blank. I can see the global fields being updated during the data entry but that is as far as I have gotten. I plan to work on that this evening. After I get that working I will revisit this and try to link customer based on the join table like you suggested.
Thanks for all of your help
You have these relationships:
Jobs::__pk_JobID = Join_Customers_Jobs::_fk_JobID
Customers::__pk_CustomerID = Join_Customers_Jobs::_fk_CustomerID
You can place a portal to Join_Customers_Jobs on the Jobs layout to list and select Customers records for each given Jobs record. Fields from Customers can be included in the Portal to show additional info about each selected Customers record and the _fk_CustomerID field can be set up with a value list for selecting Customers records by their ID field.
You may also find this demo field a useful source of ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
If you are using FileMaker 12 or newer, you can use Open from the File menu to open this file and produce a copy converted to the newer file format.
That demo is great and will be very helpful. I added some test data into the join table and can see how much I will be able to do once I get it to automatically populate as Jobs are created.
Again thanks for all of your help…I might just get this DB built this year. I didn't dare say that until after January 1st.