How to mass update records in SyteLine without SQL: Replace Value in Collection

Why not just run an UPDATE in SQL?

Because SQL skips everything SyteLine does when a record is saved. Changes made through a form go through the IDO layer, with its validations, triggers and event handlers. Infor's own sequence for Replace Value in Collection runs the form's standard validation events for the changed records, exactly as if someone had typed the value in.

A direct UPDATE on item_mst does none of that. That's how codes end up pointing at things that don't exist, or how notifications and audit trails get silently skipped. The rule from the SQL table cheat sheet stands: read with SQL, change through SyteLine.

Step by step

1. Open the maintenance form, for example Items, Customers or Vendors.
2. Filter to exactly the records you want to change. Use filter-in-place with criteria tight enough that every record shown should change.
3. Check the count. If more records match than the form can hold, raise your cap first (see below).
4. Export a "before" copy. Send the grid to Excel so you have the old values if anything goes wrong.
5. Select the first record and put the cursor in the field you want to change.
6. Choose Edit > Replace Value in Collection. The Replace dialog opens with the collection and field already chosen.
7. Fill in the dialog: - Replace: the value to look for, or - All Values: tick this to change the field on every record regardless of its current value. This disables Replace. On a tight filter, it's usually the safest choice. - With: the new value. - In collection / In property: already set from where your cursor was. Check them. - Case-sensitive: only match the exact capitalisation you typed.
8. Click OK. SyteLine asks about each match: Yes (this one), No (skip), Cancel (stop), or Yes to All.
9. Save the form as you normally would, and deal with any record SyteLine refuses.
10. Check the result (see below).

Trap 1: the 200-record cap

A form doesn't load every matching record. It loads up to its cap, which is 200 by default. Replace Value in Collection only works on what's loaded. Filter 1,500 items, run the replace, and only the first 200 change. Nothing tells you about the other 1,300.

To deal with it:

• Raise your Data Record Cap in User Preferences. You can set a higher number, or retrieve all available records, up to the system maximum.
• The system maximum is the WinStudio Max Record Cap on the Process Defaults form, set by an administrator.
• Or work in batches: filter to 200 or fewer at a time, for example by product code range, and repeat.

Large collections take time to load and validate, so batches are often the more comfortable route anyway.

Depending on your administrator's settings, a raised cap may last only for the current session, or carry on into future sessions. Put it back afterwards so everyday forms stay quick.

Trap 2: partial matches

The Replace box uses an implied wildcard search. Replacing AB can also match AB1, CAB or ABX, depending on the data. It's very easy to change more than you intended.

Protect yourself by:

• filtering tightly first, then using All Values, so the filter, not the search text, decides what changes
• answering Yes / No record by record on the first few, to see what it's matching, before you use Yes to All

Trap 3: forward only

The search starts at the selected record and moves forward. It doesn't wrap back to the top. Start on the first record, or rows above your cursor are silently left out.

When a record won't save

Because the change goes through normal validation, some records may be refused, for example when the new value isn't valid for that record, or the record's status doesn't allow the change. That's the system protecting you. Read the message, fix or exclude that record, and save the rest. Don't "solve" refusals with SQL. They're telling you the change isn't valid for that record.

Check the result

In the form: filter on the new value and compare the count with your "before" export.

With read-only SQL (on-premises): an example written for this article. Check the column names against your version, and test on a copy first:

-- how many items now have the new product code, and when were they changed (read-only)
SELECT  item, description, product_code, RecordDate
FROM    item_mst
WHERE   site_ref = 'MAIN'            -- your site
  AND   product_code = 'NEWCODE'     -- the new value
ORDER BY RecordDate DESC;

RecordDate records when each row was last changed, so a block of rows with today's time is your update.

When each row needs a different value

Replace Value in Collection sets one value across many records. When each row needs its own value, such as new prices per item, prepare the rows in Excel and use Paste Rows Overwrite instead.

When this isn't the right tool

• Thousands of records, or different values per record. One value across a filtered set is what Replace Value in Collection is for. "Each item gets its own new cost" is a data import or a small development job, done through SyteLine's import tools or an IDO-based script, not a hand edit.
• Fields the form doesn't let you edit. If a field is read-only on the form, there's usually a reason. Find the form or utility that owns it.
• Anything financial or transactional, such as posted transactions, balances or quantities on hand. These have their own utilities and audit rules. Don't mass-replace them.

Good habits

• Change one record by hand first. If that saves cleanly, the bulk change probably will too.
• Export before, check after.
• Do it at a quiet time for large batches, so you're not saving hundreds of records while people are working on them.
• Write it down: what changed, how many records, when, and who asked. Future you will want to know.

FAQ

How do I update many records at once in SyteLine?

Filter the maintenance form to the records you want, put the cursor in the field, choose Edit > Replace Value in Collection, enter the new value, confirm with Yes to All, then save.

Why did Replace Value in Collection only change 200 records?

The form only loaded 200 records, the default cap. Raise your Data Record Cap in User Preferences, up to the WinStudio Max Record Cap, or work in batches.

Can I mass update SyteLine data with SQL instead?

You shouldn't. Direct SQL updates skip SyteLine's validations, triggers and event handlers. Use the form, an import tool, or an IDO-based process.

Why did Replace Value change records I didn't expect?

The Replace box uses implied wildcards, so it can match partial values. Filter tightly and use All Values instead.