Search for "Costum Lists" entries

Chritz

osTicket 1.17.2
Apache 2.4.52
PHP 8.0.27

Hello,
I created an own list under "Agent Panel -> Lists -> Custom Lists".
Then I added the list to the ticket under "Forms -> Ticket Information".
Now I want to filter for this list in the search.
To do this, I selected the list in the „Search“ under "Advanced Ticket Search" and searched for "includes".
Unfortunately, the search does not find any entrys.

If I assign a "Custom Forms" to the ticket, then it works.

What I am doing wrong?

Many thanks!

Chritz

I found the following error message in the log.
Can someone help me?

DB Error #1054
[SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.ticket_pid AS ticket_pid, A1.flags AS flags, A1.created AS created, A2.subject AS cdata__subject, A1.isoverdue AS isoverdue, B5.name AS user__name, A2.priority AS cdata__priority, B6.firstname AS staff__firstname, B6.lastname AS staff__lastname, B7.name AS team__name, A1.team_id AS team_id, A1.isanswered = '0' AS 2GkBZxz, (SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS _thread_count, (SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS _att_count, COALESCE(B6.firstname, B6.lastname, B7.name, 'zzz') AS assignee FROM ost_ticket A1 LEFT JOIN ost_ticket__cdata A2 ON (A1.ticket_id = A2.ticket_id) JOIN ost_ticket_status A3 ON (A1.status_id = A3.id) LEFT JOIN ost_thread A4 ON (A4.object_type = 'T' AND A1.ticket_id = A4.object_id) LEFT JOIN ost_thread_referral A5 ON (A4.id = A5.thread_id) LEFT JOIN ost_staff A6 ON (A5.object_type = 'S' AND A5.object_id = A6.staff_id) LEFT JOIN ost_thread A7 ON (A1.ticket_id = A7.object_id AND A7.object_type = 'C') LEFT JOIN ost_thread_referral A8 ON (A7.id = A8.thread_id) LEFT JOIN ost_staff B0 ON (A8.object_type = 'S' AND A8.object_id = B0.staff_id) LEFT JOIN ost_team B1 ON (A5.object_type = 'E' AND A5.object_id = B1.team_id) LEFT JOIN ost_team B2 ON (A8.object_type = 'E' AND A8.object_id = B2.team_id) LEFT JOIN ost_department B3 ON (A5.object_type = 'D' AND A5.object_id = B3.id) LEFT JOIN ost_department B4 ON (A8.object_type = 'D' AND A8.object_id = B4.id) LEFT JOIN ost_user B5 ON (A1.user_id = B5.id) LEFT JOIN ost_staff B6 ON (A1.staff_id = B6.staff_id) LEFT JOIN ost_team B7 ON (A1.team_id = B7.team_id) WHERE A2.field_40 REGEXP '\"?2(\"|, |$)|\"?10(\"|, |$)' AND ((A3.state = 'open' AND (A1.staff_id = 2 OR A6.staff_id = 2 OR A7.object_type = 'C' AND B0.staff_id = 2 OR A1.team_id IN (1) OR B1.team_id IN (1) OR A7.object_type = 'C' AND B2.team_id IN (1))) OR A1.dept_id IN (1) OR B3.id IN (1) OR A7.object_type = 'C' AND B4.id IN (1)) GROUP BY A1.ticket_id ORDER BY A1.created DESC LIMIT 25] Unknown column 'A2.field_40' in 'where clause' ---- Backtrace ---- #0 (root)/include/mysqli.php(207): osTicket->logDBError() #1 (root)/include/class.orm.php(3482): db_query() #2 (root)/include/class.orm.php(3529): MySqlExecutor->execute() #3 (root)/include/class.orm.php(2104): MySqlExecutor->getArray() #4 (root)/include/class.orm.php(2054): HashArrayIterator->{closure}() #5 (root)/include/class.orm.php(2033): CallbackSimpleIterator->next() #6 (root)/include/class.orm.php(2042): CallbackSimpleIterator->rewind() #7 (root)/include/class.orm.php(1713): CallbackSimpleIterator->valid() #8 (root)/include/class.orm.php(1723): CachedResultSet->fillTo() #9 (root)/include/class.orm.php(1739): CachedResultSet->asArray() #10 (root)/include/staff/templates/queue-tickets.tmpl.php(258): CachedResultSet->getIterator() #11 (root)/scp/tickets.php(569): require_once('...') #12 {main}

KevinTheJedi

Chritz

Sounds like you need to drop and rebuild the ticket__cdata table. You can drop this table and run cron manually a bunch of times until the cdata table is rebuilt and fully repopulated with data. We import the data in batches which is why you'll have to run cron a bunch of times until it's fully done.

Cheers.

Chritz

Hello KevinTheJedi,

I dropped the ticketcdata.
Then I run the /api/cron.php.
The ticketcdata has been recreated.
Unfortunately I still get the same error when searching.

Is there anything else I can do?

Chritz

I tested it again in the task.
There I get a slightly different error message.

DB Error #1054
[INSERT INTO ost_ticket__cdata SET field_48=2, ticket_id= 143 ON DUPLICATE KEY UPDATE field_48=2] Unknown column 'field_48' in 'field list' ---- Ablaufverfolgung ---- #0 (root)/include/mysqli.php(207): osTicket->logDBError() #1 (root)/include/class.dynamic_forms.php(389): db_query() #2 (root)/include/class.dynamic_forms.php(400): DynamicForm::updateDynamicDataView() #3 (root)/include/class.signal.php(98): DynamicForm::updateDynamicFormEntryAnswer() #4 (root)/include/class.orm.php(684): Signal::send() #5 (root)/include/class.dynamic_forms.php(1535): VerySimpleModel->save() #6 (root)/include/class.forms.php(1451): DynamicFormEntryAnswer->save() #7 (root)/include/class.ticket.php(3824): FormField->save() #8 (root)/include/ajax.tickets.php(706): Ticket->updateField() #9 (root)/include/class.dispatcher.php(153): TicketsAjaxAPI->editField() #10 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch() #11 (root)/include/class.dispatcher.php(122): Dispatcher->resolve() #12 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch() #13 (root)/scp/ajax.php(326): Dispatcher->resolve() #14 {main}

KevinTheJedi

Chritz

That error can be ignored as it's saying it's trying to insert a record that already exists.

If you are still getting the same error then something is going wrong. Maybe try adding a variable name to the field in question (field with ID of 40).

Cheers.

Chritz

Hello Kevinthejedi,

it works now
I added a "Short Answer" field under "Manage -> Forms -> Ticket Information". Entered something there and searched again.
Now the search for the lists also works.

Even if I delete the own field "Short Answer" it still works.
The issue can be marked as solved.

Thanks very much!