10 Replies Latest reply on Jun 14, 2011 10:02 AM by jeffd

    Relation Help

    jeffd

      Title

      Relation Help

      Post

      I need help in making a relationship work.

      In my table Quoter I have a fields called Equipment Make, Equipment Model, Equipment Description and Equipment Type.

       In my table Machine I have fields called Make, Model, Description and Type. This table holds all of the machine's information that I use in Quoter.

      In this Machine table I have many makes, each Make have many Models and each Model may have more than one Description. Also, but only each Model will be a certain Type.

      I want to have Drop Down Value list in the Quoter table to do the following; For Make, show all makes( I can do this); for Model, show only models related to the Make selected (I think I can do this); for Description; show only descriptions related to the model selected; then for Type, show only the Type related to the model selected.

      Thanks & Help, Please.

        • 1. Re: Relation Help
          philmodjunk

          You are describing a series of cascading conditional value lists. Are you able to set up the first conditional value list? (Show Makes for specified model.) Make sure that you can do that and report back. With that as a starting point we can likely walk you through the rest of this in pretty straight forward fashion.

          If you can't, then we need to start at a bit more basic level before tackling the additional value lists.

          • 2. Re: Relation Help
            jeffd

            Yes, I do have the first conditional value list working.

            "Make List" uses all values from the field Make from the Machine table. Relation is setup as follows; Quoter::Equipment Make = Machine::Make

            • 3. Re: Relation Help
              philmodjunk

              Great! that saves me some writing and you some reading. Wink

              Let's now extend that to the next conditional value list and that will probably show you how to extend the "cascsde" from there:

              Open Manage | database | Relationships, select Make and click the button with two green plus signs to create a second table occurrence of Make. We'll rename this occurrence MachinesByModel. Add it to your relationships to get:

              Quoter::Equipment Model = MachinesbyModel::Model

              Now your value list can list values from MachinesByModel, include only related values starting from Quoter.

              • 4. Re: Relation Help
                jeffd

                Ok, I hope when you said to "select make" you were saying to click on the Machine table occurence to make a duplicate occurence of Machine table, that's what I did & now ready for the next. If this incorrect please let me know.

                • 5. Re: Relation Help
                  philmodjunk

                  My typo, I indeed meant to select the Machine Table Occurrence in order to make a duplicate of it.

                  • 6. Re: Relation Help
                    jeffd

                    Ready for the next step when you get a sec.

                    • 7. Re: Relation Help
                      philmodjunk

                      The next step is to repeat the same process with the next value list:

                      1. Create a new occurrence of the Machines table and add a relationship to Quoter.
                      2. Define the Value list based on this new table occurrence.
                      3. Format your field with the value list.

                       

                      See how the steps are the same?

                      • 8. Re: Relation Help
                        jeffd

                        I'm not sure if I follow this because in the second relation for the model you said to set up the relation Quoter::Model = Machine::Model.

                        If I get this right the first relation is Quoter::Make = Machine::Make and the value list for make is based off this relation that shows all Makes.        Then relation #2 is Quoter::Model = Machine::Model with the Model value list based off this relation? When I select a make then go to Model field there is nothing there for me to select.

                        • 9. Re: Relation Help
                          philmodjunk

                          Please disregard my last post. I should have read through the thread more carefully to refresh my memory before posting.

                          I thought you had the first conditional value list, the list of Models for a specified Make working.

                          Check your value list setup for your Models value list. It should list values from Machine. The Include only Related Values Starting From... option should be selected with Quoter selected as the "Starting From" table occurrence.

                          Then, For the conditional value list of Descriptions, you use fields from MachinesByModel, Include only Related Values starting from Quoter.

                          • 10. Re: Relation Help
                            jeffd

                            Mr Caulkins, Your last post made me realize exactly what I had been doing incorrectly. I now have the realtions & value list working perfectly. Thanks again. I'm sure I'll bug you again down the road.