Override column formula for specific cell

Suggestion Box
1

I would like to override the column formula for specific cells.

Example, If the description for a product had a column formula like
concatenate(make, " ", model)
and I had a couple of cells that did not fit this pattern, I would like to be able to manually override the description for those cells to something else.

I love the product.

1 Like
2

You can add something like a Switch statement to the formula to check for the conditions you need in order to use the correct logic. For example…

/edit - the SwitchIf statement is also very useful for this kind of thing. You can’t manually type into a column that has a formula.

1 Like
3

I’d like to resurface this request with a proposal.

EXAMPLE USE CASE
I have a date column that in most cases can be derived from other data. However sometimes it needs to be manually overridden because the wold is complex :grinning:

Workaround 1: Add an Override Column
Add a “Date override” column where people can specify manual dates. Then the date column is

if(
  dateOverride.isNotBlank(),
  dateOverride,
  [the auto-calculated formula I was using for the column previously]
)

Does this work? Yep!
Is it an absolutely confounding UI for my users? Yep.

Workaround 2: Build a Reactivity System with Automations
I have not tried this because I don’t think the delay inherent in automations would be acceptable in my situation, but I imagine you could:

  • Move the date column formula to “value for new rows” so it only runs upon creation
  • Keep track of whether data has been manually or automatically entered via a hidden boolean column
  • Have automations watching for changes in any of the data that used to feed into your date calculation formula
  • When that data changes, update your date column if the most recent update to the cell was NOT a manual one
  • When a cell is cleared (user intends to remove a manual override), run an automation with the date calculation formula

I haven’t yet worked out detecting manual vs automation input but it seems doable. But the biggest drawbacks here are complexity of the rig and automation delay.

PROPOSAL 1: BASIC OVERRIDES (PRIMARY RECOMMENDATION)

  • Add a switch in the column options allowing manual overrides to the formula
  • When a user edits a cell in such a column, accept their input
  • Indicate which cells have been manually overridden vs calculated via formula (perhaps similar UI to when input doesn’t match column type?)

PROPOSAL 2: ACCESS TO MANUAL INPUT IN FORMULA

This is more complex but could open up interesting use cases for data validation/normalization in addition to manual overrides. Basically, you would expose any manually-entered input as a manualInput variable accessible in column formulae. This pattern is common in animation tools, e.g. Expressions in Adobe After Effects.

To implement manual override, you would do:

IF(
  manualInput.isNotBlank(),
  manualInput,
  [your regular column formula]
)

But you could also use it to manipulate input. I admit I can’t think of a great use case at the moment, but as a toy example you could ensure input begins with a dollar sign:

IF(
  manualInput.startsWith('$'),
  manualInput,
  Concatenate('$', manualInput)
)

Drawbacks here would be that it’s harder for the doc maker to implement the basic “allow overrides” functionality, and we probably lose UI highlighting of manually-overridden cells.

CHALLENGES

  • I recognize these proposals require a change to the data model, though I’m hoping it wouldn’t be too destructive (as I understand it, cells already carry ancillary data such as conditional formatting, and this could be handled similarly).
  • I’m not quite sure how to handle the user wanting to specify an override value of blank (in my proposals, blank is how the user specifies “fall back to the formula”)
1 Like
4

I ran into a need for an override while creating receipts for club dues. The dues are supposed to be $18 (for example) but sometimes members pay $20 and don’t want the change. A lookup table automatically populates the right amount so when they pay the wrong amount, the receipt is wrong. I would prefer it default to the correct amount but allow an override if desired. For now, I have to continue to populate the various amounts for dues and have a separate column to type in the actual amount collected which is usually the same so unnecessary extra work. An override would make more sense in this case.

5

hi @Club_Admin ,

Did you consider using a button with ModifyRows() to print the value in the table?
with a button (that can be pushed when you create a new row via an automation) you can bring in the value of the lookup and in case you got a number you want to correct, you can because it is a text field, the formula lives in the button, not in the column where it prints the outcome.
Cheers, Christiaan