7 Replies Latest reply on Feb 22, 2011 2:52 PM by philmodjunk

    Dynamic Drop Down Lists

    DavidEllison

      Title

      Dynamic Drop Down Lists

      Post

      Hi,

      I have searched various posted and found answer to having dependant drop down lists, but I am not sure where I am going wrong.

      I have two lists, the first selection being "work type", adjusts/limits the second field repair type in the work order table. However, when using value lists from repair type, it does not limit them when selecting only include. I am using the table service matrix for that option. I get no options in the second list at all. I have attached my table setup if it helps.

      Thanks

      Dave

      tables.jpg

        • 1. Re: Dynamic Drop Down Lists
          philmodjunk

          A bit more detail is needed to be able to help you.

           Each "box" in your relationship graph is called a "table occurrence". On the layout where you've attempted to set up your two value lists, what fields did you use and on what table occurrence is the layout based (Show Records From in Layout Setup...)?

          When you defined the conditional value list, from what table occurrence and field (s) are the values drawn?

          Then you specify "Include only related values starting from:" what table occurrence?

          • 2. Re: Dynamic Drop Down Lists
            DavidEllison

            Hi,

            • Two lists, list one is work_orders.work_type_id, second is repair_type_id.
            • The layout is based on the work_orders table.
            • The "include only related" is from service_matrix.

            Sorry if this is not making much sense. I want to use the service matrix to setup the service available for each work order, so a user would select a work type first, then based on what is in the service_matrix the repair types would be available in the next list. Eventually I want the work type screen to select the default contractor and priority :) When I get this right!

            Thanks

            Dave

            • 3. Re: Dynamic Drop Down Lists
              philmodjunk

              Two lists, list one is work_orders.work_type_id, second is repair_type_id.

              So in the value list definitions, the values come from the Work_Orders table occurrence?

              And you select a value in the Work_orders::Work_Type_ID field for the first value list and you want to see all possible repair type ID's from the Work_Orders table for that specific Work Type ID when you pull down the list in the Work_Orders::Repair_Type_ID field?

              I don't see a relationship that will support that. Such a self-join relationship could be created, but I'm not sure that's what you have in mind here.

              • 4. Re: Dynamic Drop Down Lists
                DavidEllison

                Sorry I am mixing my self up here. Thats what you get for typing a message whilst dealing with kids !

                So, my 1st list value will be inserted into workorder.work_type_id, but the values are being pulled from work_type.work_type_description.

                My 2nd list value will inserted into workorder.repair_type_id, but the values are being pulled from repair_type.repair_type_description.

                Then I would like to use the service_matrix table to help filter the repair type list, using the relationships.

                For example, serveice_matrix has work_type_id A and repair_type_id 1, work_type_id A and repair_type_id 2, work_type_id AA and repair_type_id 10.

                So If I select work type A, in the repair type list I would get values 1 and 2. If I select work type AA I would only get the repair type value 10.

                I am using the extra tables to pull in the description and using the ID's for the relationships.

                Cheers

                • 5. Re: Dynamic Drop Down Lists
                  philmodjunk

                  As designed, I don't think that will work.

                  You have this relationship:

                  Work_orders::repair_type_ID = Service_Matrix::Work_Type AND
                  Work_orders::Location_ID = Service_Matrix::Location_ID AND
                  Work_orders::contractor_ID = Service_Matrix::Contractor_ID AND
                  Work_orders::Priority_ID = Service_Matrix::Priority_ID

                  Note that this is the only relational "path" from Work_orders to repair_types, yet you need a valid number in Work_orders::repair_type_ID before you can link the current work-order record to any related records in Service_Matrix. Thus, your value list for Work_Orders::repair_type_id is empty as there are no related records from which you can list values in your value list.

                  If you had this relationship:

                  Work_orders::work_type_id = SelectedRepairTypes::Work_Type_id where SelectedRepairTypes is a new table occurrence of Repair_Types,

                  You could list values from SelectedRepairTypes, "Include only related values starting from Work_orders" to get a list of Repair_Type_Id values that match a specified Work_Type_Id entered in the Work_orders::Work_Type_ID field.

                  I understand what you want to do with Service_Matrix in general terms, but have no idea why you've matched up this specific set of key fields. There should be a way to do what you want here, but you can't include repair_id as a key field from Work_orders to set this up.

                  • 6. Re: Dynamic Drop Down Lists
                    DavidEllison

                    Ok,

                    I'll make some changes. I am essentially trying to emulate a similar setup that is on a oracle database and work on setting it up in Filemaker, for a slim version of what is used in a enterprise enviroment version. The service matrix is to configure what sort of service can be setup for each location. So for instance a location can only have a certain work type and repair types with specific. Loading the service matrix closes any oppurtunity to raise an order on a location and set services, 3rd party contractors that they shouldn't use and decrease time in raising an order.

                    I might be over engineering it, because it works on the Oracle DB. Presuming too much on Filemaker. I may just build from the ground up on Filemaker, using its methods.

                    Does anyone have an example file I could look at for related drop down lists? Seen some examples, but until I become more familiar with Filemaker, examples are easier to work through. :)

                    Thanks for the info!

                    Dave

                    • 7. Re: Dynamic Drop Down Lists
                      philmodjunk

                      As written you have to specify a repair_Type_ID before you can select a repair_type_ID....

                      I doubt that you could get that to work as designed in Oracle. I'm not saying that what you already have doesn't work, just that you can't use a relationship that chases it's own tail in either system to do this. It takes a different relationship than what you have here.

                      If you leave repair_type_ID field out of the relationship to Service_Matrix and make sure that the other fields: Location_ID, Contracter_ID, Priority_ID have valid values before you access the value list, then this should work.