Formula Kerfuffles

[Squirrel]

I need to create a formula that changes values in a field that should be a list only in the column named “categories”. Currently all values are bundled as one value. For example there are four total values: INTELLIGENCE, INFLUENCE, ASSET, MISCELLANEOUS
Currenlty in a field they can be any combination like this
[“INFLUENCE", "INTELLIGENCE", “MISCELLANEOUS", “ASSET”]
Each field can be a single value or any combination of values. When it’s combined, then it shows that [“INFLUENCE", “INTELLIGENCE”] is one single entity. This of course makes analysis maddening.

I need to add a backslash to the end of the value before the end quote like this:
[“INFLUENCE\", “MISCELLANEOUS\", “ASSET\”] or ["INTELLIGENCE\”]

I tried:
replace(categories, '"', '\\"')

This added a double backslash and missed the last value.
RESULTS: [“INTELLIGENCE\\", “INFLUENCE\\", "ASSET”]

I tried:
replace(categories, '"', '\”')

When I removed one backslash, it appropriately added one backslash but the results were missing one of the backslashes as follows:
["INTELLIGENCE\", "INFLUENCE\", "ASSET"]

Based on the insistence of the AI assistant (BING, I’m lookin at you), I tried this formula
replace(categories, '"', ‘\\\”')
Which, of course, did nothing but add three backslashes and still missed the last value.

What am I missing?

[dave]

@Squirrel

When I removed one backslash, it appropriately added one backslash but the results were missing one of the backslashes as follows:
["INTELLIGENCE\", "INFLUENCE\", "ASSET"]

I think your last quote probably wasn’t a right smart quote, because that version does work for me.

[Squirrel]

Thanks for helping me noodle this!

This was the formula I used that was closest to working correctly.

@Squirrel

replace(categories, ‘"’, ‘\”')

.
I’m confused how your formula would work - The formula you used is different. It has no identifier for the column (categories) that contains the values to change and it already implants the values.

I’m not sure about the smart quotes. How can one tell if a quote is smart?

[dave]

@Squirrel

I’m confused how your formula would work - The formula you used is different. It has no identifier for the column (categories) that contains the values to change and it already implants the values.

Since I don’t have a database with a categories field, containing text that is formatted like that, I just used a quoted string of text as the first parameter of the replace( function. When a function is called, all it ever sees are the values of its parameters. It does not see where those values came from. As far as the function is concerned, a quoted string of text, is the same as the name of a field or variable containing that text. I used pipes to quote the text.

@Squirrel

I’m not sure about the smart quotes. How can one tell if a quote is smart?

Smart quotes, are also called curly quotes, and you have to look closely to tell the difference between straight and curly or between left and right.

@Squirrel

This was the formula I used that was closest to working correctly.

@Squirrel

replace(categories, ‘"’, ‘\”')

I believe the formula was working correctly. The problem was with the data, not the formula.

To isolate the records, where the quotes were wrong, I would first search for records containing straight quotes, using the formula

categories contains {"}

Once I had fixed those, I would search for records where the number of left curly quotes doesn’t match the number of right curly quotes, using the formula

arraysize(categories,{“}) ≠ arraysize(categories,{”})

[gary]

Here is an alternate approach which will reprocess the strings completely and not be bothered by smart quotes (which may have been introduced when copying into the forum and not in the original data). This uses arrayfilter( to remove all non-alpha characters from each element and add the back slash and quotes and finally the enclosing brackets. I show this as a formulafill for the categories field.

formulafill "["+arrayfilter(categories,",",|||quoted(striptoalpha(import())+"\")|||)+"]"

[dave]

@gary

(which may have been introduced when copying into the forum and not in the original data)

If there hadn’t been smart quotes in the original data, none of his formulas would have produced any backslashes at all.

[gary]

Dave, your original formula seems strange in that if all the quotes in the string are plain quotes why are the beginning and ending quotes not both changed to "? I tried this in the Formula Workshop with text I knew had plain quotes and all quotes were replaced with ".

[dave]

@gary

Dave, your original formula seems strange in that if all the quotes in the string are plain quotes

No. They are all smart quotes. As I said in an earlier post. You need to look closely to tell the difference. Try expanding the image.

[gary]

Since I have some macular degeneration in my right eye and a cataract starting in my left eye, I’ll use that as my excuse.

[Squirrel]

I tried the first general search and replace from your initial screenshot. This was my result

I had to capture the formula and OCR it, so it is possible some characters got scrambled. Which may be why Gary did not get an error but I did? Just spitballing…

I ran this

@dave

To isolate the records, where the quotes were wrong, I would first search for records containing straight quotes, using the formula

categories contains {"}

And got this result

Without fixing anything I then ran the second formula

@dave

Once I had fixed those, I would search for records where the number of left curly quotes doesn’t match the number of right curly quotes, using the formula

arraysize(categories,{“}) ≠ arraysize(categories,{”})

And got this (which was expected since nothing was altered)

Does that give any better insight into my issue?

[Squirrel]

Hi Gary! Thanks for jumping in.

I tried this

@gary

Here is an alternate approach which will reprocess the strings completely and not be bothered by smart quotes (which may have been introduced when copying into the forum and not in the original data). This uses arrayfilter( to remove all non-alpha characters from each element and add the back slash and quotes and finally the enclosing brackets. I show this as a formulafill for the categories field.

formulafill "["+arrayfilter(categories,",",|||quoted(striptoalpha(import())+"

And got this error message in the Workshop

Any thoughts?

[Squirrel]

The other thing that seems to have happened in your trial is \ was added in front of (not what I need) and ASSET still has no trailing .

[gary]

@gary

formulafill "["+arrayfilter(categories,",",|||quoted(striptoalpha(import())+"\")|||)+"]"

What I showed in the above code is a statement to be used in a procedure. If you jut want to try the formula part in the Formula Workshop you need to remove the “formulafill” part and just use the formula itself.

"["+arrayfilter(categories,",",|||quoted(striptoalpha(import())+"\")|||)+"]"

@Squirrel

The other thing that seems to have happened in your trial is \ was added in front of (not what I need) and ASSET still has no trailing .

You must be referring here to my second post that was only meant to show what would be the result of Dave’s formula if used with text containing only plain quotation marks.

[pcnewble]

@gary

just use the formula itself.

"["+arrayfilter(categories,",",|||quoted(striptoalpha(import())+"\")|||)+"]"`

but without that spurious grave accent as the last character!

[gary]

Thanks for catching that Peter. I never noticed it being included and not sure how it got there but I’ve removed it from my original post to avoid confusion.

[dave]

@Squirrel

Without fixing anything I then ran the second formula

@dave

Once I had fixed those, I would search for records where the number of left curly quotes doesn’t match the number of right curly quotes, using the formula

arraysize(categories,{“}) ≠ arraysize(categories,{”})

And got this (which was expected since nothing was altered)

Does that give any better insight into my issue?

Those formulas were meant to be used in a search, which might be done in a couple of ways. One, would be the Find/Select dialog.

Another, would be to write a Select statement in a procedure.

The result of -1 that you got for the second formula means that formula returned a value of true() for the record that happened to be active at that time. The Select statement scans the records in the database, evaluating its formula in each record. Those records where the formula returns a true value are selected, and those which return a false value are not. -1 is true and 0 is false. The Formula Workshop only runs the formula you are testing once, in the context of whichever record is active at that moment, so in that one record, it was true that the number of left quotes did not equal the number of right quotes. At least one of the quotes in that record was wrong.

[Squirrel]

Understood. Clearly I am keen but… learning curve.

Appreciate you helping me out.

[Squirrel]

Ah! Okay! Let me try it as a procedure!

[Squirrel]

@gary

If you jut want to try the formula part in the Formula Workshop you need to remove the “formulafill” part and just use the formula itself.

"["+arrayfilter(categories,",",|||quoted(striptoalpha(import())

This worked like a charm! Thank you!

I used the formula and then saved it as a Procedure.

Is there an easy way to adapt that to alter things like this

[mclays, company, limited, data, mclays, uk]

Currently they get read as one thing vs different things in a list.

To make something into an individual value it needs [“mclays\”, “company\”]

[dave]

@Squirrel

Is there an easy way to adapt that to alter things like this

[mclays, company, limited, data, mclays, uk]

If these things are in a different field than the categories field, then you would have to use that field name in your formula, instead of categories. Other than that, it wouldn’t need to be adapted at all to change
[mclays, company, limited, data, mclays, uk]
to
["mclays\","company\","limited\","data\","mclays\","uk\"]