lookup name in same table

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
joebloogs
Posts: 13
Joined: 2013-04-17 13:17
Location: Perth

lookup name in same table

Post by joebloogs » 2018-11-05 07:55

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?

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 296
Joined: 2018-04-01 10:12

Re: lookup name in same table

Post by pböttcher » 2018-11-05 09:29

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.

joebloogs
Posts: 13
Joined: 2013-04-17 13:17
Location: Perth

Re: lookup name in same table

Post by joebloogs » 2018-11-06 02:06

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.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 296
Joined: 2018-04-01 10:12

Re: lookup name in same table

Post by pböttcher » 2018-11-06 09:42

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

joebloogs
Posts: 13
Joined: 2013-04-17 13:17
Location: Perth

Re: lookup name in same table

Post by joebloogs » 2018-11-07 11:04

spot on pb, thank you

Post Reply