Skip to content
Bitta Apps

Chapter 2 of 4Page 3 of 12

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

  1. In a workbook, insert a data table on the Item table with No., Description, Unit Cost, Unit Price and Profit %.
  2. Add a filter, here Vendor No. 40000, so only that supplier's items come in.
  3. Select Allow changes to be written back to Business Central (after a review) and choose Insert.
Insert data table dialog on the Item table filtered on Vendor No. 40000

The header menu of each column sorts the rows and filters them by value or with Business Central filter syntax.

Item data table of ten chairs with the Description header menu open for sorting and filtering

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."

Review changes after Check changes with a typed Unit Cost refused because the item has ledger entries

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

  1. On the Formulas tab, the Function Library lists every function by category. From Math & Trig, choose ROUND.
  2. 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).
  3. Select the column and fill the formula down with Ctrl+D. Each row becomes a pending change, and nothing is written yet.
ROUND formula typed in the first Unit Price cell of the chair data table, from the Formulas tab

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 %.

Ten new unit prices filled down and checked, each with the formula and Also changes 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.

Applied 9 of 10 changes, with one change refused because someone else changed the item after the table was loaded

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.

Studio Audit Field Changes filtered on Unit Price with the old and new price of each chair

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.

What's next

Build a budget in a sheet and write it back.

Next step

Ready to try Bitta Sheets?