Page 1 of 1
lookup name in same table
Posted: 2018-11-05 07:55
by joebloogs
Hi All, I'm transitioning from MS Access to PHP but are unsure on how to handle my issue on how to display and store the data.
here is my query in Access
Code: Select all
SELECT tblEmployees.ID, [tblEmployees]!firstName+" "+[tblEmployees]!Surname AS Manager
FROM tblEmployees
WHERE (((tblEmployees.isManager)=True));
I assume I need to do a lookup in Appgini, or do I use a hook and then store the ID.
Any help on how best to execute this please?
Re: lookup name in same table
Posted: 2018-11-05 09:29
by pbottcher
Hi,
it depends on what you want to do, but usually you would display the concatenation of the firstname + surname as a lookup in AppGini. In your case you need to enhance the standard lookup with your where clause.
Re: lookup name in same table
Posted: 2018-11-06 02:06
by joebloogs
this works in mysql but not Appgini
Code: Select all
SELECT Concat_Ws(' ',Firstname, Lastname) AS fullname FROM Employees WHERE Ismanager = TRUE
I posted that in the Advanced field, when I go to the lookup field, it searches but cannot return any result.
Re: lookup name in same table
Posted: 2018-11-06 09:42
by pbottcher
Hi,
for a lookup you need to have the ID of the record and then the displayed information to be returned in the SQL query.
So in this case I assume you should try
Code: Select all
SELECT ID, Concat_Ws(' ',Firstname, Lastname) AS fullname FROM Employees WHERE Ismanager = TRUE
Re: lookup name in same table
Posted: 2018-11-07 11:04
by joebloogs
spot on pb, thank you