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?
- 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!
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.
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.
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.
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!
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.