Buy Now
Sign in

Autocomplete dropdown filter with data from two tables

healyhatman , April 21 at 10:24
Answered
H
healyhatman
I have two tables, a timesheet table and the users table. I want an autocomplete dropdown, with the key coming from one table and the label from the other. This way, it will only autocomplete with the names of workers that have a timesheet entry in the database, instead of all users.
With the first Model set to All Matching Records, and timesheet.user_id in "Group Fields" the following is returned in the debug:
Array
(
[read_workers] => Array
(
[log] => Array
(
[0] => SELECT `timesheet`.`user_id` AS `timesheet.user_id`, `worker`.`name` AS `worker.name` FROM `#_employee_timesheet_timesheetline` AS `timesheet` LEFT JOIN `a#_users` AS `worker` ON `timesheet`.`user_id` = `worker`.`id` WHERE `worker`.`name` LIKE '%kam%' GROUP BY `timesheet.user_id` LIMIT 100;
)

[var] => Array
(
[0] => Array
(
[timesheet] => Array
(
[user_id] => 10
)

[worker] => Array
(
[name] => Kamron
)

)

)

)
with the first instead set to "Return an array of key/value pairs", I get the following:
Array
(
[read_workers] => Array
(
[log] => Array
(
[0] => SELECT `timesheet`.`user_id` AS `timesheet.user_id`, `worker`.`name` AS `worker.name` FROM `#_employee_timesheet_timesheetline` AS `timesheet` LEFT JOIN `#_users` AS `worker` ON `timesheet`.`user_id` = `worker`.`id` WHERE `worker`.`name` LIKE '%kam%' GROUP BY `timesheet.user_id` LIMIT 100;
)

[var] => Array
(
[10] => 10
)

)
Please help.
ALSO
Once I have the autocomplete dropdown working, I would like a few of them to use as filters. How do I set the buttons up to do that?
Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman
G
gix.vax
wich fields you retrieve in your second model ?
model 2
(worker , right?)
Fields to retrieve :
name
this should work
first model : key/value pairs
second model : one matching
H
healyhatman
Sorry I had already solved this. Pretty sure I had to specify the model for each.
Models:
timesheet
Where: concat( worker.firstName , ' ' , worker.lastName)/LIKE:%{data:worker}%
Select type: Return an array of key/value
Fields to retrieve: timesheet.user_id , concat( worker.firstName , ' ' , worker.lastName):timesheet.worker_name
worker
One matching
And in the event
{fn:read_workers}
{var/jsonen:read_workers}
Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman
G
gix.vax
with this answer you solved my problem with concat
thank you