Multi Lookup field for one table

[kevinlo]

I would like to create a from , it have multi lookup field and link up with one table. But I can't make it work on SQL builder.
For example :

Device Form , only four field
device_name , handle_by , created_by , updated_by

Staff Form , only one field
staff_name

I make handle_by , created_by , updated_by into Lookup type and lookup for staff table .
It work on Device Edit Form.
But it can't work on Device browse form.
I try to make the field relationship to that three lookup field , no record show.

Can anyone help ?

Thanks
Kevin Lo

[admin]

Kevin,

I'm not sure what you mean.

Steven

[toms]

Kevin,

Can you show us the SQL statement of your Device browse form?
I think you need 3 left join clauses to query data from the correlated staff table.

[kevinlo]

I finally make it work via below SQL statement in Browse form

select i.*, hs.staff_name, cs.staff_name, us.staff_name , dt.device_type_description
from 3ds_iplist i
left join 3ds_staff hs on hs.3ds_staff_id=i.handle_by
left join 3ds_staff cs on cs.3ds_staff_id=i.created_by
left join 3ds_staff us on us.3ds_staff_id=i.updated_by
left join 3ds_device_type dt on dt.3ds_device_type_id=i.device_type
order BY i.ip ASC

I have three field need to lookup with Staff Table. I try to use SQL builder to build the statement. But I don't know how to build it.

Thanks
Kevin Lo

[admin]

Kevin,

You have pointed out a few bugs I need to fix.

But I have added the ability to edit the SQL manually, that you can still use it until I fix them.

sql_builder.PNG

You will need to get the latest from Github and run the Update Button on the Builders Tab of the Home Page.

it shouldn't take me long to fix the bugs, maybe a couple of days.

Steven