Trying to enhance calendar report record displays using formulas
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!
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!
