formulafillallfields statement

formulafillallfields

FORMULA

The formulafillallfields statement fills every visible cell in all fields with the result of the specified formula.

Parameters

This statement has one parameter:

formula – generates the new data. This formula must generate a tab delimited line of text, with one tab column per field. This formula usually uses the exportline() function to start with the existing data, then modifies it.

Description

This statement is similar to the formulafill statement, but instead of just replacing the contents of the current field, it replaces the contents of all fields. All of them! Needless to say, incorrect use of this statement can destroy all of the data in your database, so use this statement carefully.

The formula you provide to this statement must generate a tab delimited line of text. The generated text is imported into each record, just as if it was imported using the importtext statement. The existing contents of the record are destroyed, so usually the formula is constructed to export the data in the record and modify it somehow.

This example converts all of the text in the database to upper case.

formulafillallfields upper(exportline())

This example changes the name Mary Wilson to Mary August. All occurences of her name are changed, in any and all fields.

formulafillallfields replacewordexact(exportline(),"Mary Smith","Mary August")

This example shifts data over two fields to the right. All fields from the third on are shifted, the first two are left alone. (This technique works best when all the shifted fields are the same type, otherwise you may lose data. You will also lose data that is in the last two columns, so be sure to add extra columns if needed before using the formulafillallfields statement)

formulafillallfields arrayinsert(exportline(),3,2,tab())

Be very careful not to unintentionally disturb the tabs when processing the text. For example, you might try using this formula to strip all non-alphabetic characters from your database.

formulafillallfields striptoalpha(exportline()) ☞ WRONG!

Problem is, this will also strip out the tabs, so all of the data will wind up in the first field. Here is a corrected example, which also allows spaces.

formulafillallfields stripchar(exportline(),tab()+tab()+"  AZaz")

To make the formulafillallfields statement undoable, add a startdatabasechange statement before it, like this.

startdatabasechange "AllRecords","Shift Fields Left"
formulafillallfields arraydelete(exportline(),5,2,tab())

Note: When combined with the fillall statement, the formulafillallfields statement can modify invisible records as well as visible records. See the FillAll statement for details.

See Also

change -- replaces text with new text in the current field (selected records only).
changeword -- replaces one word with another in the current field (selected records only).
changewordexact -- replaces one word with another in the current field (selected records only).
emptyfill -- fills every empty visible cell in the active field with the specified value.
fill -- fills every visible cell in the active field with the specified value.
fillall -- modifies the operation of the following *fill* statement so that all records are modified, not just visible records.
formulafill -- fills every visible cell in the active field with the result of the specified formula.
outlinelevel -- expands or collapses the entire database to show a specific level of data or summary level.
propagate -- fills all the empty cells in the current field. Each empty cell is filled with the value of the first non-empty cell above it.
propagateup -- fills all the empty cells in the current field. Each empty cell is filled with the value of the first non-empty cell below it.
resetallrecordidts -- resets the internal record id and time stamps for every record in the current database.
runmodifyfill -- runs the .ModifyFill procedure (if it exists).
runningdifference -- calculates the running difference for the current field.
runningtotal -- calculates the running total for the current field.
sequence -- fills every visible cell in the current field with a numeric sequence.
slidedata -- slides the columns at and to the right of the current column.
speedcopy -- copies multiple adjacent fields from a record in one database to a record in another.
summarylevel -- changes the summary level of the current line.
unpropagate -- removes repeating data from the currently selected field. If several records in a row contain the same data, this statement erases all but the first (top) value.
unpropagateup -- removes repeating data from the currently selected field. If several records in a row contain the same data, this statement erases all but the last (bottom) value.

History

Version	Status	Notes
10.0	New	New in this version.