Popup YouTube Video
Sheerpower Logo

Using Clusters for Mapping and High-speed Lookups

The FINDROW() High-Speed Data Lookup Function

Clusters provide a powerful way to create high-speed lookup tables, ideal for tasks such as translation. Each field within a cluster can serve as a searchable key, and lookups are performed using the findrow() function.

findrow() supports all data types, allows duplicate keys, and permits key values to change over time. Missing or unset values are handled naturally using empty strings or zero—without special cases, performance penalties, or data loss from duplicate empty keys.

findrow() is a highly optimized function that uses hashing to perform lookups in O(1) constant time, enabling over 10 million searches per second on a modern PC, regardless of the number of rows. This makes findrow() ideal for workloads requiring fast, deterministic data access.

Given a cluster name, a cluster field to search, and a value to match, findrow() returns the first row where the key is found and makes that row current, or returns 0 if the key is not found.

To handle duplicate keys, findrow() supports an optional parameter that selects the Nth occurrence of a matching key.

  • All cluster fields, regardless of data type, are searchable
  • All key values are mutable
  • Duplicate keys are fully supported
  • All key lookups execute in O(1) constant time
By default, all searches are case-insensitive. Set the optional fourth parameter to TRUE if you want a case-sensitive search.

Because all cluster fields are keys, bi-directional lookups are easy to do. In our example we will translate from English to German and German to English.

To define a cluster, use the cluster statement followed by the name of the cluster. In our example, we are calling our cluster trans. Our trans cluster has two fields: english$ and german$. To reference them we use trans->english$ and trans->german$.
cluster trans: english$, german$
The code below uses the trans cluster to translate from English to German and German to English.
// simple translation dictionary cluster trans: english$, german$ add cluster trans: english$ = 'apple', german$ = 'der Apfel' add cluster trans: english$ = 'banana', german$ = 'die Banane' word$ = a$ do // First, try translating English to German row = findrow(trans->english$, word$) if row > 0 then print word$;' >> '; trans->german$ exit do end if // Not found as English, try German row = findrow(trans->german$, word$) if row > 0 then print word$;' >> '; trans->english$ exit do end if print 'No translation for '; word$ end do

Program Explanation:

1. Cluster Definition:

A cluster named trans is defined to store English words and their corresponding German translations. The cluster has two fields: english$ for English words and german$ for German words.

2. Adding Data:

The program adds entries to the trans cluster:

  • The English word 'apple' is paired with the German word 'der Apfel'.
  • The English word 'banana' is paired with the German word 'die Banane'.

3. User Input Assignment:

The variable word$ is assigned the value of a$, which is assumed to be a word entered by the user that they want to translate.

4. Searching for English Word:

The program searches the trans cluster for a row where the english$ field matches word$ (the word entered by the user). The row number is stored in the variable row.

5. English to German Translation:

If the word is found (row > 0), the program prints the English word followed by its German translation, using ' >> ' as the separator. The loop then exits since the translation has been found.

6. Searching for German Word:

If the word wasn't found in the English field, the program then searches the trans cluster for a row where the german$ field matches word$.

7. German to English Translation:

If the word is found in the German field (row > 0), the program prints the German word followed by its English translation, again using ' >> ' as the separator. The loop then exits since the translation has been found.

8. No Translation Found:

If the word is not found in either the English or German fields, the program prints a message indicating that there is no translation available for the word entered by the user.

Summary:
  • findrow() is a core language feature for fast, deterministic data access, not a helper routine or external index.
  • A single cluster can support bi-directional lookups across multiple fields, enabling flexible data models without duplicating or restructuring data.
  • All cluster fields are first-class keys, allowing lookups on any attribute with no additional setup or configuration.
  • Lookups execute in O(1) constant time regardless of cluster size, preserving predictable performance as data grows.
  • Duplicate, mutable, and empty keys are handled naturally, eliminating special-case logic and preventing data loss or indexing errors.
  • By integrating hashing and lookup semantics at the language level, findrow() removes an entire class of application-level indexing code while remaining simple to read and reason about.

Hide Description

    

        


      

      
Enter or modify the code below, and then click on RUN

     

    		

  

  
  

    
    

    
  
  

  
  

  
 

  



    

    Looking for the full power of Sheerpower? 
    

    Check out the 
   
   Sheerpower website.
   
   Free to download.  Free to use.