Field Retrieval

Overview

Field retrieval in Q01 Core APIs is controlled by metadata-driven visibility rules and query parameters. The platform determines which fields to return based on COD_ON_OFF flags, center_dett parameter, and field selection parameters.

Key Concepts:

✅ COD_ON_OFF flags control field visibility
✅ center_dett parameter selects view mode (visualizza, dettaglio, nuovo, edit, allviews)
✅ forceListCost bypasses center_dett rules for specific fields
✅ $fields parameter for explicit field selection
✅ $group parameter for SQL GROUP BY aggregation
✅ debug=true to see generated SQL
✅ Metadata fields always included (ID, OWNER, CDATA, LOWNER, LDATA, TIMESTAMP, TREC)

center_dett to COD_ON_OFF Mapping

The center_dett query parameter determines which COD_ON_OFF flag is required for a field to be visible in the response.

Mapping Table

center_dett	COD_ON_OFF Flag Required	Use Case
visualizza	L (List)	List views, table grids
dettaglio	D (Detail)	Detail/edit views, single record display
nuovo	R (Search/Filter)	Search forms, filter interfaces
edit	N (New/Create)	Create forms, new record entry
allviews	(ignored)	Return ALL fields regardless of COD_ON_OFF

Important:

If field's COD_ON_OFF doesn't contain the required flag → field is not returned
Metadata fields (ID, OWNER, CDATA, etc.) are always returned regardless of COD_ON_OFF

Examples

Field Visibility by center_dett:

-- TB_COST example
COD_DIM: ART
COD_VAR: XART20
COD_ON_OFF: LDNMR  -- Has all flags

COD_DIM: ART
COD_VAR: XART21
COD_ON_OFF: D      -- Only D flag (detail only)

COD_DIM: ART
COD_VAR: XART22
COD_ON_OFF: LDR    -- L, D, R flags (list, detail, search)

Query with center_dett=visualizza (requires L flag):

GET /api/v4/core/ART/123?source=settings-2&center_dett=visualizza

Response includes:

✅ XART20 (has L flag)
❌ XART21 (no L flag - excluded)
✅ XART22 (has L flag)
✅ ID, OWNER, CDATA, LOWNER, LDATA, TIMESTAMP, TREC (always included)

Query with center_dett=dettaglio (requires D flag):

GET /api/v4/core/ART/123?source=settings-2&center_dett=dettaglio

Response includes:

✅ XART20 (has D flag)
✅ XART21 (has D flag)
✅ XART22 (has D flag)
✅ Metadata fields

Query with center_dett=allviews (no flag requirement):

GET /api/v4/core/ART/123?source=settings-2&center_dett=allviews

Response includes:

✅ XART20
✅ XART21
✅ XART22
✅ ALL other fields in dimension (ignores COD_ON_OFF completely)
✅ Metadata fields

forceListCost Parameter

The forceListCost parameter bypasses center_dett rules and returns only the specified fields plus metadata fields.

Use Case:

Custom field selection independent of COD_ON_OFF
Client-side rendering with specific fields
Performance optimization (reduce response size)
API integrations that need predictable field sets

Syntax

?forceListCost=FIELD1,FIELD2,FIELD3

Example

Request with forceListCost:

GET /api/v4/core/ART?source=settings-2&forceListCost=XART20,XART21,XART25

SQL Generated:

SELECT
    ART_ID,
    OWNER,
    CDATA,
    LOWNER,
    LDATA,
    TIMESTAMP,
    TREC,
    XART20,
    XART21,
    XART25
FROM TB_ANAG_ART00
WHERE TREC NOT IN ('C')

Response:

[
  {
    "ART_ID": 123,
    "OWNER": "SYS",
    "CDATA": "20251219120000",
    "LOWNER": "user@example.com",
    "LDATA": "20251219140000",
    "TIMESTAMP": "2025-12-19T14:00:00Z",
    "TREC": "M",
    "XART20": "Widget Pro",
    "XART21": "10",
    "XART25": "99.99"
  }
]

Key Points:

✅ Only requested fields + metadata returned
✅ COD_ON_OFF ignored
✅ center_dett ignored
✅ Reduces response size
✅ Predictable field set

forceListCost vs $fields

forceListCost:

Always includes metadata fields (ID, OWNER, CDATA, etc.)
Bypasses COD_ON_OFF completely
Returns ONLY specified fields + metadata

$fields:

Can exclude metadata fields
Still respects COD_ON_OFF (unless center_dett=allviews)
More flexible for fine-grained selection

Example Comparison:

# forceListCost - includes metadata
GET /api/v4/core/ART?source=settings-2&forceListCost=XART20
# Returns: ID, OWNER, CDATA, LOWNER, LDATA, TIMESTAMP, TREC, XART20

# $fields - can exclude metadata
GET /api/v4/core/ART?source=settings-2&$fields=XART20
# Returns: XART20 only (no metadata unless explicitly requested)

$fields Parameter

The $fields parameter allows explicit selection of which fields to return.

Syntax

?$fields=FIELD1,FIELD2,FIELD3

Examples

Select specific fields:

GET /api/v4/core/ART?source=settings-2&$fields=XART20,XART25,XART53

Response:

[
  {
    "XART20": "Widget Pro",
    "XART25": "99.99",
    "XART53": "rep1"
  }
]

Include metadata fields explicitly:

GET /api/v4/core/ART?source=settings-2&$fields=ART_ID,XART20,XART25

Response:

[
  {
    "ART_ID": 123,
    "XART20": "Widget Pro",
    "XART25": "99.99"
  }
]

Behavior:

Only specified fields returned
COD_ON_OFF still applies (unless center_dett=allviews)
Metadata NOT automatically included (unlike forceListCost)

$group Parameter

The $group parameter enables SQL GROUP BY aggregation.

Use Case:

Aggregate data (count, sum, average, etc.)
Distinct value selection
Reporting and analytics queries

Syntax

?$select=FIELD&$group=FIELD

Important: Must use $select to specify aggregation fields.

Example: Get Distinct Values

Request:

GET /api/v4/core/BCODE?source=settings-2&$select=XBCODE10&$group=XBCODE10

SQL Generated:

SELECT XBCODE10
FROM TB_ANAG_BCODE00
WHERE TREC NOT IN ('C')
GROUP BY XBCODE10

Response:

[
  { "XBCODE10": "ean13" },
  { "XBCODE10": "ean8" },
  { "XBCODE10": "code39" },
  { "XBCODE10": "qrcode" }
]

Example: Count by Category

Request:

GET /api/v4/core/ART?source=settings-2&$select=XART01,COUNT(*) as total&$group=XART01

SQL Generated:

SELECT XART01, COUNT(*) as total
FROM TB_ANAG_ART00
WHERE TREC NOT IN ('C')
GROUP BY XART01

Response:

[
  { "XART01": "1", "total": 42 },
  { "XART01": "2", "total": 38 },
  { "XART01": "3", "total": 25 }
]

Example: Sum by Department

Request:

GET /api/v4/core/ART?source=settings-2&$select=XART53,SUM(XART25) as total_price&$group=XART53

SQL Generated:

SELECT XART53, SUM(XART25) as total_price
FROM TB_ANAG_ART00
WHERE TREC NOT IN ('C')
GROUP BY XART53

Response:

[
  { "XART53": "rep1", "total_price": 12450.00 },
  { "XART53": "rep2", "total_price": 8930.50 },
  { "XART53": "rep3", "total_price": 15200.75 }
]

debug Parameter

The debug=true parameter returns the generated SQL query in metadata.

Use Case:

Query optimization and troubleshooting
Understanding how filters translate to SQL
Performance debugging
Learning SQL generation patterns

Syntax

?debug=true

Example

Request:

GET /api/v4/core/ART?source=settings-2&filter[XART06]=1&filter[XART01]=2&debug=true

Response:

[
  {
    "metadata": true,
    "sql": "select SQL_CALC_FOUND_ROWS `ART_ID`, `OWNER`, `CDATA`, `LOWNER`, `LDATA`, `TIMESTAMP`, `TREC`, `XART01`, `XART03`, `XART20`, `XART21`, `XART25`, `XART53` from `TB_ANAG_ART00` where `TREC` not in ('C') and `XART06` = 1 and `XART01` = 2 limit 100"
  },
  {
    "ART_ID": 123,
    "OWNER": "SYS",
    "XART20": "Widget Pro",
    ...
  }
]

Metadata Object Fields:

metadata: true - Identifies metadata record
sql - Complete generated SQL query

Benefits:

✅ See exact SQL executed
✅ Identify missing indexes
✅ Understand filter translation
✅ Debug performance issues
✅ Learn query patterns

Example: Debug Complex Query

Request:

GET /api/v4/core/ART?source=settings-2&filter[XART25][$gte]=50&filter[XART25][$lte]=100&$fields=XART20,XART25&$num_rows=10&debug=true

Response:

[
  {
    "metadata": true,
    "sql": "select SQL_CALC_FOUND_ROWS `XART20`, `XART25` from `TB_ANAG_ART00` where `TREC` not in ('C') and `XART25` >= 50 and `XART25` <= 100 limit 10"
  },
  {
    "XART20": "Widget Standard",
    "XART25": "59.99"
  },
  {
    "XART20": "Widget Pro",
    "XART25": "99.99"
  }
]

Use EXPLAIN: Copy SQL from debug output and run EXPLAIN to analyze query execution plan:

EXPLAIN select SQL_CALC_FOUND_ROWS `XART20`, `XART25` from `TB_ANAG_ART00` where `TREC` not in ('C') and `XART25` >= 50 and `XART25` <= 100 limit 10;

Metadata Fields

The following fields are always included in responses (except when using $fields without explicit metadata selection):

Field	Description	Example
`{DIM}_ID`	Primary key (auto-increment)	`ART_ID: 123`
`OWNER`	Creator user	`OWNER: "SYS"`
`CDATA`	Creation timestamp	`CDATA: "20251219120000"`
`LOWNER`	Last modifier user	`LOWNER: "user@example.com"`
`LDATA`	Last modification timestamp	`LDATA: "20251219140000"`
`TIMESTAMP`	ISO 8601 timestamp	`TIMESTAMP: "2025-12-19T14:00:00Z"`
`TREC`	Transaction record state	`TREC: "M"`

Metadata Fields Behavior:

# forceListCost - includes metadata automatically
GET /api/v4/core/ART?source=settings-2&forceListCost=XART20
# Returns: ART_ID, OWNER, CDATA, LOWNER, LDATA, TIMESTAMP, TREC, XART20

# $fields - must explicitly request metadata
GET /api/v4/core/ART?source=settings-2&$fields=XART20
# Returns: XART20 only

# $fields with metadata
GET /api/v4/core/ART?source=settings-2&$fields=ART_ID,TREC,XART20
# Returns: ART_ID, TREC, XART20

Field Selection Decision Tree

Need specific fields only?
    ↓
YES → Use forceListCost (includes metadata)
    OR $fields (exclude metadata for minimal response)
    ↓
NO → Need all fields?
    ↓
    YES → Use center_dett=allviews
    ↓
    NO → Use appropriate center_dett:
        - visualizza (list views) → L flag
        - dettaglio (detail views) → D flag
        - nuovo (search forms) → R flag
        - edit (create forms) → N flag

Best Practices

✅ DO:

Use forceListCost for custom field sets:

# ✅ Good - explicit field selection with metadata
GET /api/v4/core/ART?source=settings-2&forceListCost=XART20,XART25

Use center_dett=allviews for administrative interfaces:

# ✅ Good - all fields for admin panels
GET /api/v4/core/ART/123?source=settings-2&center_dett=allviews

Use $group for aggregations:

# ✅ Good - distinct values
GET /api/v4/core/BCODE?source=settings-2&$select=XBCODE10&$group=XBCODE10

Use debug for query optimization:

# ✅ Good - understand SQL generation
GET /api/v4/core/ART?source=settings-2&debug=true

❌ DON'T:

Don't use center_dett=allviews in production list views:

# ❌ Bad - returns too many fields, slow query
GET /api/v4/core/ART?source=settings-2&center_dett=allviews

# ✅ Good - only list-view fields (L flag)
GET /api/v4/core/ART?source=settings-2&center_dett=visualizza

Don't rely on COD_ON_OFF for field security:

# ❌ Bad - sensitive fields should be removed at database level
# COD_ON_OFF is for UI visibility, not security

# ✅ Good - use database views or column-level permissions for security

Don't use $group without $select:

# ❌ Bad - GROUP BY without aggregate functions
GET /api/v4/core/ART?source=settings-2&$group=XART01

# ✅ Good - explicit SELECT with GROUP BY
GET /api/v4/core/ART?source=settings-2&$select=XART01,COUNT(*) as total&$group=XART01

Performance Optimization

Reduce Response Size

Select only needed fields:

# Instead of returning all 50+ fields
GET /api/v4/core/ART?source=settings-2

# Return only required fields
GET /api/v4/core/ART?source=settings-2&forceListCost=XART20,XART25,XART53

Impact:

Smaller JSON payload
Faster network transfer
Lower client-side parsing overhead

Use Appropriate center_dett

# List view - only essential fields (L flag)
GET /api/v4/core/ART?source=settings-2&center_dett=visualizza

# Detail view - more fields (D flag)
GET /api/v4/core/ART/123?source=settings-2&center_dett=dettaglio

# Admin - all fields (use sparingly)
GET /api/v4/core/ART/123?source=settings-2&center_dett=allviews

Summary

✅ center_dett maps to COD_ON_OFF flags (visualizza=L, dettaglio=D, nuovo=R, edit=N, allviews=ignore)
✅ forceListCost bypasses COD_ON_OFF and returns specified fields + metadata
✅ $fields allows explicit field selection (metadata NOT auto-included)
✅ $group enables SQL GROUP BY aggregation
✅ debug=true returns generated SQL in metadata
✅ Metadata fields (ID, OWNER, CDATA, etc.) always included with forceListCost
✅ Use appropriate center_dett for performance (visualizza for lists, dettaglio for details)

Key Takeaways:

center_dett controls which COD_ON_OFF flag is required
forceListCost is best for custom field sets (includes metadata)
$fields provides minimal responses (exclude metadata)
$group enables aggregation and reporting
debug=true helps optimize queries
Choose right center_dett for use case (visualizza, dettaglio, allviews)

COD_ON_OFF - Field visibility flags
Filtering - Query filtering with operators
Sorting - Ordering results
Pagination - Limiting result sets
Field Visibility - Deep dive on COD_ON_OFF

Field Retrieval

Overview​

center_dett to COD_ON_OFF Mapping​

Mapping Table​

Examples​

forceListCost Parameter​

Syntax​

Example​

forceListCost vs $fields​

$fields Parameter​

Syntax​

Examples​

$group Parameter​

Syntax​

Example: Get Distinct Values​

Example: Count by Category​

Example: Sum by Department​

debug Parameter​

Syntax​

Example​

Example: Debug Complex Query​

Metadata Fields​

Field Selection Decision Tree​

Best Practices​

✅ DO:​

❌ DON'T:​

Performance Optimization​

Reduce Response Size​

Use Appropriate center_dett​

Summary​

Related Concepts​

Overview

center_dett to COD_ON_OFF Mapping

Mapping Table

Examples

forceListCost Parameter

Syntax

Example

forceListCost vs $fields

$fields Parameter

Syntax

Examples

$group Parameter

Syntax

Example: Get Distinct Values

Example: Count by Category

Example: Sum by Department

debug Parameter

Syntax

Example

Example: Debug Complex Query

Metadata Fields

Field Selection Decision Tree

Best Practices

✅ DO:

❌ DON'T:

Performance Optimization

Reduce Response Size

Use Appropriate center_dett

Summary

Related Concepts