AC/VARS Queries

Overview

AC (Autocomplete) and VARS (Select) are special field types that represent foreign key relationships to other dimensions. The Core APIs provide a dedicated endpoint /api/v4/core/vars for querying these foreign key values.

Key Concepts:

✅ AC/VARS fields are foreign keys to other dimensions
✅ Values must exist in destination table (orphaned link prevention)
✅ AC requires filtering (many records, search-based)
✅ VARS returns full list (few records, pre-populated dropdown)
✅ Special endpoint: /api/v4/core/vars

Common Examples:

VARS: Gender selection (Male/Female/Other)
VARS: VAT code selection (10%, 22%, 4%)
AC: Country selection (200+ countries, searchable)
AC: Loyalty card lookup (thousands of cards, searchable)
AC: Product search (thousands of products, autocomplete)

AC vs VARS Field Types

VARS (Select/Dropdown)

Use When:

✅ Limited number of options (< 50-100 records)
✅ User selects from pre-populated list
✅ No search/filtering needed
✅ Examples: Status codes, categories, VAT rates

Behavior:

Returns complete list without filter
Suitable for dropdown menus
Fast lookup (small dataset)

Metadata Definition:

-- TB_VAR example
COD_DIM: ART
COD_VAR: XART11
TIPO_CAMPO: VARS
TIPO_VAR: CL103  -- Foreign key to TB_ANAG_CL10300

AC (Autocomplete/Searchable)

Use When:

✅ Large number of options (100+ records)
✅ User searches/filters before selecting
✅ Examples: Countries, customers, products, loyalty cards

Behavior:

Requires filter parameter (prevents returning thousands of records)
OR use getACList=1 to bypass filter (treat as VARS)
Designed for search-as-you-type interfaces

Metadata Definition:

-- TB_VAR example
COD_DIM: UT
COD_VAR: XUT26
TIPO_CAMPO: AC
TIPO_VAR: PV03  -- Foreign key to TB_ANAG_PV00.XPV03

Foreign Key Validation

Orphaned Link Prevention

When inserting or updating AC/VARS fields, Core APIs validate that the foreign key value exists in the destination table.

Validation Steps:

Type validation - Ensure value matches TIPO_CAMPO definition
Foreign key existence check - Verify value exists in destination table
Return error if not found - Prevent orphaned links

Example: Valid Foreign Key

POST /api/v4/core/ART
{
  "data": {
    "XART11": "002",  # Must exist in TB_ANAG_CL10300.XCL103
    "XART20": "Product Name"
  }
}

Validation:

SELECT COUNT(*) FROM TB_ANAG_CL10300
WHERE XCL103 = '002';  -- Must return > 0

Example: Invalid Foreign Key

POST /api/v4/core/ART
{
  "data": {
    "XART11": "999",  # Does NOT exist in TB_ANAG_CL10300
    "XART20": "Product Name"
  }
}

Error Response:

{
  "status": 400,
  "message": "Invalid foreign key: XART11 references CL103, but '999' does not exist",
  "code": "INVALID_FOREIGN_KEY",
  "field": "XART11"
}

VARS Query Endpoint

Endpoint

GET /api/v4/core/vars?source={source}&field={field_code}

Parameters:

source (required) - Source parameter (COD_MENU)
field (required) - Field code from source dimension (e.g., XART11)

Example: Query VARS Field

Scenario: Get all available values for XART11 (Product Category - VARS type).

Metadata:

-- TB_VAR
COD_DIM: ART
COD_VAR: XART11
TIPO_CAMPO: VARS
TIPO_VAR: CL103  -- Points to TB_ANAG_CL10300

-- TB_OBJECT (foreign key definition)
COD_DIM: ART
COD_VAR: XART11
COD_DIM_OBJ: CL103
COD_VAR_OBJ: XCL103

Request:

GET /api/v4/core/vars?source=settings-2&field=XART11

Response:

[
  {
    "XCL103": "003",
    "XCL104": "Accessori"
  },
  {
    "XCL103": "002",
    "XCL104": "Magl.vis.cot.fil.fan"
  },
  {
    "XCL103": "001",
    "XCL104": "Magl.merinf.p.stoffa"
  }
]

Returns:

Foreign key field (XCL103) - to store in XART11
Display field (XCL104) - to show in UI

AC Query Endpoint

Endpoint with Filter (Required)

GET /api/v4/core/vars?source={source}&field={field_code}&$filter={filter_expression}

Parameters:

source (required) - Source parameter
field (required) - Field code from source dimension
$filter (required for AC) - Filter expression

Example: Query AC Field with Filter

Scenario: Search for product categories starting with "Magl".

Request:

GET /api/v4/core/vars?source=settings-2&field=XART11&$filter=startwith(XCL104,'Magl')

Response:

[
  {
    "XCL103": "002",
    "XCL104": "Magl.vis.cot.fil.fan"
  },
  {
    "XCL103": "001",
    "XCL104": "Magl.merinf.p.stoffa"
  }
]

Without Filter:

# ❌ Bad - AC requires filter
GET /api/v4/core/vars?source=settings-2&field=XART11

Response:

[]

Returns empty array because AC fields require filter to prevent massive result sets.

getACList Parameter

Bypass Filter Requirement

Use getACList=1 to treat an AC field like a VARS field (return full list without filter).

Use Case: Load complete dataset client-side for custom filtering/rendering.

Request:

GET /api/v4/core/vars?source=settings-2&field=XART11&getACList=1

Response:

[
  { "XCL103": "001", "XCL104": "Magl.merinf.p.stoffa" },
  { "XCL103": "002", "XCL104": "Magl.vis.cot.fil.fan" },
  { "XCL103": "003", "XCL104": "Accessori" },
  ...
  { "XCL103": "099", "XCL104": "Other Category" }
]

⚠️ Warning: Use sparingly - can return large datasets. Only for AC fields with manageable record counts.

Filter Syntax

Supports standard query filter operators:

String Filters

startwith:

$filter=startwith(XCL104,'Magl')
# SQL: WHERE XCL104 LIKE 'Magl%'

endwith:

$filter=endwith(XCL104,'accessori')
# SQL: WHERE XCL104 LIKE '%accessori'

contains:

$filter=contains(XCL104,'vis')
# SQL: WHERE XCL104 LIKE '%vis%'

equals:

$filter=equals(XCL103,'002')
# SQL: WHERE XCL103 = '002'

Logical Operators

OR:

$filter=contains(XCL104,'Magl') or contains(XCL104,'Accessori')
# SQL: WHERE XCL104 LIKE '%Magl%' OR XCL104 LIKE '%Accessori%'

AND:

$filter=startwith(XCL104,'M') and endwith(XCL104,'stoffa')
# SQL: WHERE XCL104 LIKE 'M%' AND XCL104 LIKE '%stoffa'

Integration with Metadata

TB_VAR Definition

Defines field type and destination dimension:

SELECT COD_VAR, TIPO_CAMPO, TIPO_VAR
FROM TB_VAR
WHERE COD_DIM = 'ART' AND COD_VAR = 'XART11';

-- Result:
-- COD_VAR: XART11
-- TIPO_CAMPO: VARS (or AC)
-- TIPO_VAR: CL103  -- Destination dimension

TB_OBJECT Foreign Key Definition

Defines exact foreign key relationship:

SELECT COD_DIM, COD_VAR, COD_DIM_OBJ, COD_VAR_OBJ
FROM TB_OBJECT
WHERE COD_DIM = 'ART' AND COD_VAR = 'XART11';

-- Result:
-- COD_DIM: ART (source dimension)
-- COD_VAR: XART11 (source field - stores foreign key)
-- COD_DIM_OBJ: CL103 (destination dimension)
-- COD_VAR_OBJ: XCL103 (destination field - primary key)

Relationship:

TB_ANAG_ART00.XART11 → TB_ANAG_CL10300.XCL103

Use Cases

Scenario: Display category dropdown on product creation form.

Implementation:

async function loadCategories() {
  const response = await fetch(
    `/api/v4/core/vars?source=settings-2&field=XART11`
  );
  const categories = await response.json();

  // Populate dropdown
  return categories.map(cat => ({
    value: cat.XCL103,  // Foreign key value
    label: cat.XCL104   // Display text
  }));
}

Rendered:

<select name="category">
  <option value="001">Magl.merinf.p.stoffa</option>
  <option value="002">Magl.vis.cot.fil.fan</option>
  <option value="003">Accessori</option>
</select>

Use Case 2: Country Autocomplete

Scenario: Searchable country field for user registration.

Implementation:

async function searchCountries(query) {
  if (query.length < 2) return [];

  const response = await fetch(
    `/api/v4/core/vars?source=users-1&field=XUT_COUNTRY&$filter=contains(XCOUNTRY_NAME,'${query}')`
  );
  return await response.json();
}

// User types "Ita"
const results = await searchCountries('Ita');
// Returns: [{ XCOUNTRY_CODE: 'IT', XCOUNTRY_NAME: 'Italy' }, ...]

Use Case 3: Loyalty Card Lookup

Scenario: Search for customer's loyalty card.

Implementation:

async function findLoyaltyCard(cardNumber) {
  const response = await fetch(
    `/api/v4/core/vars?source=loyalty-1&field=XLOYALTY_CARD&$filter=startwith(XCARD_NUMBER,'${cardNumber}')`
  );
  return await response.json();
}

Best Practices

✅ DO:

Use VARS for small, static lists:

// ✅ Good - VAT codes (few records, rarely change)
GET /api/v4/core/vars?source=settings-2&field=XART_VAT

Use AC for large, searchable datasets:

// ✅ Good - Countries (200+ records, user searches)
GET /api/v4/core/vars?source=settings-2&field=XUT_COUNTRY&$filter=contains(XCOUNTRY_NAME,'${query}')

Validate foreign keys before submission:

// ✅ Good - verify category exists before creating product
const categories = await fetch(`/api/v4/core/vars?source=settings-2&field=XART11`);
const validCodes = categories.map(c => c.XCL103);

if (!validCodes.includes(selectedCategory)) {
  alert('Invalid category selected');
}

Debounce AC searches:

// ✅ Good - wait 300ms before searching
const debouncedSearch = debounce(searchCountries, 300);
input.addEventListener('input', (e) => debouncedSearch(e.target.value));

❌ DON'T:

Don't skip filter for AC fields:

// ❌ Bad - returns empty array
GET /api/v4/core/vars?source=settings-2&field=XUT_COUNTRY

// ✅ Good
GET /api/v4/core/vars?source=settings-2&field=XUT_COUNTRY&$filter=startwith(XCOUNTRY_NAME,'${query}')

Don't use getACList for large datasets:

// ❌ Bad - returns 10,000+ records
GET /api/v4/core/vars?source=settings-2&field=XLOYALTY_CARD&getACList=1

// ✅ Good - filter first
GET /api/v4/core/vars?source=settings-2&field=XLOYALTY_CARD&$filter=startwith(XCARD_NUMBER,'${query}')

Don't submit invalid foreign keys:

// ❌ Bad - backend will reject with 400 error
POST /api/v4/core/ART
{ "data": { "XART11": "invalid_code" } }

// ✅ Good - validate first using /vars endpoint

Don't confuse foreign key field with display field:

// ❌ Bad - storing display text instead of code
{ "XART11": "Magl.vis.cot.fil.fan" }

// ✅ Good - store foreign key code
{ "XART11": "002" }

Common Errors

Error: Missing source Parameter

Request:

GET /api/v4/core/vars?field=XART11

Error:

{
  "status": 400,
  "message": "Missing required parameter: source",
  "code": "MISSING_PARAMETER"
}

Solution: Always include source parameter.

Error: Invalid Field Code

Request:

GET /api/v4/core/vars?source=settings-2&field=INVALID_FIELD

Error:

{
  "status": 400,
  "message": "Field INVALID_FIELD not found in dimension",
  "code": "INVALID_FIELD"
}

Solution: Verify field code exists in TB_VAR for the dimension associated with source.

Error: Field Not AC/VARS Type

Request:

GET /api/v4/core/vars?source=settings-2&field=XART01

Error:

{
  "status": 400,
  "message": "Field XART01 is not AC or VARS type",
  "code": "INVALID_FIELD_TYPE"
}

Solution: Only use /vars endpoint for AC or VARS field types.

Summary

✅ AC/VARS fields represent foreign keys to other dimensions
✅ VARS returns full list (for small datasets)
✅ AC requires filter (for large datasets)
✅ getACList=1 bypasses filter requirement
✅ Foreign key validation prevents orphaned links
✅ Endpoint: GET /api/v4/core/vars?source=X&field=Y&$filter=...
✅ Metadata: TB_VAR (TIPO_CAMPO, TIPO_VAR) + TB_OBJECT (foreign key definition)

Key Takeaways:

Use VARS for dropdowns (< 100 options)
Use AC for autocomplete (100+ options, searchable)
Always validate foreign keys before submission
Debounce AC searches for better UX
Don't use getACList for very large datasets

Field Visibility - COD_ON_OFF flags
Relationships - TB_OBJECT foreign keys
Filtering - Filter syntax reference
Validation - Foreign key validation
Metadata Tables - TB_VAR and TB_OBJECT schemas

AC/VARS Queries

Overview​

AC vs VARS Field Types​

VARS (Select/Dropdown)​

AC (Autocomplete/Searchable)​

Foreign Key Validation​

Orphaned Link Prevention​

VARS Query Endpoint​

Endpoint​

Example: Query VARS Field​

AC Query Endpoint​

Endpoint with Filter (Required)​

Example: Query AC Field with Filter​

getACList Parameter​

Bypass Filter Requirement​

Filter Syntax​

String Filters​

Logical Operators​

Integration with Metadata​

TB_VAR Definition​

TB_OBJECT Foreign Key Definition​

Use Cases​

Use Case 1: Product Category Dropdown​

Use Case 2: Country Autocomplete​

Use Case 3: Loyalty Card Lookup​

Best Practices​

✅ DO:​

❌ DON'T:​

Common Errors​

Error: Missing source Parameter​

Error: Invalid Field Code​

Error: Field Not AC/VARS Type​

Summary​

Related Concepts​

Overview

AC vs VARS Field Types

VARS (Select/Dropdown)

AC (Autocomplete/Searchable)

Foreign Key Validation

Orphaned Link Prevention

VARS Query Endpoint

Endpoint

Example: Query VARS Field

AC Query Endpoint

Endpoint with Filter (Required)

Example: Query AC Field with Filter

getACList Parameter

Bypass Filter Requirement

Filter Syntax

String Filters

Logical Operators

Integration with Metadata

TB_VAR Definition

TB_OBJECT Foreign Key Definition

Use Cases

Use Case 1: Product Category Dropdown

Use Case 2: Country Autocomplete

Use Case 3: Loyalty Card Lookup

Best Practices

✅ DO:

❌ DON'T:

Common Errors

Error: Missing source Parameter

Error: Invalid Field Code

Error: Field Not AC/VARS Type

Summary

Related Concepts