A public Customer saved search — companies only (isperson = F), eight contact-directory
columns, alphabetical by ID — converted to a SuiteQL query and proven equivalent at the cell level
against a live 66-row baseline run. Every filter, column, join, and the sort are accounted for.
Two engine behaviors required empirical reverse-engineering: the Name column's rendering
rule and the sort's case-insensitive collation. Both are named, encoded, and verified below.
Cell figure covers the 5 columns with live data (ID, Name, Email, Phone, Primary Contact) across all 66 rows, diffed programmatically. The 3 remaining columns are empty account-wide — translated but unverifiable against real values (see §6).
| Title | — (untitled) |
| Script ID | customsearch800 |
| Search type | customer |
| Public | Yes |
| Filter expression (verbatim) | [["isperson","is","F"]] |
| Sort | entityid ASC (only sorted column) |
| # | Field | Label | Join | Formula | Summary | Sort |
|---|---|---|---|---|---|---|
| 1 | entityid | ID | — | — | — | ASC |
| 2 | altname | Name | — | — | — | — |
| 3 | — | — | — | — | ||
| 4 | phone | Phone | — | — | — | — |
| 5 | altphone | Office Phone | — | — | — | — |
| 6 | fax | Fax | — | — | — | — |
| 7 | contact | Primary Contact | — | — | — | — |
| 8 | altemail | Alt. Email | — | — | — | — |
contact column is a select
field on the customer record itself, rendered as id (value) + contact name (display text).
Final SuiteQL, verbatim as verified. Re-executed exactly as printed before delivery.
SELECT c.id AS internalid, c.entityid AS id, LTRIM(SUBSTR(e.entitytitle, LENGTH(c.entityid) + 1)) AS name, c.email AS email, c.phone AS phone, c.altphone AS office_phone, c.fax AS fax, c.contact AS primary_contact_id, con.entityid AS primary_contact, c.altemail AS alt_email FROM customer c JOIN entity e ON e.id = c.id LEFT JOIN contact con ON con.id = c.contact WHERE c.isperson = 'F' ORDER BY UPPER(c.entityid) ASC
internalid is added deliberately (the search returns it implicitly as the row id); drop the column if the consumer does not want it.
| Search element | SuiteQL equivalent | Notes |
|---|---|---|
| Search type customer | FROM customer c | Both are the same superset: leads and prospects included. Baseline contains lead 1241 and prospect 1245; both rows exist in the SuiteQL customer table. Counts match for the right reason. |
| Filter isperson is F | WHERE c.isperson = 'F' | Checkbox filters are 'T'/'F' strings in both engines — 1:1. |
| Column entityid ("ID") | c.entityid | Direct. Holds an auto-number ("67", "81") or the company name depending on account naming settings per record. |
| Column altname ("Name") | LTRIM(SUBSTR(e.entitytitle, LENGTH(c.entityid)+1)) |
Rendering rule — the key finding. The stored customer.altname is populated on ALL 66 rows, but the search renders Name blank on 44 of them. Empirical rule: rendered Name = entity.entitytitle with the entityid prefix removed. Requires the entity join (customer does not expose entitytitle). See §5 step 4. |
| Column email | c.email | Direct, verified 66/66. |
| Column phone | c.phone | Direct, verified 66/66. Stored formatted — no normalization applied by either engine. |
| Column altphone ("Office Phone") | c.altphone | Direct. Empty account-wide — matched as empty, not value-verified. |
| Column fax | c.fax | Direct. Empty account-wide — matched as empty, not value-verified. |
| Column contact ("Primary Contact") | c.contact AS primary_contact_id, con.entityid AS primary_contact via LEFT JOIN contact con |
Displayed ≠ stored: search value is the contact internal id; display text is the contact name. Both materialized. LEFT join is deliberate — an INNER join would silently drop the 10 rows with no primary contact (both Anonymous records, Internal, all leads/prospects/new customers). Fan-out tested: contact is single-valued; 66 rows in = 66 rows out. |
| Column altemail ("Alt. Email") | c.altemail | Direct. Empty account-wide — matched as empty, not value-verified. |
| Sort entityid ASC | ORDER BY UPPER(c.entityid) ASC | Collation: the search sorts case-insensitively ("Jasper and Associates" before "JBL Inc." — binary sort would reverse them). Numeric-looking IDs string-sort before letters ("67" < "Anonymous"). UPPER() reproduces the order exactly across all 66 positions. |
savedSearchInspect: customer search, one filter (isperson is F), eight plain columns, entityid ASC sort. No formulas, joins, or summaries — deceptively simple.includeText: true: 66 rows, non-empty. Record types in the result: customer, lead, prospect — confirming the customer-search superset. Full baseline captured in four internal-id-windowed chunks to defeat output truncation; chunk union re-verified at 66 rows.SELECT id, entityid, altname, companyname, isperson, email, phone, altphone, fax, contact, altemail FROM customer) confirmed every field exists in SuiteQL — nothing guessed.altname, but stored customer.altname is populated on all 66 rows while the rendered "Name" is blank on 44. Drilled per the find-the-rule mandate:
altname/companyname shapes — the stored field cannot be the source.entity.entitytitle: rendered rows show a doubled title ("Davis Supplies Davis Supplies"); blank rows show a single one ("Davidson Supplies").entityid "81", entitytitle "81 Acme Corp", rendered Name "Acme Corp" — the remainder after the entityid prefix.entitytitle minus the leading entityid, trimmed. When title and entityid are identical the remainder is empty → blank cell. Encoded as LTRIM(SUBSTR(e.entitytitle, LENGTH(c.entityid)+1)).entityid, which is a binary sort.ORDER BY UPPER(c.entityid). Re-run reproduced all 66 positions.evalJs: 66×5 populated-column matrix, keyed by internal id. 330/330 cells equal; zero rows missing or extra; order check passed at every position.| Check | Method | Result |
|---|---|---|
| Row count | Baseline run vs COUNT(*) vs final query | 66 = 66 = 66 |
| Row identity | Internal-id set comparison in evalJs | 0 missing, 0 extra |
| Cell values | Programmatic diff, 5 populated columns × 66 rows | 330/330 match |
| Sort order | Position-by-position vs case-insensitive expected order | 66/66 positions |
| Join cardinality | Contact LEFT join; row count unchanged after join | No fan-out |
| Mid-list spot check | 5 rows re-fetched from the search by internal id, independent of the baseline pull | 5/5 match, incl. contact names |
c.altname AS name would have matched 66/66 rows and
passed every count-level check while being wrong in 44 Name cells — the stored field is
populated where the search renders blank. This is precisely why cell-level diffing is mandatory:
the "Name" a customer saved search renders is not the stored altname; it is the
entityid-stripped remainder of entity.entitytitle.
altname; the rendered value is derived from entitytitle. Column ids in saved-search definitions name the field slot, not necessarily the rendering source.UPPER() whenever order must match a search.customer table include leads and prospects. If the consumer wants customers-only, add AND c.searchstage = 'Customer' — but that changes the result set vs the search (would drop rows 1241 and 1245).transactionline quantities/amounts per transaction type.pendingBilling); SuiteQL status uses single letters.mainline/taxline grain filters on transaction searches.N/query: query.runSuiteQL({ query: sql }).asMappedResults() — note run() silently clamps at 5,000 rows; at 66 rows this search is far from the clamp, but use runSuiteQLPaged if the customer base grows past it.id, name, office_phone…) so downstream consumers read like the search did.entitytitle composition.entitytitle does not begin with its entityid — the SUBSTR rule would mis-strip; not observed in current data.altphone / fax / altemail — spot-check those columns once.No defects. Two observations for the search owner: