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?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: lookup name in same table

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: lookup name in same table

Post by pbottcher » 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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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