Custom Fields Screen

Summary

The Custom Fields screen lets an administrator add their own attributes to StockIQ records, for things StockIQ has no built-in field for, such as a brand, a machine group, a customer's on-hand quantity, or a "do not ship before" date.

It is accessible by clicking Admin > System Configuration > Custom Fields. You need the Edit Custom Field Settings permission to see it, and the Edit Custom Field Values permission to upload or edit values.

Custom fields can be added to six kinds of record: Item, Item-Site, Item-Site-Supplier, Supplier, Sales Order (line), and Supply Order Line. Each field shows up as an optional column named after its code, as CF_<code> in exports and the API, and with its name as the caption in grids.

There are two kinds of custom field:

• Stored- Values come from your ERP feed, a spreadsheet upload, or a user typing them into a detail panel. A value a user enters or uploads is never overwritten or removed by a later ERP import.
• Calculated- If Calculated From has a formula, the value is worked out each time it is displayed, for example {Cost} - {Discount} or {DueDate} - 7. A calculated field holds no values of its own, so it can't be typed in, uploaded, or imported.

When New Fields and Values Show Up

A new field does not need a refresh to appear, but its column starts out hidden, and how soon its values appear depends on where they come from.

• The field itself- As soon as you save it, the field is available on every screen that shows its kind of record (see Where Custom Fields Appear below). A screen that is already open picks it up the next time you open or reload that screen. On most grids the column is hidden at first: add it from the grid's Column Chooser, and save your view if you want to keep it.
• Uploaded values- Values from Upload Values are saved straight away. Reload the screen you are looking at to see them.
• Values typed into a detail panel- Saved straight away, and shown the next time the screen loads its data.
• Values from your ERP- Arrive with the next data refresh (usually overnight), and only after the matching import has been turned on. See Getting Values From Your ERP below.
• Calculated values- Worked out every time they are displayed, so they change as soon as the values they are calculated from change.

NOTE: An inactive field doesn't appear anywhere, even if it has values. Check the Active box if a field you expect to see is missing from the Column Chooser.

Where Custom Fields Appear

A field appears on every grid whose rows stand for its kind of record. A grid row often stands for several kinds of record at once: a Place Orders line is a supply order line for an item-site and supplier, so it can show Supply Order Line, Item-Site-Supplier, Item, Item-Site, and Supplier fields. Applies To therefore decides where a field can appear:

Custom fields also appear in CSV exports of these grids, in the public API, and in Edison's answers about the same records. Item-Site, Item-Site-Supplier, and Supplier values can be viewed and edited in a Custom Fields block on their detail panels: the Forecast Manager Attributes tab, Inventory Snapshot, the Item Dashboard, Supplier Detail, and Item-Site-Supplier Detail.

NOTE: The Forecast Manager Attributes tab shows custom field columns without being added from the Column Chooser, but it hides any that are empty for every item-site in the selected node.

NOTE: A field only appears where its kind of record does. An Item-Site-Supplier field isn't shown on a screen whose rows are item-sites, such as Forecast Manager, because one item-site can have several suppliers.

Custom Fields Grid

This grid lists the custom fields defined so far. Click a row to open it in the Custom Field Definition pane on the right.

• Field Code- The field's unique identifier. It becomes the column name in exports and the API as CF_<code>; for example, Brand becomes CF_Brand. It must also match the CustomFieldCode your ERP feed or upload file sends values under. Use only letters, numbers, and underscores, and start with a letter.
• Name- The caption users see on column headers and detail panels. You can rename it at any time without affecting saved grid layouts or integrations.
• Applies To- The kind of record the field belongs to: Item, Item-Site, Item-Site-Supplier, Supplier, Sales Order, or Supply Order Line. This decides which grids and panels show the field, which key columns identify a record in an upload or import, and which operands a calculated field can use.
• Data Type- How values are stored, sorted, and formatted: Text, Integer, Decimal, Date, Date/Time, or Yes/No. A calculated field can only be Integer, Decimal, Date, or Date/Time, and the type must match what its formula produces.
• Active- Inactive fields stop appearing in grids and exports, but their recorded values are kept. Turning a field back on makes them reappear.
• Calculated From- The formula, if the field is calculated. If this column is blank, the field is stored. It's the quickest way to see which fields are calculated.
• Order- The Display Order, which sets where these fields appear relative to each other in grids and panels. Lower numbers come first.

Custom Field Definition

This pane edits the selected field: the same settings as the grid, plus Calculated From for a calculated field.

Formulas support +, -, *, / and parentheses, with each operand written in braces, such as {Cost}. A number added to or subtracted from a date shifts it by that many days. The Check Expression button checks the formula using the same rules as Save.

Under the editor, the Available Operands list shows everything a formula for this kind of record can use, and it updates when you change Applies To. It has three columns:

• Operand- The friendly name, followed by the {Code} you'd type into the formula. Clicking an operand adds it to the end of the Calculated From box, so you don't have to type codes yourself.
• Type- Whether the operand is a number or a date.
• Source- Custom field (another numeric or date custom field on the same kind of record) or Built-in (a standard StockIQ property such as a cost, quantity, or date). Cost and price operands show a ⚠ and the permission that normally protects them, such as View Costs or View Prices.

IMPORTANT: Anyone who can see a calculated field sees its value, even if they don't have the permission that protects a cost or price it uses. A warning appears under the formula when this applies.

Only operands from the same kind of record are listed. For example, an Item field can't use an Item-Site value, because one item has many item-sites and there's no single value to use. Text and Yes/No fields never appear, since a formula can't do math with them. Supplier records have no built-in operands, because the supplier record has no numeric or date properties, but a calculated Supplier field can still use other numeric or date Supplier custom fields.

Formulas can't compare two dates (days between), use conditions (if/then), join text, or add up values across records.

The Update Info box under the pane shows who created the selected field and who last changed it.

Toolbar

• Undo- Discards unsaved edits and reloads the screen from the last saved state. It asks you to confirm first.
• Save- Saves the selected field. Save is disabled while the form has errors, and is refused with a message if:
  • the formula is invalid,
  • the field's code or name is already used, or
  • the change isn't allowed, such as changing a locked Code, Data Type, or Applies To.
• New- Adds a new field to the grid and selects it for editing. It starts as an active Text field on Item-Site named "New Custom Field" with code NewField. Change these before saving. The field isn't created until you click Save.
• Delete- Removes the selected field. Before you confirm, StockIQ tells you how many recorded values will be deleted along with it. A field that another field's formula uses can't be deleted.
• Upload Values- Opens a dialog for uploading stored values from a spreadsheet. See Uploading Values below.

IMPORTANT: Deleting a field also deletes every value recorded for it, and can't be undone. To hide a field but keep its values, clear Active instead.

Uploading Values

The upload file has one row per value, with these columns:

• ShipperCode
• CustomFieldCode
• Value
• ItemCode, SiteCode, SupplierCode
• OrderNumber, LineNumber

One file can hold values for any mix of fields. Each field's Applies To setting decides which key columns its rows need:

Value formats:

• Dates- yyyy-MM-dd.
• Decimals- Use a period.
• Yes/No- true/false, 1/0, or yes/no.
• An empty Value- Clears that value.

Rows with errors are reported and skipped; the rest of the file is still applied. Uploaded values are saved straight away and count as user-entered, so the nightly ERP import won't overwrite them. Calculated fields can't be uploaded to.

Getting Values From Your ERP

If your ERP holds the values, StockIQ can import them every night, the same way it imports your items and orders.

1. Create and save the field on this screen. Its Field Code is the CustomFieldCode the import will send.
2. Ask StockIQ Support to add the field to your ERP extract. They set up the extract for the kind of record the field applies to.
3. Click Admin > System Configuration > Staging Table Import Settings, type customfield in the Table Name filter, select the import that matches Applies To (for example, impt.ItemSiteCustomFieldValue for an Item-Site field), check Import Data Into This Table, and click Save.
4. Values arrive with the next data refresh. Add the column to your grids from the Column Chooser.

NOTE: To clear a value from the ERP side, leave its row out of the extract. A row with an empty Value is rejected rather than clearing the value.

Who Owns a Value

A value someone types into a detail panel or uploads becomes user-owned, and is marked with a person icon on the detail panel. Hover over the icon to see who set it.

• The nightly ERP import never overwrites or removes a user-owned value.
• To hand a value back to the ERP, clear it in the detail panel. The field goes back to blank, and the next import can fill it in again.

Rules and Limits

• Field codes and names must be unique across all kinds of record.
• Once a stored field has values, its Code, Data Type, and Applies To can't change. A calculated field's are locked as soon as it's saved. The name can always be changed. To change a locked setting, delete the field and create it again.
• A stored field that already has values can't be turned into a calculated field.
• A field that another field's formula uses can't be deactivated, deleted, or recoded.
• A formula that refers to itself, directly or through other fields, is rejected when you save.
• If any value in a formula is missing, the result is blank rather than zero, by design. Dividing by zero is also blank.

Troubleshooting

• The new column isn't in the grid- Most grids add custom field columns hidden: open the Column Chooser and add it. If it isn't in the Column Chooser either, reload the screen, check that the field is Active, and check Where Custom Fields Appear above to see whether that screen shows its kind of record.
• The column is there but empty- Values typed in or uploaded appear after you reload the screen. Values from your ERP appear after the next data refresh, and only if the import is turned on in Staging Table Import Settings. If the import ran but values are missing, open Admin > Full Refresh, find the custom field import in Table Import Summaries, and check its errors. A common cause is a value that doesn't match the field's Data Type, such as text in a Decimal field.
• The upload said it worked, but values don't show- Check that the CustomFieldCode in the file matches the Field Code exactly, and that the key columns identify existing records for the field's Applies To.
• A calculated field is always blank- A blank operand makes the whole result blank. Check that every field and property the formula uses has a value for that record, and use Check Expression to confirm the formula is still valid.