Manual Override of Lookup Field Data


#1

I think it would be great if in single records, you could manually override a Lookup field’s data. It would still take the field type of the parent it’s looking up (dates would still be dates, etc) but you could make simple exceptions to blanket lookup categories.

As an example:

I work for an organization that teaches classes in jails. Jails segregate the people incarcerated by gender. I have a tab for classes with columns for (among other things) class location, graduation date, and gender, I also have tab for students with columns for (among a lot of other things) which class they’re in, and what their gender is.

I’d like the gender column in the students tab to be a Lookup field that uses the gender for the class by default. But I would also like to be able to override that to account for times when the self-identified gender of the student doesn’t match what gender jail they’re incarcerated in (they don’t have jails for nonbinary folks). This only affects a small number of students, so it would be handy not to have to enter all students’ genders manually to account for this.

Another use would be for graduation dates. We have rare instances where someone has to graduate early or late compared to the rest of the class. My class tab has a graduation date field, and the participants’ tab has a Lookup field for that date that references the graduation date in the class’s tab, which we then use to calculate other things (formulas using that and their birthday to find their age at graduation for example). It would be handy to be able to override that date in specific circumstances.


#2

Typically, if I need to perform such an override, I’ll configure two additional fields: An override field, where I can enter an adjusted value, and an evaluation field, which ‘decides’ whether to use the lookup or the override in calculations and display. For instance, using your gender example:

{Gender} = looked up value
{Identifies as} = override field
{Adj Gender} = formula configured as IF({Identifies as},{Identifies as},{Gender})

{Adj Gender} is what I’d use in Page Designer or any other such display intended to convey information to a user of the application.

Admittedly, not as elegant as an override capability — but far less likely to introduce unintended results down the line…