An expression to insert HTML tags for duplicate text

[Kirk_Masden]

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:

[Rich Text Formatting - in Preview Program](https://community.appsheet.com/t/rich-text-formatting-in-preview-program/52714) Announcements

The following feature has been added to the Preview Program. Feel free to try out the feature and report issues or possible improvements in this thread. AppSheet is expanding how text can be formatted. For data that is typed as LongText, app creators can now choose to render Markdown or HTML in Detail views. Note: In all views other than Detail, the value will render as plain text. How do app creators use the feature? Start with data that has HTML or Markdown inputs. In this example, th…

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.

[bonameblisto]

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

[Kirk_Masden]

bonameblisto:

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

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

[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]

tsuji_koichi:

Withtout hard-coding the html string

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]

tsuji_koichi:

but take time to save changes

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.