Is it possible to condense this huge and clunky formula in any way?

momentsgoneby80 · ‎Mar 22, 2021

Hi!
I have a base for fanfic that imports data and chapters via Airtables Webclipper. Some fanfics have 100+ chapters, so my base is set up to accomodate up to 125 chapters, one per field.

After a fanfic is imported I want to tagg it for the characters in it that have not yet been tagged and disregard the ones that are mentioned in passing, but aren’t “physically present” in the fic.
As is the code to find A (as in one) character looks like this:

IF(
	FIND("A1",{🚫 Disregard characters (mentioned, but not present)}),
	'',
		IF(
		AND(
			FIND("A1", {🗃 Characters to Log}),
			FIND("A1", {Characters})
		),
		'',
		IF(
			FIND("A2", {🗃 Characters to Log}),	"A1, ",
			IF(FIND("A2",{🗃 Chapter 00 - Oneshot}),IF(FIND("A1", {Characters}),'','A1 [00]\n'))&''&	
			IF(FIND("A2",{🗃 Chapter 01}),IF(FIND("A1", {Characters}),'','A1 [01]\n'))&''&	IF(FIND("A2",{🗃 Chapter 02}),IF(FIND("A1", {Characters}),'','A1 [02]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 03}),IF(FIND("A1", {Characters}),'','A1 [03]\n'))&''&	IF(FIND("A2",{🗃 Chapter 04}),IF(FIND("A1", {Characters}),'','A1 [04]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 05}),IF(FIND("A1", {Characters}),'','A1 [05]\n'))&''&	IF(FIND("A2",{🗃 Chapter 06}),IF(FIND("A1", {Characters}),'','A1 [06]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 07}),IF(FIND("A1", {Characters}),'','A1 [07]\n'))&''&	IF(FIND("A2",{🗃 Chapter 08}),IF(FIND("A1", {Characters}),'','A1 [08]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 09}),IF(FIND("A1", {Characters}),'','A1 [09]\n'))&''&	IF(FIND("A2",{🗃 Chapter 10}),IF(FIND("A1", {Characters}),'','A1 [10]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 11}),IF(FIND("A1", {Characters}),'','A1 [11]\n'))&''&	IF(FIND("A2",{🗃 Chapter 12}),IF(FIND("A1", {Characters}),'','A1 [12]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 13}),IF(FIND("A1", {Characters}),'','A1 [13]\n'))&''&	IF(FIND("A2",{🗃 Chapter 14}),IF(FIND("A1", {Characters}),'','A1 [14]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 15}),IF(FIND("A1", {Characters}),'','A1 [15]\n'))&''&	IF(FIND("A2",{🗃 Chapter 16}),IF(FIND("A1", {Characters}),'','A1 [16]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 17}),IF(FIND("A1", {Characters}),'','A1 [17]\n'))&''&	IF(FIND("A2",{🗃 Chapter 18}),IF(FIND("A1", {Characters}),'','A1 [18]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 19}),IF(FIND("A1", {Characters}),'','A1 [19]\n'))&''&	IF(FIND("A2",{🗃 Chapter 20}),IF(FIND("A1", {Characters}),'','A1 [20]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 21}),IF(FIND("A1", {Characters}),'','A1 [21]\n'))&''&	IF(FIND("A2",{🗃 Chapter 22}),IF(FIND("A1", {Characters}),'','A1 [22]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 23}),IF(FIND("A1", {Characters}),'','A1 [23]\n'))&''&	IF(FIND("A2",{🗃 Chapter 24}),IF(FIND("A1", {Characters}),'','A1 [24]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 25}),IF(FIND("A1", {Characters}),'','A1 [25]\n'))&''&	IF(FIND("A2",{🗃 Chapter 26}),IF(FIND("A1", {Characters}),'','A1 [26]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 27}),IF(FIND("A1", {Characters}),'','A1 [27]\n'))&''&	IF(FIND("A2",{🗃 Chapter 28}),IF(FIND("A1", {Characters}),'','A1 [28]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 29}),IF(FIND("A1", {Characters}),'','A1 [29]\n'))&''&	IF(FIND("A2",{🗃 Chapter 30}),IF(FIND("A1", {Characters}),'','A1 [30]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 31}),IF(FIND("A1", {Characters}),'','A1 [31]\n'))&''&	IF(FIND("A2",{🗃 Chapter 32}),IF(FIND("A1", {Characters}),'','A1 [32]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 33}),IF(FIND("A1", {Characters}),'','A1 [33]\n'))&''&	IF(FIND("A2",{🗃 Chapter 34}),IF(FIND("A1", {Characters}),'','A1 [34]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 35}),IF(FIND("A1", {Characters}),'','A1 [35]\n'))&''&	IF(FIND("A2",{🗃 Chapter 36}),IF(FIND("A1", {Characters}),'','A1 [36]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 37}),IF(FIND("A1", {Characters}),'','A1 [37]\n'))&''&	IF(FIND("A2",{🗃 Chapter 38}),IF(FIND("A1", {Characters}),'','A1 [38]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 39}),IF(FIND("A1", {Characters}),'','A1 [39]\n'))&''&	IF(FIND("A2",{🗃 Chapter 40}),IF(FIND("A1", {Characters}),'','A1 [40]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 41}),IF(FIND("A1", {Characters}),'','A1 [41]\n'))&''&	IF(FIND("A2",{🗃 Chapter 42}),IF(FIND("A1", {Characters}),'','A1 [42]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 43}),IF(FIND("A1", {Characters}),'','A1 [43]\n'))&''&	IF(FIND("A2",{🗃 Chapter 44}),IF(FIND("A1", {Characters}),'','A1 [44]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 45}),IF(FIND("A1", {Characters}),'','A1 [45]\n'))&''&	IF(FIND("A2",{🗃 Chapter 46}),IF(FIND("A1", {Characters}),'','A1 [46]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 47}),IF(FIND("A1", {Characters}),'','A1 [47]\n'))&''&	IF(FIND("A2",{🗃 Chapter 48}),IF(FIND("A1", {Characters}),'','A1 [48]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 49}),IF(FIND("A1", {Characters}),'','A1 [49]\n'))&''&	IF(FIND("A2",{🗃 Chapter 50}),IF(FIND("A1", {Characters}),'','A1 [50]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 51}),IF(FIND("A1", {Characters}),'','A1 [51]\n'))&''&	IF(FIND("A2",{🗃 Chapter 52}),IF(FIND("A1", {Characters}),'','A1 [52]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 53}),IF(FIND("A1", {Characters}),'','A1 [53]\n'))&''&	IF(FIND("A2",{🗃 Chapter 54}),IF(FIND("A1", {Characters}),'','A1 [54]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 55}),IF(FIND("A1", {Characters}),'','A1 [55]\n'))&''&	IF(FIND("A2",{🗃 Chapter 56}),IF(FIND("A1", {Characters}),'','A1 [56]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 57}),IF(FIND("A1", {Characters}),'','A1 [57]\n'))&''&	IF(FIND("A2",{🗃 Chapter 58}),IF(FIND("A1", {Characters}),'','A1 [58]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 59}),IF(FIND("A1", {Characters}),'','A1 [59]\n'))&''&	IF(FIND("A2",{🗃 Chapter 60}),IF(FIND("A1", {Characters}),'','A1 [60]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 61}),IF(FIND("A1", {Characters}),'','A1 [61]\n'))&''&	IF(FIND("A2",{🗃 Chapter 62}),IF(FIND("A1", {Characters}),'','A1 [62]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 63}),IF(FIND("A1", {Characters}),'','A1 [63]\n'))&''&	IF(FIND("A2",{🗃 Chapter 64}),IF(FIND("A1", {Characters}),'','A1 [64]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 65}),IF(FIND("A1", {Characters}),'','A1 [65]\n'))&''&	IF(FIND("A2",{🗃 Chapter 66}),IF(FIND("A1", {Characters}),'','A1 [66]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 67}),IF(FIND("A1", {Characters}),'','A1 [67]\n'))&''&	IF(FIND("A2",{🗃 Chapter 68}),IF(FIND("A1", {Characters}),'','A1 [68]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 69}),IF(FIND("A1", {Characters}),'','A1 [69]\n'))&''&	IF(FIND("A2",{🗃 Chapter 70}),IF(FIND("A1", {Characters}),'','A1 [70]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 71}),IF(FIND("A1", {Characters}),'','A1 [71]\n'))&''&	IF(FIND("A2",{🗃 Chapter 72}),IF(FIND("A1", {Characters}),'','A1 [72]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 73}),IF(FIND("A1", {Characters}),'','A1 [73]\n'))&''&	IF(FIND("A2",{🗃 Chapter 74}),IF(FIND("A1", {Characters}),'','A1 [74]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 75}),IF(FIND("A1", {Characters}),'','A1 [75]\n'))&''&	IF(FIND("A2",{🗃 Chapter 76}),IF(FIND("A1", {Characters}),'','A1 [76]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 77}),IF(FIND("A1", {Characters}),'','A1 [77]\n'))&''&	IF(FIND("A2",{🗃 Chapter 78}),IF(FIND("A1", {Characters}),'','A1 [78]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 79}),IF(FIND("A1", {Characters}),'','A1 [79]\n'))&''&	IF(FIND("A2",{🗃 Chapter 80}),IF(FIND("A1", {Characters}),'','A1 [80]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 81}),IF(FIND("A1", {Characters}),'','A1 [81]\n'))&''&	IF(FIND("A2",{🗃 Chapter 82}),IF(FIND("A1", {Characters}),'','A1 [82]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 83}),IF(FIND("A1", {Characters}),'','A1 [83]\n'))&''&	IF(FIND("A2",{🗃 Chapter 84}),IF(FIND("A1", {Characters}),'','A1 [84]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 85}),IF(FIND("A1", {Characters}),'','A1 [85]\n'))&''&	IF(FIND("A2",{🗃 Chapter 86}),IF(FIND("A1", {Characters}),'','A1 [86]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 87}),IF(FIND("A1", {Characters}),'','A1 [87]\n'))&''&	IF(FIND("A2",{🗃 Chapter 88}),IF(FIND("A1", {Characters}),'','A1 [88]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 89}),IF(FIND("A1", {Characters}),'','A1 [89]\n'))&''&	IF(FIND("A2",{🗃 Chapter 90}),IF(FIND("A1", {Characters}),'','A1 [90]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 91}),IF(FIND("A1", {Characters}),'','A1 [91]\n'))&''&	IF(FIND("A2",{🗃 Chapter 92}),IF(FIND("A1", {Characters}),'','A1 [92]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 93}),IF(FIND("A1", {Characters}),'','A1 [93]\n'))&''&	IF(FIND("A2",{🗃 Chapter 94}),IF(FIND("A1", {Characters}),'','A1 [94]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 95}),IF(FIND("A1", {Characters}),'','A1 [95]\n'))&''&	IF(FIND("A2",{🗃 Chapter 96}),IF(FIND("A1", {Characters}),'','A1 [96]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 97}),IF(FIND("A1", {Characters}),'','A1 [97]\n'))&''&	IF(FIND("A2",{🗃 Chapter 98}),IF(FIND("A1", {Characters}),'','A1 [98]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 99}),IF(FIND("A1", {Characters}),'','A1 [99]\n'))&''&	IF(FIND("A2",{🗃 Chapter 100}),IF(FIND("A1", {Characters}),'','A1 [100]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 101}),IF(FIND("A1", {Characters}),'','A1 [101]\n'))&''&	IF(FIND("A2",{🗃 Chapter 102}),IF(FIND("A1", {Characters}),'','A1 [102]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 103}),IF(FIND("A1", {Characters}),'','A1 [103]\n'))&''&	IF(FIND("A2",{🗃 Chapter 104}),IF(FIND("A1", {Characters}),'','A1 [104]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 105}),IF(FIND("A1", {Characters}),'','A1 [105]\n'))&''&	IF(FIND("A2",{🗃 Chapter 106}),IF(FIND("A1", {Characters}),'','A1 [106]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 107}),IF(FIND("A1", {Characters}),'','A1 [107]\n'))&''&	IF(FIND("A2",{🗃 Chapter 108}),IF(FIND("A1", {Characters}),'','A1 [108]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 109}),IF(FIND("A1", {Characters}),'','A1 [109]\n'))&''&	IF(FIND("A2",{🗃 Chapter 110}),IF(FIND("A1", {Characters}),'','A1 [110]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 111}),IF(FIND("A1", {Characters}),'','A1 [111]\n'))&''&	IF(FIND("A2",{🗃 Chapter 112}),IF(FIND("A1", {Characters}),'','A1 [112]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 113}),IF(FIND("A1", {Characters}),'','A1 [113]\n'))&''&	IF(FIND("A2",{🗃 Chapter 114}),IF(FIND("A1", {Characters}),'','A1 [114]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 115}),IF(FIND("A1", {Characters}),'','A1 [115]\n'))&''&	IF(FIND("A2",{🗃 Chapter 116}),IF(FIND("A1", {Characters}),'','A1 [116]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 117}),IF(FIND("A1", {Characters}),'','A1 [117]\n'))&''&	IF(FIND("A2",{🗃 Chapter 118}),IF(FIND("A1", {Characters}),'','A1 [118]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 119}),IF(FIND("A1", {Characters}),'','A1 [119]\n'))&''&	IF(FIND("A2",{🗃 Chapter 120}),IF(FIND("A1", {Characters}),'','A1 [120]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 121}),IF(FIND("A1", {Characters}),'','A1 [121]\n'))&''&	IF(FIND("A2",{🗃 Chapter 122}),IF(FIND("A1", {Characters}),'','A1 [122]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 123}),IF(FIND("A1", {Characters}),'','A1 [123]\n'))&''&	IF(FIND("A2",{🗃 Chapter 124}),IF(FIND("A1", {Characters}),'','A1 [124]\n'))&''&
			IF(FIND("A2",{🗃 Chapter 125}),IF(FIND("A1", {Characters}),'','A1 [125]\n'))
		)		
	)
)

A1 and A2 gets replaced with character name and variation. It works just fine, but is as you see very bulky. The issue is that I need to check for up to 300 different characters.

For simplicity I want it to mention in what chapter(-s) it found the character, as the code does, even though removing that would cut out some bulkyness, so that is not an option to help trim it down.

So my question is if this can be condensed to something way more elegant?
I assume scripting would be the ideal route to go, but as is that is a completely foreign land and language for me.

If it can be condensed I would be ever so thankful if you could explain the how, so that I can (hopefully) learn a bit better.

Bill_French · ‎Mar 22, 2021

It may be that the problem is not the formula; rather the data model design. If you have to add fields to accommodate a new chapter, that’s not ideal.

When I see a massive number of fields that act as classifiers, it’s almost a sure sign that the data model can be optimized, and such optimizations will lead to far easier filtering and conditional processing without formulaic delirium. :winking_face:

momentsgoneby80 · ‎Mar 22, 2021

Any idea for a better model?
The reason for the many, many fields are that stories almost always, and sometimes even chapters, excedes maximum number of caracters a cell can hold and the easiest way to ensure this doesn’t happen is to split it to multiple fields. A story can be anything from 1K+ to 500K+ words.
I’m normally a fan of no more fields than neccessary, but here it’s fields-gallore. Only because it adresses the issue (but creates a formula-giant instead).

If there is a better way to go about it I would be ever so thankful for a hint, or an entire map :winking_face: , of shortest road to greatness.

Bill_French · ‎Mar 22, 2021

One idea - stories [and perhaps all chapters in aggregate] should, therefore, be stored as attachment documents and processing should occur with script processes, not formula fields.

momentsgoneby80 · ‎Mar 22, 2021

That makes all kinds of sense. Unfortunately scripts are entierly foreign to me and my brain is not working at ideal capacity right now due to illness, so learning a new language (Java?) is out of my ability at the moment, but very tempting for the future.

This base is just a base that I tinker with for something to entertain my brain with (within limits) while recovering. I will definitely mark your answer for later investigation and tinkering.

kuovonne · ‎Mar 22, 2021

You could move chapters to a linked table, with one chapter per record. Then each chapter could be responsible for checking for its own characters.

However, if you are using a free workspace, you may have trouble with the record limits.

Moving chapters to attachments could free up space, but formulas cannot search inside attachments. Getting a script to search inside an attachment is doable, but not an easy task for someone new to coding.

momentsgoneby80 · ‎Mar 22, 2021

Thank you @kuovonne for your input.

This base is just for fun and learning, and to keep track of what I’ve read and and not. With the current setup I get it to easily import warnings, different tags and other data together with the story in it’s entierty with 3 clicks.
The base is in a PRO workspace, so record limit isn’t an issue.
The clunkyness of the formula isn’t an issue either, for now at least.

My question about condensing it has more to do with learning and elegance than functionality to be honest. I see these elegant solutions to various problems in the forum and I wanted input on if this could be condensed in any way, and if so a hint or two as to how.

I’m really greatful for the ideas for different approaches I’ve gotten. It informs my future learning and projects.

kuovonne · ‎Mar 22, 2021

Learning how to write an elegant function is a combination of art and part logic. It requires knowing all the vocabulary available to you: both the functions you can use, and the arguments they can take. It also requires understanding the underlying logic of what you are trying to accomplish. Here are just a few things to consider:

Can a different function call simplify a longer pattern, such as converting nested IFs to a SWITCH?
Can the logic be simplified by looking for the opposite condition?
Does the logic contain redundant parts that can be removed?
Of the different functions and operators that will accomplish the same result, which will be the most clear?

Writing an elegant function also requires recognizing that there are many ways to accomplish something in code, and thus many possible working solution. One method of improving your code is to practice refactoring–rewriting the code so that it does the same thing, but in a different way.

Finally, the best solution is the one you understand and can maintain.

momentsgoneby80 · ‎Mar 22, 2021

Thank you @kuovonne!
That is great advice and nicely breaks down what I can try to improve.
I’m dealing with a temporary setback in what my brain can process due to illness, so scripts look so intresting, but I know it’s a too big a project right now, but a nice carrot for the future.

Your advice is a great road map for what I can do for now to become better, within something that is within my current ability. So truly, thank you!