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

stephenmadison · Answer

Thank you so much but with these actions will they be sequential. Meaning if someone edits and enters 3 dates will it pick the one closest to the top (formulaically speaking) to set the status as?

To explain: If someone goes in and changes:

Date 1
Date 2
Date 3

Will the status show as Date 3 instead of the other 2?

chayceduncan2 · Answer

That would be part of the 'filter' conditions in your trigger to make sure that when you're moving through these they move in the sequence you want. So in the case of your example, lets say someone enters all 3 dates - the logical sequence is that you don't want it to go to status 1 when it should be status 3 because that date is also filled out.

So in your trigger for status 1 let's say - you would add a condition that Date 2 and 3 must also be empty.

For status 2 - Date 3 must be empty.

This way - if someone edits all 3 dates - It will skip over changing the status to 1 and 2 because the condition of date 3 being empty isn't met.

Think of the logic similar to how you would a formula with a little more detail -

if Date 1 is filled in and date 2 / 3 are empty - let it be status 1
if Date 2 is filled in and date 3 is empty - let it be status 3
If date 3 is filled in, make it 3

Does that provide detail for what you're asking?

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

stephenmadison · Answer

I see this as working moving forward but what about dates that have been previously entered, that won't have a trigger of an add or modify, the automation wouldn't&nbsp;incorporate those.&nbsp; I would need to see what that status is on what we currently have in our database.

Forum Discussion

Change multiple choice field by date entered in another field

Related Content

Using Grid Edit to Edit Multiple Records at Once

Moving Fields from One Table to Another

Edit Multiple Records at Once with Grid Edit

Relating Multiple Records from another table

Copy Single Table from One App to Another