Lookup on a non-primary key field

[Athelene]

Please let me preface this with the fact that I am new to PHP and mysql. (I've used MSSQL and other languages some, but not these two.) So please forgive my ignorance for what is probably a very basic question.

I have a table called Category. In that table, there are about 4 fields. One is idCategory which is the primary key. I also have two other fields: categoryname and categorycolor. I want a look up that presents the user with the category name and color concatenated in the dropdown (e.g., Food - Orange) but I want to store the value of categoryColor into the field I'm populating.

I've tried editing the mysql statement in the lookup manually. But I'm a bit confused about how that statement works within appgini. I tried the following statement and it presents the right information to the user in the dropdown. However, in the edit view, it does not automatically select the right selection from the dropdown based on what is in the database. It defaults to the first in the list. Also, in the grid view, the field information in color field (the one I'm trying to populate) is blank, even though there is data in the field.

The good news is that the update portion is working fine. When I edit a record, if I select the color using the current dropdown, it does in fact update the record with the correct color in that field. It's just that it's not working properly for the grid view and the edit view.

Here's the current sql statement in my lookup in appgini: SELECT `category`.`CategoryColor`, IF(CHAR_LENGTH(`category`.`CategoryName`) || CHAR_LENGTH(`category`.`CategoryColor`), CONCAT_WS('', `category`.`CategoryName`, ' - ', `category`.`CategoryColor`), '') FROM `category` ORDER BY 2

Any help would be most appreciated! Athelene

p.s. I realize it's always better to use primary keys for this. However, the database was created by someone else and it's too late at this point for me to make that change. So I need to work with what I have.

[a.gneady]

If you are using a custom query, it should return two columns: the first is the primary key, and the second is the expression to be displayed to the user. Lookups only store the parent primary key value .. so you can't store the color in the lookup field, but you can display it if you select it as the parent caption, or if you put it as part of the second column expression in the custom query.

[Athelene]

Thank you for getting back to me on this. After spending some time on this, I see that I can make many customizations in the Hooks files. I've been experimenting there and made some progress. However, I am having a problem. In the hook file for the table I am trying to update, I am trying to set a variable using a select statement with a WHERE clause which selects a record based on the value of the already selected record. What I have is:

$OrgColor = sqlValue("select standardlinkcolor from standard_links where standardlink_ID='{$data['selectedID']}'");

This returns a null value. No error messages, just a null value in the variable.

If I use:

$OrgColor = sqlValue("select standardlinkcolor from standard_links where standardlink_ID='1'");

it works just fine. So I know that it has to do with using the variable for the selected ID. Can you please tell me what I am doing incorrectly? I tried to use the code on the web help page as an example without success.

Also, is there any other documentation on using hook files that I might use as a reference? I'd really like to learn more.

Thanks!

Athelene

[a.gneady]

Also, is there any other documentation on using hook files that I might use as a reference? I'd really like to learn more.

Did you check http://bigprof.com/appgini/help/advanced-topics/hooks/?

Re $data['selectedID'], which hook function are you using it in? This variable is not available in all hook functions, only in after_insert, before_update and after_update.

[WinnerD]

thanks for paying our attention to a non-primary key field! it helped me a lot!