1. The Basics: The $output Variable

The core of this feature is the global $output variable. This variable acts as both your input (reading the spreadsheet) and your output (sending data to SmartIMPORT). When your code runs, $output is already populated with the data from the current spreadsheet row. 

You do not need to use a return statement. Modify the $output array directly. You can change existing values or add new keys.

2. Practical Examples

Example A: Combining Columns

Scenario: Your spreadsheet has "First Name" and "Last Name", but the system needs a "Full Name" field.

// Define a temporary variable for spacing
$s = ' '; 

// Combine the normalized keys into a new key
$output['full_name'] = $output['first_name'] . $s . $output['last_name'];

Example B: Conditional Logic & Math

Scenario: You want to add a 20% tax to the price, but only if the "Taxable" column is marked "Yes".

// Check if item is taxable
if ($output['taxable'] == 'Yes') {
    // Use $p as a temporary variable for the calculation
    $p = $output['price'];
    $output['price_inc_tax'] = $p * 1.20;
} else {
    $output['price_inc_tax'] = $output['price'];
}

Example C: Validation (Skipping Rows)

Scenario: Skip the row entirely if the "Status" column is set to "Inactive".

if ($output['status'] == 'Inactive') {
    // Set output to null to stop processing this specific row
    $output = null;
}

1. Available Data Objects

In this stage, you have access to the $input array, which contains two crucial sets of data:

A. The Incoming Data ($input['event'])

This contains the raw data from your spreadsheet row (normalised with lowercase keys), precisely as it appeared in the initial processing step.

B. The Search Result ($input['search'])

This object contains the results of the system's attempt to match the row to an existing constituent. It tells you whether a match was found and provides details about that record.

2. The Search Object Structure

To write effective logic, you need to understand the structure of $input['search']. Below is a reference schema showing the most common fields.

{
  "id": "185",                     // The System ID of the found record
  "lookup_id": "1001",             // The user-facing ID
  "record_already_exists": true,   // Boolean: true if a match was found
  "cons": {                        // The "Constituent" details object
      "id": "185",
      "first": "John",
      "last": "Doe",
      "title": "Mr.",
      "type": "Individual",
      "gender": "Male",
      "birthdate": {
          "d": 14,
          "m": 3,
          "y": 1980
      },
      "address": {
          "address_lines": "123 Main St",
          "city": "Denver",
          "state": "CO",
          "postal_code": "80202",
          "country": "United States",
          "type": "Home",
          "do_not_mail": false
      },
      "email": {
          "address": "john.doe@example.com",
          "type": "Email",
          "do_not_email": false
      },
      "phone": {
          "number": "(555) 123-4567",
          "type": "Home",
          "do_not_call": false
      },
      "spouse": {
          "first": "Jane",
          "last": "Doe"
      }
  }
}

3. Practical Examples

You can use this data to perform complex logic. As with the pre-processing step, you modify the global $output variable to control the flow.

Example A: Skip Import Based on Criteria

Scenario: If the record already exists and is marked as "Deceased" in the database, do not process this row.

// Check if match exists
if (array_key_exists('record_already_exists', $input['search']) && 
      $input['search']['record_already_exists']) {

    // Check the 'deceased' flag in the 'cons' object
    if ($input['search']['cons']['deceased'] == true) {
        // Set output to null to stop processing this row entirely
        $output = null; 
    }
}

Example B: Comparing Incoming vs. Existing Data

Scenario: You want to log a warning if the spreadsheet address is different from the database address.

if (array_key_exists('record_already_exists', $input['search']) && 
      $input['search']['record_already_exists']) {
      // Get the database zip code (from search/cons)
    $z = $input['search']['cons']['address']['postal_code'];
    
    // Get the spreadsheet zip code (from event)
    $y = $input['event']['zip_code']; // Assuming column header is 'Zip Code'
    
    // Compare first 5 digits
    if (substr($z, 0, 5) != substr($y, 0, 5)) {
        // Stop the flow with a warning
            $output['error'] = 'Warning: Address mismatch detected.';
    }
}

$output['command'] = [
    'function' => 'lookup',
    'arguments' => [
        'use_default' => 'true',
        'return_rows' => false,   // Set to true if you need the full row data
        'mode' => 'normal',
        'searches' => [
            [
                'spreadsheet' => '123456789', // The ID of the external sheet
                'search' => 'value_to_find',  // The value to search for
                'matching' => 'exact'         // 'exact' or 'contains'
            ]
        ]
    ]
];

// Step 1: Check if the lookup has been performed yet
if (empty($output['lookups_done'])) {
    // PREPARE THE LOOKUP
    // We want to search the external sheet for the product_id from the current row
    $output['command'] = [
        'function' => 'lookup',
        'arguments' => [
            'use_default' => 'true',
            'return_rows' => false,
            'mode' => 'normal',
            'searches' => [
                [
                    'spreadsheet' => '987654321', // The external Product Database ID
                    'search' => $output['product_id'],
                    'matching' => 'exact'
                ]
            ]
        ]
    ];
} else {
    // Step 2: Process the results
    // The system has returned with the data. 
    // Remember to use single-letter variables ($a-$z).
    
    $d = $output['lookups_data'];
    
    // Assuming the lookup returns an array where the first item is the result
    if (!empty($d)) {
        $output['product_name'] = $d[0];
    } else {
        $output['product_name'] = 'Unknown Product';
    }
}