Search & Replace replacement formulas are used to change prices or any other kind of numeric data when using the Search & Replace function.
To use Replacement Formulas:
- Select the appropriate database.
***Always make a Backup copy of the database files before using the Search & Replace function.***
- Select Search & Replace from the Find menu to display the Search screen (the first of two screens used by the Search & Replace function). The Search screen looks like a Database record, but all of the fields are blank and the word "Search" is displayed in the lower right corner of the screen.
- Enter the Search criteria in the appropriate fields. Search Operators may be used with the search criteria. Search criteria may be entered in as many fields as necessary.
- Press [F10] or [PageDown] to display the Replace screen (the second of two screens used by the Search & Replace function). The Replace screen looks like a Database record, but all of the fields are blank and the word "Replace" is displayed in the lower right corner of the screen.
- Enter the Replacement Formula(s) into any field or combination of fields (including fields that contained the Search criteria if necessary).
- Press [F10] or [PageDown] to begin the Search.
- When the Search locates the first record that matches all of the Search criteria, a prompt is displayed asking to Confirm changes to each record. Yes is the recommended answer. The first record matching the Search criteria is displayed and the "Update this record (Yes/No/All)" prompt is displayed at lower left corner of the screen.
- Select Yes to update the record and continue to the next record that satisfies the Search criteria.
- Select No to not update the record and continue to the next record that satisfies the Search criteria.
- Select All to update the record and continue updating all the records that satisfy the Search criteria without prompting for confirmation.
Note: This function can wipe out much or even all database record information.
Replacement Formula Format
Replacement Formulas must either begin with a "+", "*", or "/" character or contain one of the Cost/Price arguments (see the argument list below).
When an operator follows an argument it uses that argument when performing the calculation. For example, "A*2" means "Average Cost * 2".
When an operator follows the "+" character, or stands alone, it uses the contents of the current field when performing the calculation. For example, "+*2" means "Current field * 2".
Operator / Operation:
+ / Addition - +5 adds 5 to the Price or Quantity. Current field + (followed by a Formula) applies the Formula to the contents of the current field.
+- / Subtraction - a + or Price/Cost argument is required. +-5 subtracts 5 from the Price or Quantity. A-5 subtracts 5 from the Average Cost and places the result in the current field.
* / Multiplication - a + or Price/Cost argument optional. *5 multiplies the Price or Quantity by 5. +*5 multiplies the Price or Quantity by 5. A*5 multiplies the Average Cost by 5 and places the result in the current field.
/ / Division - a + or Price/Cost argument optional. /5 divides the Price or Quantity by 5. +/5 divides the Price or Quantity by 5. A/5 divides the Average Cost by 5 and places the result in the current field.
+^ / To the power of - a + or Price/Cost argument is required. +^5 raises the Price or Quantity to the power of 5. A^5 raises the Average Cost to the power of 5 and places the result in the current field.
() / Operation priority - *2(+5) adds 5 to the Price or Quantity, then multiplies it by 2. Compare with: *2+5 (without parentheses) multiplies the Price or Quantity by 2, then adds 5 to it.
+M / Profit margin calculation - a + or Price/Cost argument is required. The formula is New value=Current value/(1-%). +M50 changes Price to produce a profit margin of 50% over the current Price. AM50 changes Price to produce a profit margin of 50% over the Average Cost.
Toggle Fields / Operation:
T True / Turn On toggle field
F False / Turn Off toggle field
1 True / Turn On toggle field
0 False / Turn Off toggle field
X True / Turn On toggle field
~ False / Turn Off toggle field
Argument / Value:
A / Average Cost - A+5 replaces the Price with the Average Cost + $5.00
C / Last Cost - C+5 replaces the Price with the Last Cost + $5.00.
P / Base Price - P+5 replaces the Price with the Base Price + $5.00.
S / Sale Price - S+5 replaces the Price with the Sale Price + $5.00.
L / List Price - L+5 replaces the Price with the List Price + $5.00.
X / Price Level 1 - X+5 replaces the Price with the Level 1 Price + $5.00.
Y / Price Level 2 - Y+5 replaces the Price with the Level 2 Price + $5.00.
Z / Price Level 3 - Z+5 replaces the Price with the Level 3 Price + $5.00.
See:
Inventory Profit Margins
Database Manager Contents