Looking up Data Directly from the Server

Panorama has a several functions that “reach into” another database to extract specific information, including lookup(, superlookup(, and lookupall(. See Linking with Another Database to learn more about these functions.

Whether you are using a single user database or a shared database, all of these lookup functions work by “reaching into” the local copy of the requested database – the copy on your computer. So this is the same data that you see when you look at the data with the data sheet or a form.

Of course if other users are making changes to the database (including web users), the data in local memory on your computer may not be 100% up-to-date. Panorama has a synchronization process to take care of this (see Shared Database Synchronization and Record Locking and Editing Shared Data), but if you want to make sure you are looking up the most absolute up-to-date information, regardless of synchronization, Panorama has special functions that let you do this: serverlookup(, serversuperlookup( and serverlookupall(. These functions are nearly identical to their regular counterparts, but instead of reaching into local memory, they reach across the network to the server.

### The Database Parameter

The first parameter of each of these functions is the database to “reach” into. This must be a shared database that is currently open on your computer and connected to the server. Even though these functions do not actually access the data that is in your local memory, they still require that the database be open on your computer. You cannot directly access a server database that is not actually open on your computer.

If the specified database is `""`, the current database is used. In other words, you can “reach into” the current database. Even though this is the database you currently have active on your computer, these functions will contact the server to access the data, bypassing your local in-memory copy. After all, even though this is your currently active database, other users may have made changes that you haven’t synchronized yet.

### Server Lookups Should Be Minimized

Due to the delay of accessing information over a network, the server lookup functions are thousands of times slower than their normal, in-memory counterparts. You should avoid using the server lookup functions unless you absolutely must have the most up-to-date information. If at all possible, it’s better to synchronize the data and then use a regular lookup.

You should NEVER use these function in a statement like formulafill, select, or any situation where they will be invoked repeatedly for each record. If you try, you’ll most likely find that your computer hangs up with a beachball. So don’t do that!

### The ServerLookup( Function

The serverlookup( function is almost identical to the normal lookup( function, except that it looks up the data from the server instead of from your local memory. Other than that, the parameters and operation are identical.

One difference, however, is that the lookupmore( function does not work after a serverlookup( function. When looking up data from the server, each lookup must be done separately (unfortunately, incurring additional delays in the process).

Another difference is that there are no variations on the serverlookup( function for looking up only selected records, looking up the last match instead of the first, etc. These variations are impossible because the server copy of the database doesn’t have any unselected records and may be in a different order than your copy of the database (so that searching in reverse doesn’t make any sense). If you want to use these variations you must use the normal, in-memory versions of these functions (see lookupselected( and lookuplast().

### The ServerSuperLookup( Function

The serversuperlookup( function is almost identical to the normal superlookup( function, except that it looks up the data from the server instead of from your local memory.

However, the serversuperlookup( function does not allow options to be specified to restrict to only selected records, control the search direction, etc. These options are impossible because the server copy of the database doesn’t have any unselected records and may be in a different order than your copy of the database (so that searching in reverse doesn’t make any sense). If you want to use these options you must use the normal superlookup( function.

### The ServerLookupAll( Function

The serverslookupall( function is almost identical to the normal lookupall( function, except that it looks up the data from the server instead of from your local memory.

One other difference is that the serverslookupall( function only allows one data field. The regular lookupall( function allows you to specify multiple data fields and a separator, but this option isn’t available when looking up from the server.

You also need to keep in mind that the order of the data on the server may be different than the order of the data on your local computer. There is no way to control the data order on the server.

### Panorama X vs. Panorama 6 Server Lookup Operation

If you’re converting a Panorama 6 database that uses server lookups, Panorama X works differently and you’ll need to make adjustments to your code and possibly forms.

In Panorama 6, the lookup( function would automatically lookup from the server unless you explicitly instructed it not to do so.

In a Panorama 6 procedure, you could disable server lookups by using the `serverlookup "off"` statement, then turn it back on again with the `serverlookup "on"` statement. In Panorama X the `serverlookup` statement doesn’t exist, so you’ll have to remove it from your code. Instead you should convert any `lookup(` functions to `serverlookup(` functions if you want then to look up from the server instead of local memory.

In a Panorama 6 form, a form preference option allowed you to enable or disable whether or not `lookup(` functions on that form performed server lookups. This option did not allow control of how individual lookups worked – either the entire form used server lookup or the entire form used in-memory lookup – no mix and match. Panorama X does not have this form preference option. Instead, you can use either the lookup( or serverlookup( function depending on what is required for each form object. This gives you full flexibility to mix and match these options on a single form. Of course you can also use the new serverlookupall( and serversuperlookup( options in a form object. When using any of these objects in a form, just keep in mind that there is a significant performance penalty for doing a server lookup instead of a normal in-memory lookup.

See Also

- Bulk Data Transformations on Shared Databases -- using formulafill, propagate, etc. in a shared environment.
- Controlling and Monitoring the Server Connection -- overriding automatic server connection behavior.
- Record Locking in Procedure Code -- implicit and explicit management of record locking.
- Server Variables (Shared Variables) -- sharing variable values across multiple users.
- Synchronizing in Procedure Code -- customizing the data synchronization process.

History

Version | Status | Notes
10.2 | New | New in this version.