Different Formula for Each Record in a Base

[Tom_H]

I’m not sure if this can be done but here’s what I’m looking for:
I have a base with two basic tables: Jobs and List

The Job Table has the following fields Job Name, Job Number, Custom Calculated Field (CCF) 1 Formula (CCF 1 Formula), CCF 2 Formula…CCF 6 Formula, and a link to the List Table

The List Table has the following fields List Name, Customer Inputed Field 1 (CIF 1), CIF 2…CIF 8, a link to the Job Table (allowing only a single link), then there are Lookup Fields for CCF 1 Formula…CCF 6 Formula.

Everything up to that point is pretty basic, but what I’m trying to do is create 6 fields CCF 1…CCF 6 that will actually perform the operations/functions shown in CCF 1 Formula…CCF 6 Formula. Here’s a link to the base.
Link to Base

I can get the CCF 1…CCF 6 fields to repeat the text of the formulas, but I actually want them to perform the operations within the text…can this be done?

Thanks in advance for any guidance or assistance you can provide!
Tom

[Justin_Barrett]

Welcome to the community, @Tom_H! :grinning_face_with_big_eyes:

Tom_H:

I can get the CCF 1…CCF 6 fields to repeat the text of the formulas, but I actually want them to perform the operations within the text…can this be done?

No. In many programming languages, what you want would be accomplished with a command/function often named eval (short for “evaluate”). You feed it a string that represents the code you want to run, and it runs it. Unfortunately Airtable doesn’t support this kind of behavior currently (though it’s a cool idea, and one that you might consider adding to the Product Suggestions category).

The end result you seek can be done through other means, though. What I would suggest is to ditch the [Job] table, and change the {Job} field in [List] into a single select. From there, each formula field would do its own switching between the appropriate mini-formulas via the SWITCH() function. Here’s how {CCF 1 Formula} would look with this modification:

SWITCH(
    Job,
    "Job 1", Trim(Upper({CIF 1})),
    "Job 2", Upper(Concatenate({CIF 1}, " ", {CIF 2})),
    "Job 3", Trim(Upper({CIF 1}))
)

Do the same for the other formula fields with their respective formula combinations pulled from the [Job] table.

While this would be fairly manageable for a setup where there are only going to be a few job possibilities, it would become a bit unwieldy for larger datasets.

That aside, I’m curious about the data you’re working with. What source gives you the field contents with such disparate information? {CIF 2}, for example, contains a last name in some records, a full name in others, and a date reference in still others. If there’s any way of cleaning up the data before it comes into Airtable, I suggest doing that, rather than trying to make all the options work on the back end via formulas.

[W_Vann_Hall]

I think @Justin_Barrett is probably on the right track; using the SWITCH() function that way would allow some fancy customization that could seem much cooler than it actually is under the hood. :winking_face: Slightly orthogonal to your request, I’ve built query generators that allow one to specify complex searches in a special [Query Generator] table and have the results displayed in an [Output] table. I keep threatening to strip out all the client proprietary data and post a sanitized demo base, but I’ve yet to do so.

I also once suggested this — which would be greatly improved by adopting the SWITCH() function, which was introduced after I posted. Maybe there’s something in it you can use; I’ve certainly not been able to. :winking_face:

[Tom_H]

Hi @Justin_Barrett and @W_Vann_Hall,

Thank you so much for answering the question! The sample base that I sent didn’t portray the scope of my actual intended base where there are close to 150 Job records. We manage a plastic card personalization program and the disparate data sets are a result of us integrating with our clients existing data structure.

Your guidance of using the SWITCH () function was exactly what I needed, (although having an EVAL () function would be much less involved). I kept the List Table and the Job Table and added a new table that I called Formula Concatenate which is linked to the Job table and has only one record, “.” (thanks @W_Vann_Hall for your post about Concatenation of a Column). Now every time that I add a new Job to the Job table I’ll just copy the formula CCF 1 Formula…CCF 6 Formula from the Formula Concatenate Table and paste it into the formula field for CCF 1…CCF 6 in the List Table and my intended purpose is met. We get about 5-10 new jobs per month so it’s a manageable solution. If you know of an easy way to avoid having to do the copy and paste of the formula I’m all ears. Here’s the updated Personalization Sample base.

Thanks Again!

Tom

[Justin_Barrett]

Tom_H:

If you know of an easy way to avoid having to do the copy and paste of the formula I’m all ears.

I’m afraid not. Current API tools (including things like Zapier and Integromat) don’t have access to the innards of formula fields, so it’s gonna be a manual process until that changes (hint: make a suggestion in the Product Suggestions category and it might be considered).

[Tom_H]

Hi @Justin_Barrett,

Thanks for the response. I’ve posted a product suggestion requesting the addition of the Eval() function. For now, manual copy and paste it is!

Thanks again!

Tom