13 Replies Latest reply on Jan 2, 2014 1:56 PM by john.s

    Limiting values in a value list



      Limiting values in a value list


           I have a customer table with four customer types; 1=person, 2=corporation, 3=school, 4=league

           when I create a new job I want to look up only certain types of customers…for example if I create a portrait I want a value list that only contains customers with a value of 1 (person).  I've created three a calculated number fields in the customer table to attempt to do a self join field _sj_person value = 1,   _sj_corporation value = 2,  _sj_league value = 4  (schools are not a problem because I have a detail record for each school that I use for all school jobs)  I then created 3 table occurrences for the customer table and joined each using the _sj filed as the primary key to the customer type key in the customer table.  I then created a value list based on the joined table.  I either get all the customer records or nothing depending what I've tried.  I think I have over complicated this and now have a mess. I've tried every type of value list I can think of…this should be simple and probably is.

           The tables in gray are "real tables" and colored tables are occurrences.

           All help is greatly appreciated.


        • 1. Re: Limiting values in a value list

               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.

          • 2. Re: Limiting values in a value list

                 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.


            • 3. Re: Limiting values in a value list

                   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.

              • 4. Re: Limiting values in a value list

                     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

                • 5. Re: Limiting values in a value list

                       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.

                  • 6. Re: Limiting values in a value list

                         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.

                    • 7. Re: Limiting values in a value list

                           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.

                      • 8. Re: Limiting values in a value list

                             Here's the other showing both fields

                        • 9. Re: Limiting values in a value list

                               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.

                          • 10. Re: Limiting values in a value list

                                 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.

                            • 11. Re: Limiting values in a value list

                                   Thanks Phil,

                                   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


                              • 12. Re: Limiting values in a value list

                                     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.

                                • 13. Re: Limiting values in a value list

                                       Thanks Phil,

                                       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.