5 Replies Latest reply on Sep 30, 2013 12:52 PM by philmodjunk

    Interesting Conditional Value List Question

    jrobgk1

      Title

      Interesting Conditional Value List Question

      Post

           So, I have a table of <Machines>, that have fields Machines::machineName and Machines::MachineType.  I also have a table of <CommonIssues> that have related issues for those various machines, related by CommonIssues::MachineType<-->Machines::MachineType.

           I would like the user interface to select a machine type, be given a list of machines that meet that criteria, and then a list of the issues that are associated with that type of machine.

           So far, I can get a VL of types of machines, and then a CVL off of the type, but I can't get the Names of machines to become a CVL based on the initial selection of Type.

           Have i missed setting up an extra relationship? Or should I do something with the table structure?

           A little confused...
           Jesse

        • 1. Re: Interesting Conditional Value List Question
          philmodjunk

               I'm not totally clear on exactly what you are trying to create on your layout.

               

                    I would like the user interface to select a machine type, be given a list of machines that meet that criteria,

               In addition to producing a conditional value list that could also be a list of records from Machines shown in a portal or a find could be performed that pulls up a list view of all the machines of that type. I'm not sure how/why you'd need a value list of such machines in this scenario. Are you just finding records or are you creating a new record. And if you are creating a new record, is it a new machine or a new issue record?

          • 2. Re: Interesting Conditional Value List Question
            jrobgk1

                 On the layout, the user would select from a list of machine types that he would be working on.  Then, a list of machines that fit the type would be chosen. Then, a common issue relating to that machine type would be chosen. 

                 This info would all be entered into a record in a table called "repair actions" which is related to "machines" which is related to another table "common issues".

            • 3. Re: Interesting Conditional Value List Question
              philmodjunk

                   Thus, you are creating a new record in Repair Actions that is linked both to a specific Machine and a Specific common issue (if one exists).

                   I would not use a Machine name for the needed relationships. Names are subject to change and vulnerable to data entry errors. If you have to change a name for either reason in your database, you may end up losing the link to existing related records as they match by the original name. Use an auto-entered ID such as a serial number instead.

                   This suggests the following relationships:

                   CommonIssues|Repair------<RepairActions>------Machines>-----MachineTypes------<CommonIssues|Type

                   Both CommonIssues|Repair and CommonIssues|Type would be Tutorial: What are Table Occurrences? with the same data source table.

                   The match fields:

                   CommonIssues|Repair::__pkCIssueID = RepairAction::_fkCIssueID
                   Machines::__pkMachineID = RepairActions::_fkMachineID
                   MachineTypes::__pkMachineTypeID = Machines::_fkMachineTypeID
                   MachineTypes::__pkMachineTypeID = CommonIssues|Type::_fkMachineTypeID

                   But for a conditional value list of Machines for a selected type, we'd need to add in another occurrence of Machines and MachineTypes like this:

                   RepairActions-----MachineTypes|VL------<Machines|VL

                   RepairActions::MachineTypeID = MachineTypes|VL::__pkMachineTypeID
                   MachineTypes|VL::__pkMachineTypeID = Machines|VL::_fkMachineTypeID

                   Then your CVL of Machines can be setup on RepairActions::_fkMachineID to list values from Machines|VL, Include only related values starting from Repair Action.

                   Your CVL of CommonIssues would be set up on RepairActions::_fkCIssuesID to list values from CommonIssues|Type, Include only related values starting from RepairActions.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   And You might not need that CVL of machines if you set up a portal to RepairActions on a Machines layout. Then you might perform a find to find a machine and fill out the repair action data directly in a row of the portal.

              • 4. Re: Interesting Conditional Value List Question
                jrobgk1

                     Phil-

                     You added MachineTypes as a table, i am assuming.  Or is it a TO of Machines, self related?

                • 5. Re: Interesting Conditional Value List Question
                  philmodjunk

                       I set it up as a table. If you are going to have a value list of Machine Types, a table of the same would make sense to me, though I suppose that you could manage without it by linking a MachineType field in Machines directly to the TO of common issues.