4 Replies Latest reply on Nov 8, 2010 9:48 AM by DennyHayes

    Cool Trick



      Cool Trick


      I was playing with coloring text, and realized that there seems to be no way to determine color with a calc or script. The only thing that you can do is set a color, but you have no way of knowing the present color.

      My problem was that I often have two fields to deal with previously quoted numbers etc. One field would be a calc field named something like "FieldA Dynamic, and other would be number field named FieldA Static. The static fields would be used the rest of the calculations and the static fields only be updated to the dynamic field values when we would activate an update script. But is some situations we do not want to update certain static fields, because we have overridden the number by manually typeing in numbers that do not agree with the dynamic field value. 

      To indicate that a static field has been overridden we turn the number to a different color, such as red, with the ColorText command. Sometimes I may want to update all static fields to the dynamic fields, but sometimes I may want to only update the field numbers that are not red. Normally this would require creating a separate field for each static field to store the color name of the number value.

      But there is a trick when you are dealing with number fields, since number fields will accept letters, but only show the numbers. Because of this, for years, I have been hiding text in number fields. But in this case you can use that feature to store the color. So, if I am using the color red to indicate some condition, and the static number is a calculated dollar amount such as $12.00. When I run the update script it will typically copy the 12 from the dynamic calc field to the static number field, and it will appear in the default color of, say black. At this point both the static field and the dynamic field have the same value, but I may want to click into the static field and alter the number so that it does not match the dynamic field. And when I exit the static field, if the value does not match the dynamic field, an exit trigger script sets the color to red. But you can also have the exist script add the name of the color, such as 12RD. When you have clicked out of the field you will only see the number 12, because number fields will not show the text. You can now use a calc to see the name of the color to use that number in other calcs, scripts, etc.

      There is one slight annoyance though, because you can click back into the field and see the text. But you can easily solve that problem by an entry script trigger to remove it, as you go into the field, and use the exit script trigger to put the letters back in when you exit the field. That way the text is totally hidden, but you know the color. Another thing that you can do, is if you clear out the static field totally, you can have the exit script copy the dynamic field value in black again, so that it is back where we started.

      This is just one use for the situation where number fields can store text that you don't see. There are also many other symbols that you can deposit there for various uses. Years ago I would rename fields with random numbers proceeded by a letter, convert all scripts names to an option/space, and convert numbers to text and store them in number fields just to confuse developers trying to copy my work :) I imagine spending the time breaking the password to a file, only to find the relationships, and calcs were simply random numbers, and the scripts were all named with an option/space. :) I would always have a backup copy for myself through. 

        • 1. Re: Cool Trick

          Although I applaud your creative thinking, storing text in number fields is not good practice.  If this data is pulled by MySQL, it will crash it (MySQL would expect numbers only).  If you wish to find any numbers with text, it is very difficult because FM indexes numbers as one word.  I also would be greatly concerned about the needless script triggers.  Also, the number field will automatically lose the text (in some situations) so there is no guarantee it will stick.

          I would suggest that you instead use conditional formatting, something similar to:

          Field1  ≠ Field2

          ... and select colorizing the text red.  I do not believe in coloring actual data at all ... it is ugly when you print and text is not all black. :^)

          • 2. Re: Cool Trick

            Thanks for the information about MySQL. It is nice to know, but I have no intention of using anything in this database with SQL, and there are many problems with SQL and other databases that are normal practice in FM. So you always need to be careful when dealing with other databases. I have been hiding text in number fields since before FM 3.0, and have never lost anything. I may have even done it when I beta tested for Dan Chadwick of Nashoba Systems who created Filemaker, but there was no scripting back then, so I don't know what I would have used it for? I just can't remember back that far :)  As far as coloring text, FM developers created the feature because users wanted to use it, and I use it all the time to highlight text to be noticed, or paragraph titles etc. To not use it is just plain silly. As far as printing, the printing layout and the data entry layout that uses see, are different layouts, and when colored text is not needed, I simply use a simple calc field to show the text in black. But sometimes we even want red text on printouts like invoices so that they know their payment is late, etc. If you start using too many fields where the default text is a different color, you typically end up with way too many fields, so I try to keep the field count to a minimum. The problem with conditional formating is that it typically involves calcs, and FM is so slow that having too many calcs causes speed problems, especially in list view. So I prefer to use static fields as much as possible.

            • 3. Re: Cool Trick

              Well then we can agree to disagree.  In fact, many things you've said I totally disagree with.  But this one mostly ...

              "The problem with conditional formatting is that it typically involves calcs, and FM is so slow that having too many calcs causes speed problems, especially in list view. So I prefer to use static fields as much as possible."

              • 4. Re: Cool Trick

                Yep, we disagree :)