Transitioning to UTF-8
Hi everyone,
Can any of you share with me how you navigated migrating your realm to UTF-8?
We've only had QB for 2 years, but apparently, UTF-8 wasn't enabled when we started. Over the past year...
Forum Discussion
Hi Jennifer,
We are currently on a similar journey. When UTF-8 is enabled and you are using another 3rd party cloud service, like Workato, any non-acsii characters sent to the third party service will cause errors, this is the reason to clear the non-ascii characters. Here is what we are doing to repair the data.
I made a rich text formula field and entered the formula in the link below. The link below describes the formula and also the reference ascii Website where this information can be found. I have since removed several of the ascii characters in that formula that do not throw the error but this will get you started.
What the formula does is display all the characters that will cause an error. You then can build a report and use one of three tools to clean up the data namely: Grid Edit, Find/Replace or (Export-correct-Import). You can test by turning UTF-8 on one App at a time and looking for the black diamond with the question mark. This process has to be done for each table.
Of course if you're not using third party cloud services UTF-8 can be enabled anytime and then the data can be cleaned up when it is discovered but you won't know is hiding behind the black diamond question mark thingy.
Finding non- windows-1252 Characters in Text fields using a formula
Good luck.
------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------
Hi Jim,
Thank you so much for replying! I think your reply was buried in my inbox with all of the other QB discussion threads, and I apologize for the delay in responding to you.
I wasn't aware I can implement UTF-8 on an app-by-app basis; QB Tech Support told me they had to do it at the realm level - which made the project seem far more daunting (because then everything has to be perfect with no new imperfect data coming in).
While I'm not using 3rd party apps that are directly importing to QB; we do use forms on our website to collect data, that I am building into Sync Files in Google Drive and syncing that data weekly.
Once I have enabled UTF-8, when importing data that contains non-ascii characters such as quotation marks, apostrophe's, or parentheses (these seem to be the biggest challenges in our data), will they continue to cause issues (in your experience and opinion)?
Thank you again! This gives me the ability to develop a clear plan of action on how to tackle what otherwise felt like an overwhelming and impossible task.
Take care,
Jen
------------------------------
Jennifer Juhasz
FamilySmart
BC, Canada
------------------------------Hi Jennifer,
Enable UTF-8 per App by going to the App properties=>Advanced Settings and check the UTF-8 checkbox.
Once UTF-8 is enabled all "newly entered" non-ascii characters display normally. The problem is dealing with the "old" non-ascii characters currently in the text fields of your Apps. All those characters need to be replaced or removed.
------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------In case someone finds this thread, here is the solution I am using so far.
First search the App Exchange for: "Mixed Encoding Scanner". Which will take a App DBID and scan the entire App for non-ascii characters.
Once I know where to go, I can decide to either update each record one at a time or use something like the "Special Character Finder" text-formula field and "Find/replace in these records" all the different non-ascii characters identified by the "Special Character Finder" in the field. When the Mixed Encoding Scanner finds a symbol I haven't included in the formula I add another line.
I use the manual update when there are only a few records that need to be updated. There is a button that opens all the fields or one field at a time in the Mixed Encoding Scanner. Then Grid Edit and click four times just to activate edit to change the record. One thing I have noticed is when I save, Quickbase asks me to review every change like someone else is editing the record, which I know is not possible. So using Grid Edit takes a lot of effort and can be very frustrating.
The second method of using Find/replace in these records is a lot faster. It's annoying that the pop up window is at the opposite corner of the screen so I have to do a lot of mouse traveling. I can edit a lot of records with minimal effort.
Improvements I would make to the second option are to locate the pop-up window closer to the more drop down menu, auto refresh the report page on close of the pop-up, allow the User to go back after the Find/replace is complete to perform the next operation instead of having to start at the more drop down menu.
I have tried QuNect Razor and that identifies one field at a time one table at a time, I cannot recommend using this tool as it is since it takes considerable time to work through one record if there are multiple fields.
------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------
