5 Replies Latest reply on Sep 9, 2016 6:13 AM by PeterDoern

    Scripting problem

    ucharles

      I have a field called "class" which uses the case function given age and sex of a livestock to determine its category: calf, heifer, cow, bull and so on. With time as the animal is growing it will be changing its category status; say from calf -> heifer -> cow etc.

       

      This works Ok and I am happy with it. What I want is for this field to trigger change in the drop down list based on value list  based on a Category table that has same values that a result of the class calculation. In other words if  class = "cow", then script trigger must category drop down list to select "cow" automatically.

        • 1. Re: Scripting problem
          dtcgnet

          When the class becomes "cow", you would like the category ID to change to represent "cow" also? Can you post a verison of the database?

          • 2. Re: Scripting problem
            ucharles

            When the class becomes "cow", I would like the _fk_category _ID to change to represent "cow" also, yes!

            • 3. Re: Scripting problem
              PeterDoern

              You've got an error in your "class" field definition:

               

              Case (

                   age > 1 ≤ 345 and sex = "Female" ; "Calf-Female" ;

                   age > 1 ≤ 345 and sex = "Male" and Castration = "Yes" ; "Calf-Male" ;

                   age > 1 ≤ 345 and sex = "Male" and Castration = "No" ; "Calf-Male" ;

                  

                   age < 720 and sex = "Female" ; "Heifer" ;

                   age < 720 and sex = "Male" and Castration = "Yes"; "Steer/Tollie" ;

                   age < 720 and sex = "Male" and Castration = "No"; "Young Bull" ;

                  

                   age ≥ 720 and sex = "Female" ; "Cow" ;

                   age ≥ 720 and sex = "Male" and Castration = "Yes" ; "Ox" ;

                   age ≥ 720 and sex = "Male" and Castration = "No"  ; "Bull" ;

              )

               

              should be:

               

              Case (

                   age ≤ 345 and sex = "Female" ; "Calf-Female" ;

                   age ≤ 345 and sex = "Male" and Castration = "Yes" ; "Calf-Male" ;

                   age ≤ 345 and sex = "Male" and Castration = "No" ; "Calf-Male" ;

                  

                   age < 720 and sex = "Female" ; "Heifer" ;

                   age < 720 and sex = "Male" and Castration = "Yes"; "Steer/Tollie" ;

                   age < 720 and sex = "Male" and Castration = "No"; "Young Bull" ;

                  

                   age ≥ 720 and sex = "Female" ; "Cow" ;

                   age ≥ 720 and sex = "Male" and Castration = "Yes" ; "Ox" ;

                   age ≥ 720 and sex = "Male" and Castration = "No"  ; "Bull" ;

              )

               

              but even having said that, I've suspected from the get-go that really you should remove this class calculation from the Livestock table and make it part of the Category table. What would happen if governing bodies changed the definition of a steer somehow, or added a new category?

               

              In other words, add sex, age and castration fields to Category. For your adult animals, make age an impossibly high number like 999999. Then form a relationship from Livestock to Category something along the lines of:

               

              Livestock ∞ livestock_CATEGORY_byAgeSexCastration

              age <= age

              sex = sex

              castration = castration

               

              Then:

              Livestock::class = livestock_CATEGORY_byAgeSexCastration::category_name

               

              You'll have to add auto-enter option to castration:

              Case ( sex = "female" ; "n/a" ; IsEmpty  ( Self ) ; "no" ; sex = "male" and Self = "n/a" ; "no" ; Self )

              to accommodate the "n/a" value which will be required to resolve the relationship.

               

              Finally, change _fk_Category_ID to be an auto-enter calc (replace field contents, always):

              livestock_CATEGORY_byAgeSexCastration::_pk_category_id

               

              although I'm not sure you need this field anymore if you have a "soft" link between livestock and category as above.

               

              I hope this helps.

               

               

              Peter

              • 4. Re: Scripting problem
                ucharles

                Peter, you seem to be endowed with special skills to understand the blind intents of the novice like me! It is just working how I want it to. I need to to scrutinize it and understand how it works. Hope you won't mind if I come later with a question or two? Thanks.

                 

                Regards

                1 of 1 people found this helpful
                • 5. Re: Scripting problem
                  PeterDoern

                  well, shucks.

                   

                  No, I don't mind further questions at all -- that's what this forum is all about, after all.

                   

                  In the meantime, all the best figuring it out.