QuickBase Database cleanup, reserved characters, and Boolean search logic

Question

Before starting QuickBase at my company, I inherited a lead list of 5000+ potential customers, which is one of the first things I transferred over once we started using the product. Unfortunately, these lists came from a variety of sources and therefore the customer names had terribly inconsistent formatting. For example, some are listed in all caps and some normal lowercase. Up until now, this was not a huge deal. We use the customer name as the link in orders/sales activities/etc. back to the customer's form.

Recently, I realized that this link was sometimes breaking for our iPhone users if the club name included an "&" sign. Knowing that this is sometimes a restricted character, I painstakingly went through and updated all of the clubs with "&," and updated the link in all related records. I also noticed while doing this that for the customers listed in all capital letters, changing it to "AND" instead of "and" made it unreadable in table-to-table relationship summary tables. I can only assume this is related to Boolean search logic, since everything was fine once I switched it to "and."

A few related questions:

1) Are there any other restricted characters that might break links? Does it differ between PC, iPhone, and Android users?

2) Is it possible to search for "AND," "OR," and "NOT," in all-cap form specifically? Or create a filter to capture these? My tests so far indicate no, but there are a lot of smart people on here :) I am hoping there is a smart way to filter for these, because there are over 1000 customers with some combination of "and," "or," and "not" that I'd prefer not to check one by one.

3) When does the Boolean logic issue truly kick in? Do AND, OR, and NOT have to be isolated, or could it screw things up if they're within a word? For example, do I only need to be worried about customers listed like "CITY HOOK AND LADDER," or will those like "OREGON HUNTING CLUB" also break the system?

I appreciate your help!

quickbasecoachd · Answer

I don't have all your answers, but AND and OR are only reserved if they are in caps and stand alone. &nbsp;ie no problem for OREGON.&nbsp;

bullseyebullsey · Answer

That is certainly a relief, thanks! I'll still have my work cut out for me, but that will cut down significantly on the number of records I'll have to update.

_anomdiebolt_ · Answer

While it is always better to scrub your data before entry into a new system you can use script to automate the cleanup of data already in QuickBase.

This post received some bad formatting in the conversion to the new forum but it provided a general solution of specifying as set of regular expression patterns to clean up a set of records identified by a query:

What is the "Clear the Swamp" Technique?
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=531

In a nutshell you supplied a set of parameters to identify what records and fields needed to be processed:

 var dbid = "bmb3gqcsb";
 var apptoken = "pkcstad5vrharc4664uucpt258d";
 var qid = "1";
 var fidSource = "6";
 var fidSourceTag = "text1";
 var fidTarget = "7";
 var fidTargetTag = "text2";
 var batchSize = 20;

And then a set of rules to apply to the records:

 var rules = [{
   name: "To Title Case",
   pattern: /(?:^|\s)\w/g,
   replace: function(match) {
     return match.toUpperCase();
   }]

The above rule converted the text in the field to UPPER CASE using a regular expression. to do the matching an a replacement function to do the substitutions. For safety the script was written to write the changes to new field but the source and target fid could point to the same field.

_anomdiebolt_ · Answer

While it is always better to scrub your data before entry into a new system you can use script to automate the cleanup of data already in QuickBase.

This post received some bad formatting in the conversion to the new forum but it provided a general solution of specifying as set of regular expression patterns to clean up a set of records identified by a query:

What is the "Clear the Swamp" Technique?
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=531

In a nutshell you supplied a set of parameters to identify what records and fields needed to be processed:

 var dbid = "bmb3gqcsb";
 var apptoken = "pkcstad5vrharc4664uucpt258d";
 var qid = "1";
 var fidSource = "6";
 var fidSourceTag = "text1";
 var fidTarget = "7";
 var fidTargetTag = "text2";
 var batchSize = 20;

And then a set of rules to apply to the records:

 var rules = [{
   name: "To Title Case",
   pattern: /(?:^|\s)\w/g,
   replace: function(match) {
     return match.toUpperCase();
   }]

The above rule converted the text in the field to UPPER CASE using a regular expression. to do the matching an a replacement function to do the substitutions. For safety the script was written to write the changes to new field but the source and target fid could point to the same field.

_anomdiebolt_ · Answer

Regarding the ampersand and other "special" characters there might be another problem going on and it is not clear if QuickBase has fixed the problem or not without a lot of focused testing which I do not have the time for that the moment. Recently several people reported text wrapping on pages (where a non-breaking space character was & n b s p ;) and the appearance of the replacement character in random places on pages:

This problem was reported after they migrated to their new servers.

This is a telltale sign that there is a character encoding problem. At various times in the last week I have noticed the character encoding has changed between utf-8 and windows-1252 and it is currently set to windows-1252. as tested in FireFox, Chrome and IE11 today from the console:

I don't know the status of this issue but it might be contributing to what you are seeing. Only QuickBase can fix this issue.

Forum Discussion

QuickBase Database cleanup, reserved characters, and Boolean search logic

Related Content

Loan database - Interest period

Database Expert Needed

QuickBase Scripts

Rebuild Database Expert Needed

Posting from an HTML form directly to Quickbase