Building a Calgary Database with “Searchable” Fields

[Alteryx_KB]

The option to be able to search a field with wildcard characters is often needed. This functionality within the Calgary suite of Tools can be accomplished by having a “Contains” function, where this is accomplished without utilizing a wildcard to search. This also allows you to be able to use the Indexing power that the Calgary products offer, without having to do these searches with downstream functions like Filter or other Formula based tools.






Tools used:




Here's how
1) Create a base cydb file with this option selected:






2) Once the base file has been created, you now want to modify that cydb file to enable the Fields you want to query with the “Contains” option. In this example dataset, we want to be able to do these type of queries for the “CONAME” and “ADDR” fields:


 

Setup Calgary Input into Calgary Loader, when the following message will appear. Make sure you check “Yes”
 

           


3) Select the “Load a single advanced index” option


 
Set the Index Name you would like (in this case, keeping the same name), and choose the field desired from the “Source Field”. 




Also, set Advanced Index Mode to Full Text


 
 
4) Repeat this process with all of the fields you’d like to be able to search.  In this sample, placing “mail” in the CONAME and “ln” in the ADDR fields returned this record:

[jm_idm]

If I want to create a searchable field for multiple fields, do i have to run the Calgary Loader with this setup each time or is there a way to set up multiple fields for the advanced index  in one instance of the loader?   

[StefanW]

Hello

I have a problem with special characters in a contains-search on an advanced index of a Calgary database:

• Index contains Zürich does not return any records ((note the Umlaut ü in the search criteria))
• Index contains rich returns the expected records for Zürich, but also records for Richterswil that I don't want to be included.

The same problems occurs with French accented letters, e.g. é:

• Index contains Courtételle does not return any records
• Index contains Court returns the expected records for Courtételle, but also records for Courtelary or Boncourt.

Is there any way to write criterias in a way that special characters are correctly treated?