An expression to insert HTML tags for duplicate te...

Kirk_Masden · 09-18-2021 10:40 PM

Hi! I have an app that displays data as follows:

Both columns look like lists of numbers but the format is text that has been written to a spreadsheet when data was archived (this is part of a record of app usage).

I would like to write an expression that compares the set of numbers in “D to W session in which ‘GOT IT’ was tapped” with those in “D to W sessions in which a mistake was made” and writes the following to a column in the spreadsheet as text:

<em>1368</em>, 1369, 1370, 1371, 1375, 1383, 1399, <em>1432</em>, 1445, <em>1465</em>, 1478, 1498, 1531

That would allow me to take advantage of the new rich text formatting capability in order to emphasize the sessions in which a mistake was made:

One difficulty is that the number of items in the top column can be between 6 and about 23 or so and the number of items in lower column can be between 0 and about 10 or so.

I imagine that, if this is possible, it would be a pretty complicated expression but it only needs to be written once for each record and that writing task doesn’t come up that often so, even if the expression is a little expensive, I think that would be OK.

Thanks for your attention!

P.S. The only approach that comes to mind is 10 nested SUBSTITUTE() expressions. That would probably require lots of IF() expressions to prevent errors and SPLIT() and INDEX() expressions to chop the strings up so that their parts can be compared. Is that what is required?

Steve

Yikes! I can’t think of a good way.

Kirk_Masden

After thinking about it for a while, I came up with this rather expensive and inelegant solution. As I say, however, it will only be used once per new record when being written by an action, so this may be OK.

if(
isblank([List 2]),[List 1],
concatenate(
if(count(split([List 1],", "))>0,
if(in(index(split([List 1],", "),1),split([List 2],", ")),concatenate("<b>",index(split([List 1],", "),1),"</b>"),index(split([List 1],", "),1)),""),
if(count(split([List 1],", "))>1,
if(in(index(split([List 1],", "),2),split([List 2],", ")),concatenate(", <b>",index(split([List 1],", "),2),"</b>"),concatenate(", ",index(split([List 1],", "),2))),""),
if(count(split([List 1],", "))>2,
if(in(index(split([List 1],", "),3),split([List 2],", ")),concatenate(", <b>",index(split([List 1],", "),3),"</b>"),concatenate(", ",index(split([List 1],", "),3))),""),
if(count(split([List 1],", "))>3,
if(in(index(split([List 1],", "),4),split([List 2],", ")),concatenate(", <b>",index(split([List 1],", "),4),"</b>"),concatenate(", ",index(split([List 1],", "),4))),""),
if(count(split([List 1],", "))>4,
if(in(index(split([List 1],", "),5),split([List 2],", ")),concatenate(", <b>",index(split([List 1],", "),5),"</b>"),concatenate(", ",index(split([List 1],", "),5))),""),
if(count(split([List 1],", "))>5,
if(in(index(split([List 1],", "),6),split([List 2],", ")),concatenate(", <b>",index(split([List 1],", "),6),"</b>"),concatenate(", ",index(split([List 1],", "),6))),""),
if(count(split([List 1],", "))>6,
if(in(index(split([List 1],", "),7),split([List 2],", ")),concatenate(", <b>",index(split([List 1],", "),7),"</b>"),concatenate(", ",index(split([List 1],", "),7))),"")
)
)

The idea is to go through List 1 item by item and check to see if each item is in List 2. If it is, it gets encapsulated with

<b> and </b>.

If not, it is reproduced unchanged.

This only goes up to seven, but I think I can follow this pattern up to 25 or whatever. If anyone else has a better idea, please let me know.

P.S. Sorry, I forgot how to share long, complicated expressions.

Koichi_Tsuji

Withtout hard-coding the html string, you would be able to achieve that, using LOOPING action. Once the action is fired, action will look up the whole list (list 1 + list 2) and examine items one by one. If the items is duplicated, then pass the value with html tag else the raw number. The values will be added to long text type field.

Kirk_Masden

Thanks! I think I want to hard cord the html string to avoid performance issues (one time and it’s done vs. doing it every time). So, I guess that, in my case, it’s better to use a long expression in one action, right?

Koichi_Tsuji

It depends what you prefer and prioritize. Loop action is super quick , but take time to save changes.

Hard coding is not flexible at all, while loop action is flex.

Kirk_Masden

Right. I think I’d like to have the changes save quickly (just one writing act) so in this case I’ll stick with the long expression. Thanks again!

Koichi_Tsuji

No problem
If you select loop action, you can run it on the backend, by bot. That’s another option to be in mind. Good luck.

bonameblisto

How important to save the original order of items in list?

Kirk_Masden

Thanks for your response. In this case, I really do want to keep the order the same. I think I can imagine subtracting one list from the other and then putting all of the common items together either in the beginning or the end. That might work for some people but not for me in this case.

bonameblisto

Ok.
I’m have same problem in one of my project. Order extremely important. Without elementary “loop” construction, i write expressions in ten times longer than your
I’m think to write separate application to generate such exspressions

An expression to insert HTML tags for duplicate text