Change multiple choice field by date entered in another field

Question

I would like to change a multiple choice field using the dates of another field. I know we can do this in one formula field which I have done in other applications. However, I need it to be a multiple choice field because I will be creating a kanban report from that field. Is there a way i can use a formula text field to manipulate the fields.

Something like this:

If(
not IsNull([1st Patient Enrolled]),[Milestone Status]=1st Patient Enrolled,
not IsNull([Activation Date]),[Milestone Status]="Activation Date",
not IsNull([Initial IRB/CIRB/SWOG Approval]),[Milestone Status]="Initial IRB/CIRB/SWOG Approval",

I know there are form rules that change fields based on other fields but it is inconsistent and i need it to be in a specific order. Once again i know this can be done with a one formula text field but that field cannot be used with a kanban report. Any help you can provide will be greatly appreciated.

ChayceDuncan2 · Answer

Your best bet would be to set up a series of Quick Base automations that map your formula logic to reset the MC choice field for your Kanban.

Form rules, as their name implies only work on forms, so they're a bit limited. Automations will work with any change that occurs on the record. So you could set it up that when certain conditions are met and its time for the actual status to move with your workflow, fire off an the change and have it happen in the background so your MC field gets in sync

So in your first example - you would have an automation set up that:
when a record is added/modifed -
and [1st patient enrolled] is not blank,
and current Milestone status = "Not Started" or something (This is important so that you don't accidentally reset your status once you've move on

then add an action to set the status to "1st patient enrolled"

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

Sign up

Login to the community