Formula Kerfuffles

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
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:

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

This added a double backslash and missed the last value.

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:

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?

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

Thanks for helping me noodle this!

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

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?

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.

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.

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,{”})
1 Like

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())+"\")|||)+"]"
1 Like

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

1 Like

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 ".

1 Like

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.

1 Like

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. :face_with_monocle:

1 Like

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

And got this result

Without fixing anything I then ran the second formula

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

Does that give any better insight into my issue?

Hi Gary! Thanks for jumping in.

I tried this

And got this error message in the Workshop

Any thoughts?

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 .

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.


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.

1 Like

but without that spurious grave accent as the last character!

1 Like

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.

1 Like

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.

1 Like

Understood. Clearly I am keen but… learning curve.

Appreciate you helping me out.

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

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\”]

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]