Difference between revisions of "KEYLOOKUP()"

From Lianjapedia
Jump to: navigation, search
m (Reverted edits by Yvonne.milne (talk) to last revision by Barrymavin)
(One intermediate revision by the same user not shown)
Line 3: Line 3:
  
 
==Syntax==
 
==Syntax==
KEYLOOKUP(<workarea> | <"alias"> | <"table">, <"indextagname">, <keyExpr>, <resultExpr> [, <notfoundExpr>])
+
KEYLOOKUP(<workarea | alias | table>, <"indextagname">, <keyExpr>, <resultExpr> [, <notfoundExpr>])
  
 
==See Also==
 
==See Also==
[[Data Mapping]], [[LOOKUP()]], [[RLOOKUP()]], [[SEEK()]], [[SET RELATION]], [[SQLLOOKUP()]]
+
[[LOOKUP()]], [[RLOOKUP()]], [[SEEK()]], [[SET RELATION]], [[SQLLOOKUP()]]
  
 
==Description==
 
==Description==
Line 23: Line 23:
 
It can also be used to lookup NoSQL key/value pairs.
 
It can also be used to lookup NoSQL key/value pairs.
  
See [[Data Mapping]] for more information on two way mapping using lookup tables.
+
You should always provide a fully qualified database!table if your app is to work in Web/Mobile e.g "southwind!employees".
 +
 
 +
If you use this function in Web/Mobile clients you should always specify the resultExpr for keyLookup() as a string prefixed with ? so that the parameter can be proxied correctly back to the server without throwing a javascript error on the client.  e.g. "?lastname".
  
 
Note: for Virtual Tables, see [[SQLLOOKUP()]].
 
Note: for Virtual Tables, see [[SQLLOOKUP()]].
Line 32: Line 34:
 
use orders in 0
 
use orders in 0
 
? keylookup("employees","employeeid",orders.employeeid, lastname, "not found")
 
? keylookup("employees","employeeid",orders.employeeid, lastname, "not found")
lcTable = "employees"
 
? keylookup("&lcTable","employeeid",orders.employeeid, lastname, "not found")
 
use employees alias emp in 2
 
? keylookup("emp","employeeid",orders.employeeid, lastname, "not found")
 
? keylookup(2,"employeeid",orders.employeeid, lastname, "not found")
 
 
</code>
 
</code>
  

Revision as of 11:47, 10 July 2019

Purpose

Function to perform a cross-table lookup

Syntax

KEYLOOKUP(<workarea | alias | table>, <"indextagname">, <keyExpr>, <resultExpr> [, <notfoundExpr>])

See Also

LOOKUP(), RLOOKUP(), SEEK(), SET RELATION, SQLLOOKUP()

Description

The KEYLOOKUP() function looks up the specified index <keyExpr> in the index <indextagname> of the specified <workarea | alias | table>.

The KEYLOOKUP() function then evaluates the expression <valueExpr>, and returns the value of the <resultExpr> if the <key expression> is found.

The KEYLOOKUP() function returns a null string if the <key expression> is not found or the <notfoundExpr>, if specified.

The current record position in the specified <workarea | alias | table> cursor is not moved so this function can also safely be used in SQL queries.

If the specified table is not already open then Lianja will open the table automatically.

This function can be used to dynamically lookup foreign key values in target tables.

It can also be used to lookup NoSQL key/value pairs.

You should always provide a fully qualified database!table if your app is to work in Web/Mobile e.g "southwind!employees".

If you use this function in Web/Mobile clients you should always specify the resultExpr for keyLookup() as a string prefixed with ? so that the parameter can be proxied correctly back to the server without throwing a javascript error on the client. e.g. "?lastname".

Note: for Virtual Tables, see SQLLOOKUP().

Example

open database southwind
use orders in 0
? keylookup("employees","employeeid",orders.employeeid, lastname, "not found")