Custom "Department" Field Not Saving Correctly on Ticket Creation

yoforever

Description:
I've encountered an issue with custom fields of type "Department" in osTicket. When creating a new ticket, the field values are not saved correctly in the database, and the data does not sync properly to the ticket queue columns until a manual update is performed. This behavior was observed in a fresh installation with no custom modifications.
Steps to Reproduce
Install a fresh version of osTicket (tested on v1.18.2.
Go to Admin Panel > Forms > Ticket Details and add a new field:
Name: "Dependencia" (or any name).
Type: "Department".
Set as visible and editable for agents.
Go to Admin Panel > Settings > Tickets > Queues > Open > Columns and add the "Dependencia" field as a column.
Create a new ticket from scp/tickets.php, selecting a department in the "Dependencia" field.
Check the database:
Query ost_form_entry_values for the ticket's form entry.
Query ost_ticketcdata for the ticket.
Open the ticket and manually update the "Dependencia" field (e.g., reselect the same value).
Expected Behavior
On ticket creation:
In ost_form_entry_values: value should contain the department name (e.g., "Support"), and value_id should contain the department ID (e.g., 1).
In ost_ticketcdata: The dependencia column should contain the department name (e.g., "Support").
In the "Open" queue: The "Dependencia" column should display the department name.
Actual Behavior
On ticket creation:
In ost_form_entry_values: value contains the department ID (e.g., 1), and value_id is NULL.
In ost_ticketcdata: The dependencia column is "0".
In the "Open" queue: The "Dependencia" column is empty.
After manually updating the ticket:
ost_form_entry_values corrects itself: value shows the department name, and value_id shows the ID.
At some point later (seemingly random), ost_ticketcdata updates with the department name, and the queue reflects it.
Additional Details
Version: osTicket v1.18.2.
Environment: Fresh install, no custom plugins or modifications.
Database Observations:
Initial state: ost_form_entry_values stores the ID in value instead of the name, and value_id is NULL.
Post-update: Data corrects in ost_form_entry_values, but ost_ticketcdata syncs inconsistently.
Possible Cause: The "Department" field type may not be processed correctly on ticket creation, and synchronization to ost_ticketcdata seems delayed or event-driven (e.g., manual update or background process).
Workaround
Manually updating the ticket after creation corrects ost_form_entry_values, and the data eventually syncs to ost_ticketcdata. A plugin could also force the correct values on ticket.created.
Logs/Database Evidence
Initial ost_form_entry_values (ticket ID 2, field "Dependencia"):
entry_id | field_id | value | value_id
---------+----------+-------+---------
6 | 26 | 1 | NULL
After manual update:
entry_id | field_id | value | value_id
---------+----------+---------------+---------
6 | 26 | Support | 1
ost_ticketcdata (initially):
ticket_id | dependencia
----------+------------
2 | 0
ost_ticketcdata (after "magic" sync):
ticket_id | dependencia
----------+------------
2 | Support
Request
Can anyone confirm this behavior? Is this a known issue, or should it be reported as a bug? Any insights into the inconsistent sync with ost_ticketcdata would be appreciated.

KevinTheJedi

yoforever

It's not due to the _form_entry_values data; it's due to the _ticket__cdata data. For some reason _ticket__cdata is getting 0 instead of the actual ID of the Department. Yes, this is a bug but definitely not a high priority one. This will be put on the list to look into later on down the line. We are focusing on v2.0 so legacy will not see another update for a bit. You are more than welcome to look into this yourself to speed up the process.

Cheers.

yoforever

ok, thx. I upload here a plugin anyway if someone want it. Note that it is just a simple one, you have to change the names of the new entries manually before install the plugin (and after create the forms)

plugin.php

<?php
return array(
  'id' => 'fixdeptcdata',
  'name' => 'Fix Dept Cdata Plugin',
  'description' => 'Fixes the synchronization of Department-type custom fields (procedencia, destinatario) in ost_ticket__cdata on ticket creation.',
  'version' => '1.0',
  'author' => 'Carlos Gonz  lez',
  'plugin' => 'fix_dept_cdata.php:FixDeptCdataPlugin'
);

fix_dept_cdata.php

<?php
// /include/plugins/fix_dept_cdata/fix_dept_cdata.php

require_once(INCLUDE_DIR . "class.plugin.php");
require_once(INCLUDE_DIR . "class.signal.php");
require_once(INCLUDE_DIR . "class.dept.php");

error_log("FixDeptCdataPlugin: Archivo cargado");

Signal::connect('ticket.created', function($ticket) {
    error_log("FixDeptCdataPlugin: onTicketCreated ejecutado para ticket #" . $ticket->getId());
    
    $fields_to_fix = ['procedencia', 'destinatario'];
    $form_id = 2; // "Ticket Details", ajusta si es diferente
    
    foreach ($fields_to_fix as $field_name) {
        $sql = "SELECT v.value, v.value_id 
                FROM " . TABLE_PREFIX . "form_entry e
                JOIN " . TABLE_PREFIX . "form_entry_values v ON e.id = v.entry_id
                JOIN " . TABLE_PREFIX . "form_field f ON v.field_id = f.id
                WHERE e.object_id = " . db_input($ticket->getId()) . "
                AND e.object_type = 'T'
                AND e.form_id = " . db_input($form_id) . "
                AND f.name = " . db_input($field_name);
        $result = db_query($sql);
        $row = db_fetch_array($result);
        
        // Manejar la inconsistencia: revisar value y value_id
        $dept_id = null;
        if ($row) {
            if ($row['value_id'] && is_numeric($row['value_id'])) {
                $dept_id = $row['value_id'];
                error_log("FixDeptCdataPlugin: Usando value_id para " . $field_name . ": " . $dept_id);
            } elseif ($row['value'] && is_numeric($row['value'])) {
                $dept_id = $row['value'];
                error_log("FixDeptCdataPlugin: Usando value para " . $field_name . ": " . $dept_id);
            } else {
                error_log("FixDeptCdataPlugin: Valor no numérico para " . $field_name . ": value=" . $row['value'] . ", value_id=" . $row['value_id']);
                continue;
            }
        } else {
            error_log("FixDeptCdataPlugin: No se encontró el campo " . $field_name . " para ticket #" . $ticket->getId());
            continue;
        }

        if ($dept_id) {
            $dept = Dept::lookup($dept_id); // Cambiado de Department a Dept
            if ($dept) {
                $dept_name = $dept->getName();
                $cdata_sql = "UPDATE " . TABLE_PREFIX . "ticket__cdata 
                              SET " . $field_name . " = " . db_input($dept_name) . "
                              WHERE ticket_id = " . db_input($ticket->getId());
                if (db_query($cdata_sql)) {
                    error_log("FixDeptCdataPlugin: Actualizado ost_ticket__cdata para ticket #" . $ticket->getId() . " con " . $field_name . " = " . $dept_name);
                } else {
                    error_log("FixDeptCdataPlugin: Error al actualizar ost_ticket__cdata para " . $field_name . ": " . db_error());
                }
            } else {
                error_log("FixDeptCdataPlugin: Departamento no encontrado para ID " . $dept_id . " en campo " . $field_name);
            }
        }
    }
});

class FixDeptCdataPlugin extends Plugin {
    function bootstrap() {
        error_log("FixDeptCdataPlugin: bootstrap() ejecutado");
    }
}

error_log("FixDeptCdataPlugin: Fin del archivo");

KevinTheJedi

yoforever

You made it more complicated than it needs to be. 🤦‍♂️ You could've investigated the issue and fixed the underlying issue (probably a one-liner change) instead of creating an entire plugin simply for a bandaid fix.

Cheers.