Popup YouTube Video
Sheerpower Logo

High-speed Lookups with Clusters: FINDROW(), FINDVALUE(), and FINDVALUE$()

Each function below opens with the idea; click a Show me line for its syntax and examples, or Expand all.

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, subject to available RAM. This makes findrow() ideal for workloads requiring fast, predictable data access.

Note: Hashing structures are built on demand the first time a field is searched, and are maintained automatically thereafter. Each hashed field sustains roughly 5 million value additions or changes per second on a modern PC.

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.

Show me: calling findrow(), duplicate keys, and examples
  • 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. For a case-sensitive search add the named option exact: true — findrow(cities->city$, 'Cebu', exact: true) — with no need to spell the occurrence number first. (The older spelling, TRUE as the fourth argument after an explicit nth, still works.)

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.


Finding a Row by Two Fields at Once

Sometimes one field cannot identify the row you want. An order belongs to a customer and covers a product. A game item belongs to a session and has a name. In every case like this, the row you are after is "the one where both fields match."

Give findrow() a second field/value pair, and it finds exactly that row:

Show me: two-field findrow(), and why it beats a combined key
row = findrow(orders->customer$, "Acme Corp", orders->product$, "widgets")

Use this whenever a row is identified by a combination — owner and name, date and department, session and resource. If you have ever glued two values together into a lookup field, you no longer need to:

Everything you know about findrow() still applies: matching is case-insensitive unless you say otherwise, the matched row becomes the current row, and findvalue() accepts the same extra pairs when you want a value back instead of a row number.

Problem: Looking up a row by two fields used to mean inventing a combined key field (customer$ + "|" + product$), storing it on every row, rebuilding it on every change, and hoping no value ever contained the separator character. Forget one rebuild — or meet one value with a "|" in it — and lookups silently return the wrong row.

Solution: Pass additional field/value pairs directly to findrow() or findvalue(). The row must match every pair. No combined key field exists, so there is nothing to maintain and no separator to collide with.

Efficiency: Sheerpower's index already knows exactly how many rows hold each value. Given two fields, it checks both counts instantly, starts from whichever value appears in fewer rows, and walks just those few, checking the other field on each. If either value is unique — the usual case — that is one direct lookup plus one comparison. Big database engines run this same strategy, but they must estimate which side is smaller from statistics kept on the side; Sheerpower's index knows the exact counts already, so it starts on the cheaper side every time.

Takeaway: Ask for the row by the fields you actually mean. Sheerpower picks the fastest path automatically.

Simplified Lookups with FINDVALUE() and FINDVALUE$()

The findvalue() and findvalue$() functions look up a row in a cluster by searching one field, then return a specified value when that row is found.

The returned value may come from the matched row, such as emp->name$ or emp->salary, or it may be any compatible expression, variable, or constant.

// Return data from the matched row name$ = findvalue$(emp->id, myid, emp->name$, "not found") // Return a constant when the row exists message$ = findvalue$(emp->id, myid, "found it", "not found")

As a side effect, _integer is set to the matching row number. If no matching row is found, _integer is set to 0.

The current cluster row is not changed by findvalue() or findvalue$(). This means they can be safely used inside cluster iteration loops without disturbing the loop's current row.

Problem: Business programs often need to find a row by one field, then read another field from that same row. Without a helper function, this requires a findrow(), a test for zero, and a separate field reference.

Solution: findvalue() and findvalue$() combine the lookup, the not-found test, and the returned value into one expression.

Efficiency: The function uses Sheerpower's cluster lookup model directly. Any cluster field can become a key when searched, so no separate key/value table or declaration is needed.

Takeaway: Use findvalue() or findvalue$() when the real intent is: find this row, return this value, otherwise use this explicit fallback.

FINDVALUE() — Return a Numeric Value from a Matched Row

Show me: FINDVALUE() syntax, arguments, and examples

Syntax

num = findvalue(search_field, search_value, found_result, not_found_result [, nth] [, exact: true])

Description

findvalue() searches a cluster field for a matching value. If a matching row is found, Sheerpower positions the cluster to that row, evaluates found_result, and returns the resulting numeric value.

If no matching row is found, findvalue() returns not_found_result.

Use findvalue() when the value being returned is numeric.

Arguments

search_field
A cluster field designator that identifies the field to search. This is the same form accepted by findrow(), such as emp->id, emp->salary, or invoice->number.

search_value
The value to match in search_field. The search value may be numeric or string, depending on the field being searched.

found_result
The numeric value to return when a matching row is found. This may be a numeric field designator, a numeric variable, or a numeric constant.

If found_result is a cluster field designator, it is evaluated after the cluster has been positioned to the matched row.

not_found_result
The numeric value to return when no matching row is found.

This argument is required. There is no silent default.

nth
Optional. Selects which matching row to use when duplicate matches exist. 1 means the first match and is the default.

exact: true
Optional, and named: makes string matching case-sensitive. Written by name after the positional arguments, it needs no nth before it (findvalue(..., exact: true)); the older positional spelling, TRUE after an explicit nth, still works.

Returns

findvalue() returns a numeric REAL value. Both found_result and not_found_result must be numeric.

Examples

// Find an employee by name and return the employee's salary salary = findvalue(emp->name$, "Bob", emp->salary, 0)
// Return 1 if the customer exists, otherwise return 0 exists? = findvalue(customer->id, id, true, false)

FINDVALUE$() — Return a String Value from a Matched Row

Show me: FINDVALUE$() syntax, arguments, and examples

Syntax

text$ = findvalue$(search_field, search_value, found_result, not_found_result [, nth] [, exact: true])

Description

findvalue$() searches a cluster field for a matching value. If a matching row is found, Sheerpower positions the cluster to that row, evaluates found_result, and returns the resulting string value.

If no matching row is found, findvalue$() returns not_found_result.

Use findvalue$() when the value being returned is a string.

Arguments

search_field
A cluster field designator that identifies the field to search. This may be a string field, such as emp->name$, or a numeric field, such as emp->id.

search_value
The value to match in search_field. The search value is not determined by the $ on findvalue$(). The $ controls the return type, not the search type.

found_result
The string value to return when a matching row is found. This may be a string field designator, a string variable, or a string constant.

If found_result is a cluster field designator, it is evaluated after the cluster has been positioned to the matched row.

not_found_result
The string value to return when no matching row is found.

This argument is required. There is no silent default.

nth
Optional. Selects which matching row to use when duplicate matches exist. 1 means the first match and is the default.

exact: true
Optional, and named: makes string matching case-sensitive. Written by name after the positional arguments, it needs no nth before it (findvalue(..., exact: true)); the older positional spelling, TRUE after an explicit nth, still works.

Returns

findvalue$() returns a string value. Both found_result and not_found_result must be strings.

Examples

// Search a numeric field and return a string field label$ = findvalue$(emp->id, 1234, emp->label$, "?")
// Find an employee by ID and return the employee's name name$ = findvalue$(emp->id, 1234, emp->name$, "[unknown]")
// Return the SKU from the third matching order line sku$ = findvalue$(line->order_id$, ord$, line->sku$, "", 3)

The Found Result Is Evaluated at the Matched Row

Show me: how the found result is evaluated

The most important rule is that found_result is evaluated at the matched row.

Sheerpower finds the matching row, positions the cluster to that row, evaluates found_result, and then restores the original row position. After the call, the cluster's current row is exactly what it was before the call; only _integer reflects the matched row number.

salary = findvalue(emp->name$, "Bob", emp->salary, 0)

This returns Bob's salary, not the salary from the row that happened to be current before the call.

This is what makes findvalue() more than a simple existence test. It is a lookup-and-return operation.

The Not-Found Result Is Required

Show me: the required not-found result

The not_found_result argument is required for both findvalue() and findvalue$().

This forces every call site to make the not-found behavior visible. There is no hidden default and no silent assumption.

  • Use a fallback value when absence is ordinary.
  • Use 0 or "" when absence and empty mean the same thing.
// Ordinary fallback name$ = findvalue$(emp->id, id, emp->name$, "[unknown]") // Empty string is intentional sku$ = findvalue$(line->order_id$, ord$, line->sku$, "")

Simplifying the FINDROW() Pattern

Show me: replacing the findrow()+test+read pattern

A traditional lookup with findrow() looks like this:

row = findrow(emp->id, id) if row > 0 then salary = emp->salary // found the employee else salary = 0 // did not find the employee end if

With findvalue(), the same intent becomes one expression:

salary = findvalue(emp->id, id, emp->salary, 0)

The shorter form is not just more compact. It removes the row-number bookkeeping and says directly what the program means.

Problem: Repeated findrow() code makes the developer write mechanical lookup steps instead of the business intent.

Solution: findvalue() and findvalue$() express the lookup and returned value in one readable expression.

Efficiency: Because Sheerpower clusters can search on any field, this works without extra lookup tables, declarations, or special key/value conventions.

Takeaway: When you want data from the matched row, use findvalue() or findvalue$() instead of manually finding the row and testing it.

FINDVALUEBY() and FINDVALUEBY$() — Compound Versions of FINDVALUE()

Show me: FINDVALUEBY() / FINDVALUEBY$() syntax and example

findvalueby() and findvalueby$() work exactly like findvalue() and findvalue$() described above — same found and not-found results, same _integer side effect, same restored row position, same optional nth and case_sensitive arguments. The one difference: the row is found using the same two-field intersecting search that findrowby() performs, so the matched row is the one where both field/value pairs match.

Syntax

num = findvalueby (search_field1, search_value1, search_field2, search_value2, found_result, not_found_result [, nth] [, exact: true]) text$ = findvalueby$(search_field1, search_value1, search_field2, search_value2, found_result, not_found_result [, nth] [, exact: true])

Example

// The quantity of widgets on Acme's order -- one expression qty = findvalueby(orders->customer$, "Acme Corp", orders->product$, "widgets", orders->qty, 0)

Summary

findvalue() returns a numeric value from a matched row. findvalue$() returns a string value from a matched row.

findvalueby() and findvalueby$() do the same, but find the row with findrowby()'s two-field intersecting search — the matched row is the one where both field/value pairs match.

The search fields may be numeric or string. The $ suffix on findvalue$() and findvalueby$() describes the returned value, not the searched values.

The found result is evaluated after the matching row is found and the cluster is positioned to that row.

The not-found result is required, so every lookup clearly states what happens when the row is absent.

(Show/Hide Sheerpower FINDVALUE() and FINDVALUEBY() Takeaways)
Summary:
  • findrow() is a core language feature for fast, predictable 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 rules 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.