The LRKeep function keeps the values in a data entry column on the correct rows when the adjacent query is refreshed, while also enabling adjacent formulas to fill down.
Syntax
LRKeep( target , default formula , Key1 , [...keyn] )
The LRKeep function syntax has these parts:
| Value | Description |
|---|---|
| target | Required. This argument identifies the data entry value to be retained. |
| default formula | Required. This argument specifies the function to be used to initialize data entry cells in new rows. |
| key 1 ...key n | Required. The combination of key values uniquely identifies the row where the data entry value belongs. |
Default Formula
The default formula argument is used to initialize cells in the data entry column when new rows appear in the query. The most common values are a zero for numeric columns and a blank for text columns, but you can enter any valid formula.
The reason for requiring a formula versus a static value has to do with Excel’s behavior for filling down adjacent functions. Excel fills down formulas in columns from left to right for as long as it keeps encountering formulas. If you type static text in a column, then formulas in the columns to the right will not fill down when the query is refreshed.
To overcome this limitation, Liberty Reports inserts default formula into the data entry column prior to refreshing the data, and then restores the data entry values to the correct rows after the refresh completes. Any new rows that appear will still contain default formula.
Liberty Reports expects default formula to match the format used in the Excel Formula Bar, starting with the equal sign. Since default formula is a text argument, it must be enclosed in double quotes. A typical default formula for a numeric data entry column is "=0".
Text formulas can be tricky because of technicalities involving double quotes inside of double quotes. For this reason, Liberty Reports supports a convenient shortcut to represent a formula for entering blank text: omit the equal sign and simply enter two double quotes, "".