Formatted searches, fields that fill themselves

A formatted search fills a field on the screen with the result of a query. It runs by itself when another field changes, or on demand with Shift+F2 inside the field. For example:

• When the series is picked, the comment gets that series' fixed text.
• When the customer is picked, the reference takes their vendor number.
• When the total changes, a user field says whether it needs approval.

It is SAP Business One's formatted search and Nori desktop's control events. Desktop ones are not carried over by the migration, because they used Windows control names and SQL Server queries; they are set up again here.

They are set up by whoever has the formatted searches permission. The Administrator has it.

Create a formatted search

1. Go to Administration › Customization › Formatted searches and press New.
2. Give it a Name that says what it does.
3. In Screen choose where it applies: Every document, one document type (for example Document: Invoice) or a catalog (Catalog: Items).
4. Below appear the Fields of the screen, split as they show: header, each tab, lines and footer. On the field you want to fill press Fill.
5. On each field that should trigger it press On change. With none, it only runs with Shift+F2.
6. Check Keep what was typed so it does not change a field the user already filled.
7. Write the Query.
8. Check the sentence at the top: it says in words what it will do, for example "On Invoice, when the user changes Series, Nori will run the query and put the result in Comments".
9. In Try it type sample values for the fields the query reads and press Try it: it tells you what the field would get, without saving anything. When the result is what you expect, save.

Runs when changing is when it runs by itself: every time the user changes one of those fields on the screen. Left empty, it only runs when the user presses Shift+F2 inside the field it fills.

The query

It is a SELECT. The first value of the first row fills the field; with no rows the field is left empty.

The query reads the screen's fields with a colon and the field name:

SELECT 'Coffee shop sale ' || name
FROM document_series
WHERE id = :document_series_id

• A user field udf[U_ROUTE] is read as :u_route.
• A line field is read with line_ in front: :line_quantity, :line_item_id. It is the line that changed.
• Under each field of the list is how to read it.
• On documents, :total is the document total.
• :user_id is the user entering it.

It only reads: it cannot change data or see users, passwords or settings, and it has a time limit. These are the same rules as SQL reports.

Field names on documents

You don't need to learn them: the Fields of the screen list puts them in for you. For reference, the header ones:

On a catalog the name is the field's database name (name, code, item_group_id…). A user field is always udf[CODE].

How it behaves

• When a field is filled, its own searches run too (up to five in a row).
• If the field it fills is a lookup (customer, sales employee…), the query returns the id and the screen shows the name.
• On lines, the search runs when a line field changes or an item is added, and fills that same line.

If it does not work

• Nothing happens when the field changes: check the name in Runs when changing is exact and the search is Active.
• An error message appears: the query has an error; the message says which. Try it first in Reports › Report writer.
• Shift+F2 says the field has no search: Field it fills does not match the name of the field you are in.
• The line's item is not filled: the item can only trigger searches, not be filled by one.