Is this what you want using the actual field names you have in your database?
You select Customer ID to see a list of tool records.
You select a tool and see data for both Tool Items and Tool Procedures Appear?
Does your layout refer to Work Orders in Layout setup | "Show records from"?
If so, you need this relationship:
Work_Orders::_Fk_customer_ID = ToolsByCust::_pk_Customer_ID
ToolsByCust is a new occurrence of tools, not your existing tools occurrence--which needs to be kept as you have it.
Your value list should list _PK_Tool_ID from ToolsByCust as field 1 and a name field from ToolsByCust as field 2.
Specify the Include only related values starting from Work Orders option.
Now add the Tools::Tool Procedure field to your layout.
When you select a customer in _fk_Customer_ID, the value list of tools will be limited to that customer. When you select a tool, the ::Tool Procedure field will display that data from the same related record.
I think this will work I have have to get it a shot!
I gave it a shot and still didnt work maybe I did something wrong.
I took a screen shot of everything and a description of what I am trying to do.
On the workorder layout (top left) it has a pop up menu where the customers are, it pulls this info from the tool layout bottom right hand corner.
I have the relationship correct (I think) and and the value list to the top left.
It all looks correctly set up.
Is the _FK_TOOL_ID field the field that is set up with this conditional value list as shown in your screen shot?
Where/how does this fail to work for you?
Yes _FK_Tool_ID is where the tools show up.
When I click on _FK_TOOL_ID on the workorders list it says <no values defined>.
Had you first selected a customer in the Work_Orders::_FK_Customer_ID field?
Then you need to examine the values of the match fields used in these relationships.
When you go to a layout based on tools, do you see values in the _PK_Tool_ID field?
Are there values in the two _FK_Customer_ID fields (Check layouts for both Work Orders and Tools)?
If there are values in these fields, do the values match?
I got it to kinda work but not really lol
When I select company and then go to pick item it shows only the first item for that company but not the other items.
item_ID 1 - burger
item_ID 2 - fries
item_ID 3 - shake
when you select "Joes" and then the Item it will only show Burger on the list. Not fries and shake as well.
Its doing that for all the companies i have, each have about 4 or 5 items right now and they only show
the first record.
In your example, the second field lists text. Is it by any chance a calculation field? If so, make sure that "text" is the specified return type for the calculation. Otherwise, if you are sorting on the second field in the value list setup, you'll only see one entry.
OK so I got the it to work half way.
I got it so when you select Customer it filters the tools by customer.
But now the procedure wont come up.
I have tool procedure as an edit box to just pull in info from the tools.
But I changed it to a pop up menu and then set it to only show related info from customer table and it has the procedures of all the tools only related by the customer.
But I need it to show automatically, not by a pop up or anything.
Here is the relationship.
The tool procedure realtionship to it self is just trouble shooting.
Customers are where the customer names are, then at tools you select tool as well as customer and set the procedure. Then the second occurance of the tools is where it is pulled into Workorders, and the combined info is shown. (Customers, tools, procedure)
Did you put the Tool Procedure field from Tools on your work order layout?
I took away the relationship between the tool tables.
I related the Workorders, tool procedure field to show data from toolsbycust tool procedures.
It shows only the first value even when I change the tool.
Joes > Steak > and it shows abc
Joes > Chicken > and it shows abc
not the procedures I set
Put the relationship back. You need it to display the related record's tool procedure.