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


History

VersionStatusNotes
10.0NewNew in this version.