Re: How to use a roll up field to fill a formula field
how can i use a roll up field to dynamically populate a formula? Is it possible?
Thanks to everyone
Sorry for the delayed reply. I appreciate the video that you shared. It definitely makes your end goal clear.
After a little play, I was able to work up something that effectively does the same thing, but in a slightly different way. Instead of dynamically updating a formula (which won’t work as I said earlier), it dynamically updates a couple of dictionary-type strings, which are then used by a single unchanging formula to find any item in the dictionary and replace it with its associated alternate item (or category or whatever you’re calling it).
Your video doesn’t show every detail of your setup, but I think that I understand how it works. The [F C C] table is where you are creating the basic from-to relationship in {Parola da cercare} (“word to search for”) and {Parola da scrivere} (“word to write”). My hunch is that each of these records is linked to that single record in the [F C C D], where you’re aggregating them all using one or more rollup fields and using some formulas to build the final formula. You were then hoping to roll up that formula in your primary table by linking all records to it, but for now you’re just copying and pasting it. If I’m reading that setup correctly, then several parts can stay exactly as they are to convert it to the setup that I built. Here’s the breakdown of mine…
F C C
In this table, add a formula field that concatenates {Parola da cercare} with {Parola da scrivere}, separating them with a colon, like this:
{Parola da cercare} & ":" & {Parola da scrivere}
This, of course, assumes that you don’t have a colon in any of the strings that you’re searching for. If you do, you’ll need to replace that separator with some other unique character that’s not part of any of your entries in either field. Everything that I write below is based on using a colon as the separator, but I’ll try to point out where to change it if needed.
Here’s how my test table looks with this setup:
F C C D
In this table, you’ll need two rollup fields. The first brings in the entries from the {Parola da cercare} field, using ARRAYJOIN() to combine them using a vertical bar separator:
The second rollup field is setup the same way, but pulls in the contents of the formula field that I labeled {Combined} in my sample screenshot above. I named these two rollup fields {Dictionary Base} and {Dictionary Full}.
Movimenti
In this table, I’m assuming that you already have a link field that connects every record to the single record in the [F C C D] table. You’ll use that link field in the process of adding two rollup fields, one each to pull in the contents of the {Dictionary Base} and {Dictionary Full} fields from [F C C D]. For the aggregation formula, just use values & "", which will output a string. That should give you something like this in all of your records in the [Movimenti] table:
Now all you need is a single formula field with this formula:
IF(
{Description},
IF(
REGEX_MATCH({Description}, {Dictionary Base}),
REGEX_EXTRACT({Dictionary Full}, "(?:" & REGEX_EXTRACT({Description}, {Dictionary Base}) & "\\:)([^\\|]*)(?:.*)")
)
)
Long story short, that formula first checks to see if you have a description in the {Description} field. If so, it tries to use the {Dictionary Base} string to match text in the description. The vertical bar separator in that string is effectively an “or” operator. Using my example, it will look for “Alpha” OR “Beta” OR “Gamma”. If any of them match, then it will extract the appropriate related item from the full dictionary string by finding the same item followed by a colon, and then collecting everything between the colon and the next vertical bar (or the end of the string, whichever comes first).
The nice thing about this setup is that you never need to change the final formula. Just add more entries to the [F C C] table.
Alternate option
With all of that said, I must admit that I’d be more inclined to solve this problem via scripting. I’d still use a table similar to your [F C C] table, but I’d then use a script—either triggered via automation, or run manually—to read those values, process the new transaction record(s) and output the appropriate data after matching. It avoids all of the extra fields (and one extra table) needed just to figure out that one data item. If you’d like to explore how a script could take care of this, message me directly and we can discuss the options.
