9 Replies Latest reply on Sep 9, 2015 7:51 PM by bigtom

    restricting data displaying on a layout



      first up, I am new to FM, and still have my head in Access 2000 space, and I am assuming I have just really overlooked something basic, apologies in advance


      scenario, I set up a layout to add details of litters to the database,  (litters table and the main table of the database is for individuals with sire & dam etc table)


      that's working, then I thought it has to be easier to add the sire and dam then using the standard ID - name basic value list, and found the superb

      Tutorial: How to use an auto-complete drop down list when ...

      which does work, except I have two things driving me crazy, attached are (I hope) all the screen shots you'll need, but in words

      obviously because of the way I have set this up I have two 'issues'

      1. if I type in a female's name in the sire/father box, the database will accept it
      2. when I do a short word search and get taken to the selection layout it becomes obvious why, while the genders of the opposite sex don't show their names (so don't show up in the value list) they are there without their names showing because I haven't had the calculation field store their names, and I am drawing from the calculation field   If ( IsEmpty ( Gender ) or Gender = Upper ( "F" ) ;Name )


      I have trawled through this forum (and others) & found lots on conditional value lists etc, but nothing that gels with me at this stage


      thanks for pondering on my problem


        • 1. Re: restricting data displaying on a layout

          You mention value list problems; and you show some miscellaneous detail.

          But nowhere do you show the value list setup.

          It would be helpful to do that.

          • 2. Re: restricting data displaying on a layout

            thanks Bruce, I had a thought during the night that  I hadn't included a screenshot of the value list set up, here it is and some otehr screenshots, on the sire dropdown







            thanks so much for requesting the extra info


            • 3. Re: restricting data displaying on a layout

              Uniqueness: there can be only one cat in the universe named Fluffy? And if there is a male "Fluffy" there can never be a female "Fluffy"?

              Naming convention confusion:

              ConstantMale can be "Fluffy" but constantBOYS is "M"?

              • 4. Re: restricting data displaying on a layout

                Maybe this will give you some ideas.



                • 5. Re: restricting data displaying on a layout

                  my aim is to have the uniqueID as the unique feature in the table, and of course the sires & dams will be in number fields (soonish!) as ID numbers too. (This is a new transfer, eg I have the imported text Date of Birth and the 'corrected' FM date field type Date of Birth (that was an easy upgrade, thank you FM), sires and dams were exported as named dogs not IDs)


                  The data as it is currently stored is pulled from a proprietary pedigree database which I gather used the name field as the unique field, so I have stuck to a unique ID and a unique name, the unique name MAY go, it may not. If you are intrigued by never having two Fluffys, you should see how often one name was used in the early days of registration, my 'fix' in the days when I did my own database on dBase was to have in brackets after the dog's name the breeder's surname, I recall even resorting to using approximate year of births in some cases, there were a lot of Dashes and Fops! (even Dots!)


                  Why have I left the proprietary database? It is too restrictive in its relational structure and formatting reports is murder, and it doesn't offer me the analysis I want, so back to my own brew


                  sincere thanks for your input :-)


                  • 6. Re: restricting data displaying on a layout

                    Hi Jan,


                    If I understand your explanation correctly, you are trying to achieve two separate value lists of sires and dams, with the males appearing only on the sires list and the females appearing only on the dams list?


                    That being the case two approaches you could consider are:


                    i) create two relationships to the table where data on sires and dams is located based on Gender, one for males and one for females, then create two value lists that use the "include only related values" option, one for each gender. Then attach the list of males to the sires field and the list of females to the dams field.


                    ii) alternatively, you could create stored calculation fields for the names of males and females, and create two "include all values" value lists (one based on each of the calculation fields) and, as above use them for the the sires and dams fields respectively.


                    In either case, that will enable you to access value lists of the appropriate gendered Sires and Dams.


                    As regards your desire to prevent other names (not on the appropriate value list) from being entered, perhaps the simplest approach would be to apply field validation to the Sires and Dams fields with the "Member of value list" option enabled (and the corresponding value lists specified) for each.


                    There are a few other approaches you could consider to address both the issues you've indicated, but perhaps try the suggestions I've outlined above and see whether that provides an adequate solution.




                    • 7. Re: restricting data displaying on a layout



                      an update: with hindsight it is obvious, the issue of the females appearing in the male list although without their names, is because of the way I generated the found set,

                      • the found set is called from the script
                      • which activates when data is placed in the search box
                      • this script is from Tutorial: How to use an auto-complete drop... . Using FileMaker Pro . Forum . FileMaker Forums
                      • so I overcome MY issue (needing to restrict to a subset of data) by adding the script line, before I perform the find
                      • SET FIELD [table::constGIRLS; "F"]
                      • constGIRLS is a calculated field on the underlying table which places F in the field if the gender is missing or female;

                      and of course adapted for males for the sires dropdown


                      this doesn't seem to alleviate the issue of being able to add a valid (in the table) female name to the sire, but I have great hopes that Ray Cologan's pointing out to me the validation option for the field "Member of value list" will resolve that issue at least to my satisfaction

                      THANKS EVERYONE

                      and a special thanks to Bruce Robertson for his sample, it inspired some thoughts for other areas within my project


                      • 8. Re: restricting data displaying on a layout

                        You really seem to be bent on doing things the hard way, There is no need for the ConstGIRLS or constBOYS field. You have a Gender field.

                        • 9. Re: restricting data displaying on a layout

                          Depending on the size of the list and how it is used this couldn't all be in one table run on ExecuteSQL. Just the data you need and a UUID for each record and Fields to store the UUID of the Sire and Dam of each pup. you would need another table for specific litter data that is not directly associated with an animal. Such a number of pups in a litter or delivery date/time. These could also be figured out via SQL from the big table, but things like litter ID, location or doctor might be better off in its own table anyway.


                          I recall that ExecuteSQL can manage the auto complete, but I am not for sure. FM14 makes this easier right? Use ExecuteSQL to generate a value list and use that value list for the Auto complete set on the field.


                          Given the nature of breeding animals sometimes this may be a better way to do it. You may need to think of how you want to view search for litter data, but usually you always have some sort of information to start with that makes it possible.


                          At some point with the relationships you end up doubling your data or relating things to themselves when a pup from a litter is later a sire. More confusing in the relationship model is if a boy pup has a litter with his own mother.