Formula outputs in different field

[Keith_Hadayia]

I was wondering if there was a way to have “if” formulas display in a different field? We work in private corporate elections & in access we have use a checkbox that automatically populates corresponding fields, but when the checkbox is un-checked, we manually enter in the numbers (of votes for the election)

One idea we had was to make an if formula field that says if the box is checked show the numbers in a numeric field type so we can still manually enter them.

[W_Vann_Hall]

You may already be doing this, as I’m not quite following your example base, but the solution is to implement the function you describe — only bassackwardly.

You can’t directly manipulate the value of a field from a formula defined within a different field, so you can’t create a formula that contains a checkbox and says, “if this box is checked, set {Field A} equal to {Field B}.” However, what you can do is define {Field A} to be a formula field configured something along these lines:

IF(
    {CheckBox 1}=1,
    {Field B},
    {Override}
    )

What that says, essentially, is if the field {CheckBox 1} is checked — that is, is equal to 1 — the set the value of the current field ({Field A}) to equal that of {Field B}. Otherwise, set {Field A} to the value entered in {Override}. ({Override} is a data entry field allowing manual override of a preconfigured value under special circumstances.)

In fact, if you don’t need the extra manual step of ticking the box as a safeguard against unintentional data change, you can eliminate the checkbox field altogether by configuring {Field A}'s formula to read

IF(
    {Override},
    {Override},
    {Field B}
    )

Now, whenever data has been entered into {Override}, {Field A} will assume this is an intentional correction and accept it over any preconfigured values.

[Keith_Hadayia]

Thanks so much for the reply, I will give this a shot. The example is a bit half-assed. What would happen in actual job is several field would populate if the box was checked, but this example only shows one. But we now that we know that there is a such thing is {override}, that’s a great starting point

Thanks again & LOL at Bass-ackwards!!

[Keith_Hadayia]

Just out of curiosity, is there a way to make an formula if formula that says if(checkbox =0), then the field type is numeric? Wasn’t sure how feasible it is to change the field type based on an if statement, or if dynamic field types even exist, but that might better ask the initial question

[W_Vann_Hall]

Um, no, twofold.

First, you’re still trying to modify {Field A} based on the value of {Field B}. An Airtable formula can change the value only of the field in which the formula is configured.

Even if that wasn’t the case, though, Airtable doesn’t allow shape-shifting field types. Instead, a calculated field’s type is determined by kind of a ‘lowest common denominator’ process: If any of the calculation’s possible outcomes is text, then all results will be expressed as text. Similarly, if the field’s possible outcomes are incompatible types, the field’s value will be expressed as a textual representation of that value.

You can test this by defining four fields: {Field A}, {Field B}, {CheckBox}, and {Calc}.

• Fields A and B are data entry fields you can set to various data types.
• {CheckBox} is, unsurprisingly, a checkbox field.
• {Calc} is a formula field with the following configuration:
  IF({CheckBox},{Field A},{Field B})

Now, configure {Field A} to be a number field and {Field B} a single-line text field and enter a representative value in each. Now, as you tick and untick the checkbox in {CheckBox}, you’ll see the value of {Calc} switch between those of {Field A} and {Field B}. However, you’ll also notice the {Field A} value is presented flush-left, indicating it is a textual representation of the value.

A quick run through some paired data types reveals this:

Field A	Field B	calc
text	number	text
date	number	text
number	duration	number
number	currency	number
checkbox	duration	text

[Keith_Hadayia]

Thanks again, I’ve learned a lot this afternoon researching these topics.

[W_Vann_Hall]

Keith_Hadayia:

Thanks again, I’ve learned a lot this afternoon researching these topics.

You and me, both. :winking_face:

[Michael_Fischer]

Hi,

I’m having a similar problem. I am using a Kanban To-Do list and would like to move overdue items automatically to the “Overdue” stack. The only problem is that I can’t make the priority field a formula and use it in Kanban at the same time.

Is there a way to do this?