Workbooks
Bulk changes with a formula
Change many Business Central records at once: fill one formula down a data table, let Business Central check every row, then apply the changes that pass.
updated: applies-to: 1.0.0.0
When one rule changes many records, write the rule once as a formula, fill it down a data table, and let Business Central check every row before anything is saved. The example below raises the price of one supplier's chairs after a 6% cost increase.
Bring the records into a sheet
- In a workbook, insert a data table on the Item table with No., Description, Unit Cost, Unit Price and Profit %.
- Add a filter, here Vendor No.
40000, so only that supplier's items come in. - Select Allow changes to be written back to Business Central (after a review) and choose Insert.
The header menu of each column sorts the rows and filters them by value or with Business Central filter syntax.
The steps for data tables and write-back are in Live table editing.
When Business Central says no
Business Central's own rules still apply. Type a new Unit Cost for an item that has ledger entries, choose Review changes and then Check changes, and the change is refused: "You cannot change Unit Cost because there are one or more ledger entries for this item."
Choose Undo this change, and the cell is back to the value in Business Central. Nothing was written.
Write one formula and fill it down
- On the Formulas tab, the Function Library lists every function by category. From Math & Trig, choose ROUND.
- In the first Unit Price cell, enter the rule: the unit cost plus 6%, times the markup, rounded to cents, for example
=ROUND(D5*1.06*1.3,2). - Select the column and fill the formula down with Ctrl+D. Each row becomes a pending change, and nothing is written yet.
The result of each formula is what gets written. If a formula gives an error, such as a division by zero, only that change is refused.
Check every change
Choose Check changes again. Every record runs Business Central's validation and is rolled back, and each change shows what else it changes, such as Profit %.
Apply, and keep a colleague's edit
Choose Apply. Each record is written on its own. If someone changed a record after you loaded the table, that change is refused with "Changed by someone else: refresh the data table" and the new value, so their edit is never overwritten. The other records are still written.
A refused change stays pending, highlighted, until you decide what to do with it. If one change of a record fails, that record's other changes are skipped, and a record that was deleted is reported. Discard drops whatever is left.
To read the new values, open the menu on the data table's title bar and choose Refresh this live data.
Every change is logged
The Studio Audit Log keeps each apply. Field Changes, filtered here on Unit Price, shows the old and new value of every record.
When to use it
Use it when one rule changes many records: a supplier's price rise, a new markup, or one field corrected across a range of records.