13 Replies Latest reply on Apr 30, 2014 12:06 PM by raybaudi

    call out color by name and use it to format a field

    martinc

      I'm using SoSimple Calendar. It seems to work pretty well for a larger volume it calendar items. It generates html to display an interactive calendar.

       

      Being html, we can color code items with just using text in a Color field. Gray, lightBlue, Red, and so on. We have a dropdown list for employees that we can use to set a color for them that shows up on the Cal. It's pretty cool.

       

      For the Non-html side of our database, is there a way to use those colors to color a field by simply using the name...Say Bob color field is set the Blue and Toms to Red. I'd like users to see a blue bar on all of Bobs work orders and a Red bar on Toms. I can see a massive If/Then statement in the conditional formatting- would also be a major pain.

       

      Thanks

        • 1. Re: call out color by name and use it to format a field
          Mike_Mitchell

          One way to do it would be to store the names (IDs?) and color codes in a table, then use that in your Conditional Formatting calculation. For example:

           

               TextColor ( { fieldName } ; RGB ( UserColors::Red ; UserColors::Green ; UserColors::Blue ))

           

          where there's a join between a User ID and the UserColors table.

          • 2. Re: call out color by name and use it to format a field
            danielfarnan

            Another way is to define a bunch of Custom Functions that return the RGB values of the colour name. For example, you would have a custom function named Gray that would be defined as RGB ( 80, 80, 80 ).

            • 3. Re: call out color by name and use it to format a field
              erolst

              Mike_Mitchell wrote:

              color codes in a table, then use that in your Conditional Formatting calculation

               

              danielfarnan wrote:

              Another way is to define a bunch of Custom Functions that return the RGB values of the colour name

               

              Am I missing something? The only calculation you can use in CF is the one to determine if to apply the formatting; the actual 'formatting result' needs to be selected as a hardcoded setting and cannot be calculated. This means one needs to add a string of conditions to check for the name and set the respective formatting for each condition.

               

              Being able to calculate the CF formatting result is one of the features I hope for in FM14 …

              • 4. Re: call out color by name and use it to format a field
                Mike_Mitchell

                erolst is correct. Meant to say “calculation”, not “Conditional Formatting calculation”. You can do this using the calculation I posted, but you have to do it as part of the schema.

                • 5. Re: call out color by name and use it to format a field
                  steve_ssh

                  Hello Martin & Friends,

                   

                  Rarely too shy to propose a hack, I'll mention that:

                   

                  If you just need a single bar color-coded on the layout, you might be able to avoid a massive Case statement by creating a bar of text built out of the Unicode char for a black box.

                   

                  I haven't tested this in Windows, but on OSX this works to some degree.

                   

                  Your mileage may vary based on the actual dimensions that you need the bar to be.

                   

                  I'll attach an archive with files for the examples that I played with:

                   

                  Version 1:

                   

                     Uses a repeating field to hold the bar color.  Does not require v.13.

                   

                   

                  Version 2:

                   

                     Uses a merge variable to hold the bar color, and updates the merge variable using the "Hide When" calc.  Requires v13 and does not work properly in List view.

                   

                  Both of the above use Mike's suggestion of defining a Colors table.

                   

                   

                  Comments:

                   

                  I'll be the first to say that this is a kludge.  My apologies to anyone whose stomach turns should it seriously go against the grain of someone's better development sensibilities.

                   

                   

                   

                  Update Comment Added A Short While Later:

                   

                  In retrospect, using a repeating field in the Version 1 example now strikes me as unnecessary;  I believe one could get the job done just as well with a non-repeating field that calculates its value similar to how the merge variable value is calcuated in the second example.

                   

                   

                   

                  p.s. Out of curiousity:

                   

                  What do folks say instead of "your mileage may vary" in places where kilometers are the preferred unit of long-distance measurement?

                   

                   

                  Best to all,

                   

                  -steve

                   

                   

                   

                  Message was edited by: steve_ssh:

                   

                     Added update comment re: repeating field, and updated archive of samples to incorporate small improvement to Let calc, and to change repeating field to non-repeating.

                  • 6. Re: call out color by name and use it to format a field
                    beverly

                    Lol. People who travel by km certainly have variable (different) mileage.

                     

                    I've used your method. I've also used a space character (resize the bounding box). Use any 'text' which can be manipulated as if a box and by use of ConForm (CF can mean so many things!) and/or text color calculation.

                     

                    The color/colour choice in ConForm text or background, of course is not changeable by calculation. But one supposes that if the set is not huge, there can be multiple conditions set on the same object. Blue if Harry, green if George, etc.

                     

                    I also have many ways to play with color in FMP that I've been using for years before ConForm or text formatting calcs.

                     

                    One way might be a container field in the contacts table. Put a small png of appropriate color in the container (one per person). Then use it for a background field behind any related contacts field which can be transparent (showing all of the background) or pts wider & taller all than the filled field so that you appear to have a "border" of the differing related colors. Remember to allow the container contents to expand the solid png!

                     

                    -- sent from my iPhone4 --

                    Beverly Voth

                    --

                    • 7. Re: call out color by name and use it to format a field
                      martinc

                      I figured it out...I know I said I know nothing about html, this is true but..in a web viewer this works very nicely

                       

                      "data:text/html,

                      <html>

                      <head>

                      <body bgcolor=" & EMPLOYEES::CalColor & ">

                      <body>

                      </body>

                      </html>"

                      • 8. Re: call out color by name and use it to format a field
                        beverly

                        If this is your exact calculation, I suggest the following changes:

                         

                        • you seem to have TWO beginning body tags.

                        • you seem to be missing the closing head tag

                        • use CSS not bgcolor

                        • add a non-breaking space so that there is something (just 'in case'...)

                        • here's what I'd put:

                         

                        LIST ( "data:text/html,"

                        ; ""
                        ; " "
                        )

                        or even this is sufficient, as you can use only a snippet of html:

                        LIST ( "data:text/html,"
                        ; "

                        "
                        ; " "
                        ; "

                        "

                        ) // note you may need to add width and height for div

                         

                        Web viewer doesn't like "layering" with other objects, so be careful of using this for interface element.

                         

                        Beverly

                        • 9. Re: call out color by name and use it to format a field
                          martinc

                          Thank you Beverly.  I'll try some of that cleanup.

                           

                          The screen shot is essentially what I was after.  The color gets applied to records related to the employee (active jobs).  This could be used for anything that needs a visual cue where users need to be able to change applied colors via a drop-down

                          If anyone wants, here is our list of web colors that sit in EMPLOYEE: CalColor as a drop down.

                           

                          AliceBlue

                          AntigueWhite

                          Aquamarine

                          Azure

                          Beige

                          Black

                          Blue

                          BlueViolet

                          Brown

                          BurlyWood

                          CadetBlue

                          Chocolate

                          Coral

                          CornFlowerBlue

                          Cyan

                          DarkBlue

                          DarkCyan

                          DarkGray

                          DarkGreen

                          DarkKhaki

                          DarkOrange

                          DarkSalmon

                          DarkSeaGreen

                          DeepSkyBlue

                          ForestGreen

                          Gold

                          GoldenRod

                          Green

                          IndianRed

                          Khaki

                          LightBlue

                          LightSeaGreen

                          LightSlateGray

                          LightSteelBlue

                          MediumSeaGreen

                          OliveDrab

                          Orange

                          Orchid

                          Peru

                          RosyBrown

                          SlateGray

                          Tan

                          Teal

                          SandyBrown

                          YellowGreen

                           

                           

                          ee List.jpg

                          • 10. Re: call out color by name and use it to format a field
                            raybaudi

                            The web viewer borders can be eliminated.

                            • 11. Re: call out color by name and use it to format a field
                              beverly

                              you said

                              The screen shot is essentially what I was after.  The color gets applied to records related to the employee (active jobs).  This could be used for anything that needs a visual cue where users need to be able to change applied colors via a drop-down

                               

                              the "gets applied" is what I was wondering. I see the single WV for each record and this works well. If you try to use this with other objects, you cannot "layer", you must use the WV as a single object wherever you use it ( and this may be fine for your neeeds ). Using the PNG in a container ( on of my methods, above ), however, can be used as a background or "border" for any related object.

                               

                              HTH,

                              • 12. Re: call out color by name and use it to format a field
                                martinc

                                Gets Applied is the wrong term.  should have said something like Sets the web viewer to a specific color.  Apparently, one can add lots of calculated info to the web viewer.  Here is a video on the subject:

                                https://www.youtube.com/watch?v=3rm8T-FKFCQ

                                 

                                Daniele, how can the web viewer border be eliminated?

                                 

                                Thx

                                • 13. Re: call out color by name and use it to format a field
                                  raybaudi

                                  martinc ha scritto:

                                   

                                  Daniele, how can the web viewer border be eliminated?

                                   

                                  Thx

                                  Try to put this code into a Web Viewer with border line set to "none"

                                   

                                  "data:text/html,

                                  <body style='border=0;margin=0;overflow:hidden'>

                                  this is a Web Viewer w/o embossed border

                                  </body>"