Most data operations occur on individual data cells and records. However, Panorama X also includes powerful operations that can rapidly modify huge swaths across an entire database (see Morph Field Dialog. Morph All Fields Dialog and related topics). When working with a single user database these operations are extremely fast (thanks to Panorama’s in-memory architecture) and are fully undo-able. When working with a shared database, you need to be more judicious about using these operations. Since the data changes must be shared across a network (or even over the internet) they will be much slower. In fact, operations that modify multiple fields (Morph All Fields Dialog , Shifting Data Left & Right) aren’t available at all when using a shared database.
You also need to keep in mind that since changes are immediately transmitted to other users, the Undo command is not available for these operations when performing bulk data modifications on a shared database. Instead, Panorama will normally warn you before performing these operations (though you can customize this using the Warnings panel of the Preferences dialog).
When a bulk data transformation is performed, Panorama keeps track of which data cells are actually changed, and what the new values are. It then transmits this information to the server. To optimize performance, only information that has actually changed is transmitted. As other users synchronize with the server, the updated information will be transmitted to them as well, propagating the changes across the network.
As an example, consider this procedure from an invoice database. The procedure re-calculates how much is owed on each invoice.
field Balance
formulafill Total-Payments
Suppose your database contains 1,000 invoices, and since the last time you calculated the balances, 7 payments have come in and been recorded in the database. In that case, the calculated balance will only change for 7 records, the other 993 records will have unchanged balances. So Panorama will only transmit those 7 new balances to the server for synchronization. That’s a lot better than having to send 1,000 records to the server!
When you perform a transformation based on a calculation like this, the calculation is performed based on the data on the local computer. To make sure that the data isn’t stale, you probably want to synchronize before performing the transformation, like this:
synchronize
field Balance
formulafill Total-Payments
When performing bulk modification operations, Panorama X ignores record locking. For example, suppose that some other user is currently editing an invoice when you run the code above, and that that invoice had a recent payment. Even though the other user is editing the record, the Balance will be recalculated. However, when the other user finishes editing the record, the Balance for that record will change back to the value it had when that user started editing. In this case it’s not a big deal because you can simply run the balance calculation code again later.
If you have an application where record locking must be honored, you cannot use any bulk transformation operation. Instead, you need to perform the operation one record at a time, like this:
synchronize
select Balance<>Total-Paments
loop
lockrecord
if error
nsnotify "Balance Calculation Skipped",
"TEXT","Invoice "+InvoiceNumber+
" skipped because it was locked by another user"
else
Balance = Total-Payments
endif
downrecord // this automatically unlocks
until stopped
See Record Locking in Procedure Code to learn more about coordinating record locking in your code.
When working with a single user database, it’s usually no problem to modify every record in the entire database. RAM memory is so fast that even tens of thousands of records can be modified almost instantly. When working with a shared database, however, these operations are much slower because of the need to synchronize the changes to every other user of the database.
For example, consider this code which increases the price of every item in the database by 25%.
synchronize
field Price
formulafill Price*1.25
If the database contains 5,000 items, then 5,000 new prices have to be transmitted to the server. Then later, all 5,000 records must be downloaded from the server to every other user’s computer. Because Panorama X synchronizes data on the record level, not the field level, the entire contents of these 5,000 records must be downloaded, not just the prices.
If you really need to change every record of the database, there is no way to bypass this synchronization overhead. If you have multiple mass changes to be made, you might want to consider temporarily switching the database to single user, then making the bulk changes, then re-sharing it. This could actually be faster and more foolproof than performing a bulk data modification on the entire database.
Keep in mind that Panorama itself will do what it can to minimize the amount of data that needs to be synchronized. For example, consider this code which reduces the prices of items that have more than 500 units remaining in stock.
synchronize
field Price
formulafill ?(Inventory>500,Price*0.75,Price)
Even though this formulafill statement recalculates every record in the database, the price will only change for items with more than 500 items in stock. Only those records will be sent to the server and synchronized to other users. Any item with 500 or less items in inventory will be unaffected, and will not be synchronized. Not only does this reduce the time needed for synchronizing, it also reduces the chance that some other user is currently editing one of the updated records.
Panorama has an alternate method for modifying multiple cells in a field, the serverformulafill statement. There are two differences between this and a regular formulafill statement. First, it allows you to specify a selection formula so that the changes are made only to the specified records. This eliminates the need for a separate select statement.
The second difference is that this statement does not transmit the list of changed data to the server. Instead, it transmits the formula for calculating the new values to the server. The calculations are then performed again on the data in the server. This is potentially faster, since the formula is usually quite short, so is much faster to transmit to the server than all of the modified data values. However, the changed data values will still have to be synchronized later to all of the other computers, so in the long run the speed difference may not be significant.
The previous section showed an example of reducing the price of all items with more than 500 units remaining in stock. This example can be rewritten with the serverformulafill statement like this. Notice that the synchronize statement is not necessary, because the calculation will be performed on both the local computer and on the server (which will always have the most up-to-date data). (You can, however, include the synchronize statement if you wish.)
field Price
serverformulafill Inventory>500,Price*0.75
If you used the serverformulafill statement in earlier versions of Panorama (Panorama 6 or earlier), you may remember that in those versions the formulas used by this statement were not allowed to contain variables. This restriction has been eliminated from Panorama X. So in Panorama X the code below will work fine, even though it would not work in Panorama 6.
let threshold = 500
let discount = 25
field Price
serverformulafill Inventory>threshold,Price*(1-(discount/100))
If you previously used shared databases in Panorama 6, bulk data transformations work a bit differently now than in that earlier version. You may need to make modifications to your existing code to accommodate these changes.
The most important change is that Panorama X checks each cell to see if it actually changed. If a cell didn’t change, Panorama X doesn’t upload the cell to the server, which means that it doesn’t have to be synchronized to other user’s computers. In many situations a bulk modification operation may not actually change much data, in other words, most of the data was already set to the correct value. When using Panorama 6 this didn’t matter, it would incur the overhead of modifying every cell in the field even if only a few cells actually changed value. Panorama X is much, much faster in these situations, both for the initial bulk modification operation, and for subsequent synchronization operations to other computers.
The one caveat here is that it’s very important that the local database contains up-to-date information before starting the bulk modification operation. If it’s not accurate, Panorama may not correctly determine if a cell has been modified or not. Fortunately, all you need to do is make sure that a synchronize statement is included just before the bulk modification, like this:
synchronize
field Balance
formulafill Total-Payments
Another difference is that unlike Panorama 6, Panorama X doesn’t lock each record as the data is modified. See Bulk Data Transformations Aren’t Record Locked to learn more about this. If your existing code was using if error to check to see if a record lock had failed, you’ll need to rewrite that code for Panorama X.
See Also
History
| Version | Status | Notes |
| 10.2 | New | New in this version. |