Autocomplete dropdown filter with data from two tables

Question

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?

healyhatman · Accepted Answer

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}

Forums

Autocomplete dropdown filter with data from two tables