12 Replies Latest reply on Jan 18, 2017 9:50 PM by slongsdc

    Filtered value list help

    slongsdc

      Hello all,

       

      This is my first query, and obviously one from a relative neophyte, but here goes.

       

      I have created a database to serve as an electronic logbook for the maintenance of high-end publishing equipment. I have a table of all of the machine-specific maintenance procedures which I can successfully display in a proceduresMACHINE TO in portals on filtered slide panels per timing (daily, weekly, monthly, etc.).

       

      What I am having trouble with is the logbook itself. I have two options: one is to populate a portal with procedures filtered by the selection of the machine for the logbook entry. I have this working, but am having trouble passing the selections from this portal into another which will serve as the logbook entry for the date of the maintenance. The other, perhaps more elegant approach that I have partially functioning, is a portal with a pulldown menu of the maintenance procedures. My problem here is the filtering of the procedures: I can create the pulldown menu, and a new portal row is added to the entry, but all of the procedures show. I have tried different join tables and variables, but to no avail so far.

       

      I trust that this isn't too vague. If someone could perhaps point me to a discussion where there is a solution to this or offer a hint, I would appreciate your assistance.

        • 1. Re: Filtered value list help
          erolst

          1. Can we assume that you have tables for

           

          - Machine

          - Log

          - Procedure

           

          and that a log entry is basically a join table entry that combines a machine and a procedure (and adds attributes like date, operator, comment etc?), so the basic structure is

           

          Machine --< Log >-- Procedure

           

          2. On what basis would you want to pre-filter the procedures available for a given piece of equipment?

          • 2. Re: Filtered value list help
            slongsdc

            Yes, I have a table for Machine, Procedure, and Logbook. Procedure and Logbook have foreign keys to MachineID. The procedures are all specific to the different machines, extracted from their individual maintenance manuals. I have a logbook line item as a join between logbook and machine, and want to filter the procedures for that join. Maybe I am overcomplicating this. Let me reassess today (I work in Saudi Arabia--our work day will begin soon).

             

            Thanks in advance for you input.

            • 3. Re: Filtered value list help
              philmodjunk

              I don't see the need for both a log book and  log book line item table.

              • 4. Re: Filtered value list help
                slongsdc

                Per your suggestion I have started working on a stripped down version with a join table like the one erolst has outlined. The portal in the logbook would then be populated with selections from the related procedures table. This still leaves the issue of filtering the procedures for selection in the entry to those that relate to the machine. The drop down list of procedures in the example I am working on still shows all of the procedures.

                • 5. Re: Filtered value list help
                  slongsdc

                  I have uploaded the stripped down version with the tag SteveLongPortalExp so you can see if I'm on the right track. The file's name is PortalExp101817.

                   

                  Thanks for any assistance.

                  • 6. Re: Filtered value list help
                    erolst

                    slongsdc wrote:

                     

                    Yes, I have a table for Machine, Procedure, and Logbook. Procedure and Logbook have foreign keys to MachineID. The procedures are all specific to the different machines, extracted from their individual maintenance manuals.

                    In that case you can simply create a relational/conditional value list by specifying Values from Procedure fields, starting from a Machine TO that is related to Procedure by machineID; eg

                     

                    Procedure_forMachine >-- Machine --< Log >-- Procedure

                     

                    so from the Perspective of Machine, the "left side" provides you with a choice of machine-specific procedures, from which you select for entry into the "right side".

                    • 7. Re: Filtered value list help
                      slongsdc

                      Let me check, because I haven't made this work yet.

                       

                      The table on the left is a TO of Procedures? Linked from the foreign key of Procedures (in this TO) to the primary key of Machine ID?

                       

                      And the value list is comprised of which two fields?

                      • 8. Re: Filtered value list help
                        erolst

                        slongsdc wrote:

                         

                        The table on the left is a TO of Procedures? Linked from the foreign key of Procedures (in this TO) to the primary key of Machine ID?

                        You said that Procedures has a foreign key for Machine, which means

                         

                        Procedure_forMachine::id_machine = Machine::id

                         

                        (bt,w the foreign key is not "in this TO"; a field is a part of the table schema, and thus "belongs" to all TOs of the table.)

                         

                        slongsdc wrote:

                         

                        And the value list is comprised of which two fields?

                        Depends on what you want to enter, but the usual way is to have the primary key as the 1. field (the actual value to be inserted), and a human-readable value (eg Procedure::title) as the 2. field.

                        • 9. Re: Filtered value list help
                          slongsdc

                          Thanks, You are being very helpful, and I am being rather dense. Is there any way I can share my file with you?

                          • 10. Re: Filtered value list help
                            slongsdc

                            Attached is my file. I am heading to bed soon. I really appreciate your

                            help.

                             

                            Regards,

                            Steve

                            • 11. Re: Filtered value list help
                              slongsdc

                              Don't know what I was doing wrong last night, but it works now! Thanks so much!

                              • 12. Re: Filtered value list help
                                slongsdc

                                I forgot to mention that it only works when you check "Include only related values starting from: Machines"

                                 

                                This just in case another beginner stumbles onto this discussion. Thanks again.

                                 

                                Also, thanks for the illuminating reminder that a TO is just a part of the schema, not a separate thing (didn't want to say entity).