Forum Discussion

AndrewFry's avatar
AndrewFry
Qrew Assistant Captain
6 years ago

Color code calendar events based on divisor of Record ID #

I have generated a calendar report and can color code the events on the calendar.

Currently I color code them based upon who the tech assigned to the event is. It is hard coded based on tech name.

However, I would like to somewhat automate this so that it is not based upon a text string (i.e. Tech's name) but based upon the Tech's record id #.

I need to do it based upon the tech id being wholly divisible.

The number of techs I have will vary, Soni don't want to continually be manually changing text strings to correctly color code a calendar event.

I was thinking of something like
if the tech id is wholly divisible by
5 then green
4 then blue
3 then yellow
2 then purple
1 then orange

I just can't figure out how to do the wholly divisible part ...

Any suggestions, ideas, etc??

I was thinking of using CASE, but wasn't sure on how to correctly implement the requirement of the tech id being wholly divisible.

I hope that makes sense.

-Andrew
App builders
Formulas and functions
reports and charts

4 Replies

  • TomMusto's avatar
    TomMusto
    Qrew Cadet
    6 years ago
    The Mod function will return the remainder of x/y, so when Mod(x,y) = 0 then x is evenly divisible by y. If you combine If and Mod, you could get the coloring down. Here's an example based on the exact situation presented in your post:
    If(Mod( [Related Tech], 5 ) = 0, "green", Mod( [Related Tech], 4 ) = 0, "blue", Mod( [Related Tech], 3 ) = 0,"yellow", Mod( [Related Tech], 2 ) = 0, "purple", "orange")​

    Hope this helps.

    -Tom

    ------------------------------
    Tom M
    ------------------------------
    • AndrewFry's avatar
      AndrewFry
      Qrew Assistant Captain
      6 years ago
      Hey there Tom!! Sorry for the late response.

      I kept checking my discussion for responses but then realized that they were frozen due to the update/upgrade to the community site.

      I then forgot about my post.

      During all that I actually found a solution very similar to yours. I researched the MOD function as well as the REM function.

      I opted to go with the REM function.

      Here is the code I used:

      If(Rem([Tech ID#],5)=0,"#F39C12",Rem([Tech ID#],4)=0,"#5499C7",Rem([Tech ID#],3)=0,"AF7AC5",Rem([Tech ID#],2)=0,"48C9B0",Rem([Tech ID#],1)=0,"DC7633")

      Is there any difference between what you proposed and what I went with? Does one have any advantage over the other? Is one more "proper" given the the use?

      Thanks for your feed back and again, sorry it took me a while to get back to you. I appreciate your input!

      ------------------------------
      Andrew Fry
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        Chiming into this post
        Your code is working so that is fine.  The Mod functions and the REM functions will return the same values for positive numbers  and your Tech ID#s are will be positive numbers.

        If you wanted just for the fun of it to write the formula more elegantly, then it would be

        Case(Rem([Tech ID#],5),
        0,"#F39C12",
        4,"#5499C7",
        3),"AF7AC5",
        2,"48C9B0",
        1,"DC7633")

        The colors may end up different from your formulas as to who gets which color, but it will have everyone with a different color.



        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        markshnier2@gmail.com
        ------------------------------