5 Replies Latest reply on Dec 20, 2012 7:17 AM by tlawson

    Can't make a TO that is a subset of another table

      Title

      Can't make a TO that is a subset of another table

      Post

           I am trying to make a TO and use it to populate a popup menu that gives me a limited/filtered/reduced view of another table (not sure what the right word would be).

           My structure is this:

           * Dealership table (list of car dealers) - primary key is __kp_Dealership_ID

           * Consult table (list of consultation visits to that dealer) - has foreign key pointing to dealership table _kf_Dealership_ID

           * Employee table (list of all employees from all dealerships) - has foreign key pointing to dealership table _kf_Dealership_ID

           Here is what I've tried:

           1. Tried creating a TO using join operator (dealership_consult_EMPLOYEE.).  The relationship uses the _kf_Dealership_ID key in the Consult table and the _kf_Dealership_ID key in the Employees table.

           ------> The popup refers to that TO and I still get a list of all employees, not just the employees for this company

           2. Tried createing a TO using equals operator (dealership_consult_EMPLOYEE).  The relationship uses the _kf_Dealership_ID key in the Consult table and _kf_Dealership_ID key in the Employee table.

           -----> Same result

           I thought I was getting the hang of this.  Stiff not I guess.  

        • 1. Re: Can't make a TO that is a subset of another table
          philmodjunk

               TO's only refer to an entire table, A relationship, evaluated from the point of view of a related table, produces a subset of related records.

               So if you have a portal to Table B on a layout based on Table A, the relationship linking the TO of Table A for your layout to the TO of Table B specified for your portal determines what records appear in the portal (assuming that it is unflitered).

               A conditional value list, what you are describing here also relies on a relationship where you specify "include only related values" and select a "starting from" table occurrence. The layout's TO is most often the "starting from TO" and the TO we would have used to set up a portal to list the same records is usually the TO you select from the "field" drop downs near the top of the Specify Fields for Value LIst dialog box.

               Here are some links on conditional value lists that may be helpful:

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Can't make a TO that is a subset of another table

                 I kinda have it working but I'm missing something.  If I use both values and sort on value 1, it works.  If I tell it to sort by field I get the "ID" plus an "?" (for example: "4 ?".  If I tell it to only show value 2, I get just a question mark.  I've attached the one that I want to work that shows just a "?".  Nothing is different from the one that works other than I'm telling it to JUST show value 2 and NOT sort by value 1.

                 I've included the relationship diagram and the 3 different attempt at a conditional value list (only of which one works).  I'm wondering if it has something to do with the fact that "Employee" is attached to "Dealership" and not directly to "Consult".  The drop down menu is in the "Consult" layout. I think that's the way I need it to look since employees can be added to a dealership even before creating any consults.

                 I'm still working my way through the examples/docs you showed.  They have been EXTREMELY helpful.  Thanks.

            • 3. Re: Can't make a TO that is a subset of another table

                   Sorry, here is the relationship chart that I'm using.  I also tried changing the value in "Include only related values starting from: " to "Dealership" and it didn't make any difference.

              • 4. Re: Can't make a TO that is a subset of another table

                     I think I figured it out, but I'm not sure why.  I'm using a computed value as the 2nd value.  The field is called FullName and concatinates FirstName and LastName.  If I change the 2nd Value to just be FirstName or LastName, I get rid of the question mark.  Is there a way to reference FullName?

                • 5. Re: Can't make a TO that is a subset of another table

                       OK, now I really DID figure it out.  FullName is a calculation that was evaluating to a Number and not Text.  Changed that and it worked GREAT!  Thanks again for all the help.

                       PhilModJunk,

                       I don't know if you ever sleep but you are incredibly helpful and fast.  Thanks!