11 Replies Latest reply on Jan 28, 2013 7:26 PM by bzoo2005

    Cascading drop-lists, check boxes and exporting

    bzoo2005

      I'm new to Filemaker Pro (I am a somewhat experienced Access user) and am having some issues with basic db development, and am hoping for some guidance at the start of this database, instead of having to redo it a bunch of times due to silly mistakes...so here goes.

       

      I am working on a database that will record a zookeepers (I work at a zoo) location and actions while working with assigned animals. So far, the tables I think I need are....Keepers (to have a list of keeper names, their info and activate/deactivate keepers), Animals (for the same reason), locations, and work performed. And finally a table to actually store each record...call it "events". I may also need a table to keep track of which keepers work with which animals...say "assignments"...but I'll definately take advice as to the overall structure of this thing...

       

      My droplists question:

      In Access, I was able to make "cascading drop lists" so that if you selected "Dan" you would only have a choice of animals from those that Dan is assigned to do. This was done by making a query that would pull up the info from said table, based on what was entered into the form...and populate the pulldown list from there. Is that done through the Inspector--when I select the source of the information, or is it best done via a portal? Can this be done at all?

       

      My Check Boxes question:

      I also need this to have these records track each animal's access to certain "pens" or locations. There would be "work performed" called a "shift" that would indicate a change in animals' locations. I have several issues with how to best perform this...I would LIKE to have a series of checkboxes that are on the layout and indicate each animal's last known locations. This is greyed out and locked until someone says they are shifting. At this point, the checkboxes become active, and you can move them around (click that they are in enclosure 1 instead of enclosure 2, etc.)...saving this record would update the animals' location in a location's file, as well as update the regular event. Saving it without performing a shift would save to just the event table. I hope that makes sense...

       

      And my exporting question:

      Checkboxes allow for multiple entries into one field or line on a table. So if I select that two animals...Say "Ben" and "Jerry" were fed carrots, and do a quick search, I can search for times that Ben was fed, or Jerry was fed, or when Ben and Jerry were fed, and get that record for all responses...correct? Is there a good way to separate these values upon export? These tables (specifically the "Events" table) are meant to be exported to another data analysis program via Excel, and every export I get when I play around with this has info like this:

       

      Keeper Animal Event

      Dan Ben, Jerry Fed

       

      I need:

       

      Keeper Animal Event

      Dan Ben Fed

      Dan Jerry Fed.

       

      Is this impossible, or something simple I'm missing here?

       

      Thanks in advance for any help/advice

        • 1. Re: Cascading drop-lists, check boxes and exporting
          LyndsayHowarth

          bzoo2005 wrote:

           

           

          My droplists question:

          In Access, I was able to make "cascading drop lists" so that if you selected "Dan" you would only have a choice of animals from those that Dan is assigned to do.<snip>  Can this be done at all?

          Yes... you can create valuelists which only display values which are related to the value first chosen. You must base the valuelist on a field... then choose in the dialog 'Include only related values starting from'

           

           

          bzoo2005 wrote:

           

           

          My Check Boxes question:

          I also need this to have these records track each animal's access to certain "pens" or locations.  There would be "work performed" called a "shift" that would indicate a change in animals' locations.  I have several issues with how to best perform this...I would LIKE to have a series of checkboxes that are on the layout and indicate each animal's last known locations.  This is greyed out and locked until someone says they are shifting.  At this point, the checkboxes become active, and you can move them around (click that they are in enclosure 1 instead of enclosure 2, etc.)...saving this record would update the animals' location in a location's file, as well as update the regular event.  Saving it without performing a shift would save to just the event table.  I hope that makes sense...

           

          This would probably be better done with a location table which logs the movments and keeps a record. You can then sort by timestamp to always show the most recent.

           

          If you still want something like you have described... I would use radio buttons rather than checkboxes as the radio button can only have one value whereas checkboxes allow you to have more.

          To restrict when it is editable you would have an on-field-enter script attached to the field which checks the 'Shifting' field for the value that means 'shifting'.

           

           

          And my exporting question:

          Checkboxes allow for multiple entries into one field or line on a table.  So if I select that two animals...Say "Ben" and "Jerry" were fed carrots, and do a quick search, I can search for times that Ben was fed, or Jerry was fed, or when Ben and Jerry were fed, and get that record for all responses...correct?  Is there a good way to separate these values upon export?  These tables (specifically the "Events" table) are meant to be exported to another data analysis program via Excel, and every export I get when I play around with this has info like this:

           

          Keeper                     Animal                   Event

          Dan                        Ben, Jerry                 Fed

           

          I need:

           

          Keeper                     Animal                    Event

          Dan                            Ben                       Fed

          Dan                            Jerry                      Fed.

           

          Is this impossible, or something simple I'm missing here?

           

          Thanks in advance for any help/advice

          You need to export the data from a join table which shows data from Keeper Animal & Event as single records.

          Using checkboxes for export won't do what you need. It stores an ascii 27 ot 28 (ie a soft return or line break) between the values and I am assuming given the results you show with the comma separation that now in v 12 these characters are being substituted by FMP on export.

           

          HTH

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Cascading drop-lists, check boxes and exporting
            bzoo2005

            Lyndsay,

            Thanks!  I think I'm getting the pulldown list issue!

             

            For the checkboxes, I get what you are saying, and am thankful for the understanding they might not be usable with my export needs.  So...

            The issue is, each animal can have access to many locations at the same time, and we DO need to know when they do.  I like the portal pulling info up from another table...and can see that working, and how to do it.  So thanks!  The issue is how to best structure my fields so that the db integrity is still solid, while giving me the flexibility to get the info I need.

             

            Is a setup like:

             

            Keeper                                 Animal (pulldown)                     Near (other animals...checkbox)                      Primary Loc (pulldown)                    Aux. loc  (pulldown)    

            Dan                                         Ben                                               Jerry                                                               Viewing                                         Holding

             

            I would have the locations table populated with two fields....and the events table populated with a Proximity field.

            That make sense?

            • 3. Re: Cascading drop-lists, check boxes and exporting
              comment

              bzoo2005 wrote:

               

              And my exporting question:

              Checkboxes allow for multiple entries into one field or line on a table.  So if I select that two animals...Say "Ben" and "Jerry" were fed carrots, and do a quick search, I can search for times that Ben was fed, or Jerry was fed, or when Ben and Jerry were fed, and get that record for all responses...correct?  Is there a good way to separate these values upon export?  These tables (specifically the "Events" table) are meant to be exported to another data analysis program via Excel, and every export I get when I play around with this has info like this:

               

              Keeper                     Animal                   Event

              Dan                        Ben, Jerry                 Fed

               

              I need:

               

              Keeper                     Animal                    Event

              Dan                            Ben                       Fed

              Dan                            Jerry                      Fed.

               

              Is this impossible, or something simple I'm missing here?

               

              As a rule, you can only export what you have. An exception to this rule is exporting as XML using a custom XSLT stylesheet to process the result. You can instruct the stylesheet to output each multi-valued event as a number of separate single-valued records. However, this would require at least an intermediate knowledge of XSLT.

               

              Another option is to do something similar within Filemaker: just before exporting, have your script process the events and split them off into separate records in a temp table - then export from there.

               

              Finally, you could also force your users to enter the data in a different way - for example, use a portal to list the animals involved in a specific feeding, instead of a checkbox.

              • 4. Re: Cascading drop-lists, check boxes and exporting
                comment

                Lyndsay Howarth wrote:

                 

                Using checkboxes for export won't do what you need. It stores an ascii 27 ot 28 (ie a soft return or line break) between the values

                 

                AFAIK, the values in checkbox field are separated by a carriage return (char 13).

                • 5. Re: Cascading drop-lists, check boxes and exporting
                  beverly

                  The field STORES char(13) = carriage return as a separator for valueLists. The EXPORT of that field to anything but XML will probably convert the return to VT (not HT) or char(11). The separator between repeating field sections is ASCII 27.

                   

                  So, the method of export may be the factor here.

                   

                  Beverly

                  • 6. Re: Cascading drop-lists, check boxes and exporting
                    bzoo2005

                    Michael,

                    Are you suggesting a code that runs on one table, searching each record in a set field for a carriage return.  If it doesn't find one, if makes a new record in that "export" table for that record.  Then searches the next record.  When it does find one, it makes a record in the new table with the other field values, plus only the text before the carriage return, then the next portion, then repeat...etc?

                    Then export THAT table?

                    Sounds doable...I might opt to start with the separate location fields (no checkboxes) until I can work that out...

                    • 7. Re: Cascading drop-lists, check boxes and exporting
                      comment

                      bzoo2005 wrote:

                       

                      Are you suggesting a code that runs on one table, searching each record in a set field for a carriage return.  If it doesn't find one, if makes a new record in that "export" table for that record.  Then searches the next record.  When it does find one, it makes a record in the new table with the other field values, plus only the text before the carriage return, then the next portion, then repeat...etc?

                      Then export THAT table?

                       

                      I believe the actual technique could be a bit more elegant, but the essence is correct: each record in the "real" table would cause n records to be created in the "export" table - with n being the number of checked values in the checkbox field.

                       

                       

                       

                      bzoo2005 wrote:

                       

                      I might opt to start with the separate location fields (no checkboxes) until I can work that out...

                       

                      No, that would be a step in the wrong direction. A checkbox field, even a repeating field, while not ideal are still better than that.

                      • 8. Re: Cascading drop-lists, check boxes and exporting
                        comment

                        Beverly Voth wrote:

                         

                        The EXPORT of that field to anything but XML will probably convert the return to VT

                         

                        Yes, that is a good point.

                        • 9. Re: Cascading drop-lists, check boxes and exporting
                          bzoo2005

                          Ok...so, in the interim, I'll have them make single entries (radio buttons or pulldowns)...for the critical data separation, and work on improving it from there. 

                           

                          Thank you guys for helping me get my head around this.  I'll post a new question if I need more detail on either point. 

                          You have both been amazingly helpful!

                          • 10. Re: Cascading drop-lists, check boxes and exporting
                            LyndsayHowarth

                            Hi bzoo2005,

                             

                            It would be good to know your real name...

                             

                            I do not answer questions in private messages unless you are planning to pay ;-)

                            I prefer to share publcally so others can benefit from the discussion.

                             

                            I have attached a file which is just a starting point to demo one way of looking at this. (have a look at the valuelists on the Events layout.)

                            I haven't had time to make sense of the specifics in your message but I hope this helps.

                             

                            - Lyndsay

                            • 11. Re: Cascading drop-lists, check boxes and exporting
                              bzoo2005

                              What, you don't believe my mom liked putting letters next to the word "zoo" and followed it up with a number?

                              My name is Dan Powell...

                              I'm a lead keeper at Brookfield Zoo in Chicago, Illinois.

                               

                              Sorry for the breach of ettiquet.   Didn't occur to me that I'd be limiting other's learning...I have a tendancy to think of this as an inconvenience for everyone...funny. 

                              I'll go over the file and see what I can learn.

                              On a side note, I did get the relationship to work...had some stuff wrong...and almost have it working the way I want it to.  Just not quite.  I'll see if this cracks it for me, and post again if I don't get it still.

                               

                              Thanks again for your help.