Trying to enhance calendar report record displays using formulas

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Acknowledged
I am using a formula field to display record data in a calendar report because I want the records to display in different colors based on a specific record status. However, the way I am doing this I lose the ability to have the records sorted the way that I want them to sort, In my case by start time.

For example, my calendar is used for scheduling time on an MRI machine. My formula originally included a field for Time In, Time Out, Name of the person who is scheduling time on the MRI and the type of MRI scan. Using the formula field alone as the field to display on the calendar, the records display in the order of record ID. However, I want them to display in the order of Time In to Time out, sort of like a normal Outlook or google calendar. I removed the Time In field from the formula, and included Time In as the first field in the display, followed by the formula field. Now my formula looks like this:

If([MR Physicist Approve]="yes" and [Physician Approve]="yes","<div style=\"color:green\"><img src=\"https://images.quickbase.com/si/16/234-rhomb_green.png\">"&[Time Out]&"|"&[PI Name]&"|"&[Image Type]&"</div>",
If([Metal_Human_Yes-No]="Yes" and [Metal_Appvd]="no","<div style=\"color:red\"><img src=\"https://images.quickbase.com/si/16/233-rhomb_red.png\">"&[Time Out]&" | "&[PI Name]&" | "&[Image Type]&"</div>",
"<div style=\"color:black\"><img src=\"https://www.quickbase.com/up/bcgnn27bx/g/rzk/eh/va/16blackdiamond.png\">"&[Time Out]&"|"&[PI Name]&"|"&[Image Type]&"</div>"))

With the Time In as the first data element in the calendar display, it does sort the records by Time In. However, because Time In is not part of the formula, it is always in Black font, with the rest of the fields in Red or Green.  It works, but doesn't look great.

I was wondering if anyone had tinkered with this and if there is a solution?

Thanks!
Photo of Byron Deane

Byron Deane

  • 170 Points 100 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of Byron Deane

Byron Deane

  • 170 Points 100 badge 2x thumb
To clarify what I was asking for...has anyone tried, or found a way to use a formula to sort records in a calendar report based on a specific field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
I’m using the new calendar report which is available on request now via support and will be rolled out, I think, in February to all users. It is the new type which has a Day, week and monthly view like Outlook and other calendar programs use.

It seems to sort fine and I also am using a formula like yours to concatenate and color fields.

The monthly view seems to sort by the field I am using to base the calendar on, which is a date/time field which is a formula of the date and time of the meeting combined into a Date/time formula field.

I’m assuming that you are on the old ( current) calendar and not the advance access new calendar. So I suggest that you get in the new calendar and stop messing the nuance of the color issue on the old calendar , because in a month for two you will likely lose access to the inferior calendar anyways.
Photo of Byron Deane

Byron Deane

  • 170 Points 100 badge 2x thumb

You are correct, I am still on the old version. I will contact support to see if there is a test environment that I can use before we turn on new calendar in our realm.  Actually, I do have a test realm we are using for SAML integration testing, might be able to use that.  Thanks for the suggestion.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
You will find that they implement the new version by Realm and also in "waves", so they won't just do it for you right away, but rather add you to the next wave.  I think that they are wanting to be sure there are no bugs before rolling out to all users.