Page 1 of 1

Req: SQL Statement as source for lookup field

Posted: 2019-06-13 08:01
by onoehring
Hi

I would like to be able to use a SQL statement as source for my lookup field.

Then I need to be able to choose which column should be saved to the database - and which value should be shown to the user.
Currently AG saves the primary key only.

Olaf

Re: Req: SQL Statement as source for lookup field

Posted: 2019-06-22 15:12
by a.gneady
Mmm ... lookup fields implemented in AppGini work only through saving the primary key of the parent record. Storing any other field would introduce many issues. However, using 'Advaned' button in the lookup tab, you can compose any custom SQL query to display any other values/fields to users. When dealing with the raw table data, you can always use a join query to display any other values other than the primary key.

Re: Req: SQL Statement as source for lookup field

Posted: 2019-06-23 04:52
by onoehring
Hi,

yes, but I have to select from the first dropdown a table that exists in AG, and in the second a field from that table.
So I need to create some table for that SQL or use fancy select x AS FieldFromTable to accomplish that (which has failed me - you notice, when you read other questions of me.

Lets say, I want to read the memberID from the members_users table. I created a view (as suggested by Jan) in the database and a table in AG which has the same fields as the view. Unfortunately this is not even working 100% as in "rebuild fields", AG mentions, that the view (where I was forced by AG to set a primary key if I want to use memberID as as contents of my dropdown) has not PK and the Rebuild fields can not set it either.

In another case I had column X from my query which should be displayed, but even though I selected this in the dropdown in AG field definition, only the 2nd field was displayed.

I had many problems with sorting the displayed list as well. My query did sort the list as I needed, but AG messed it up and sorted by the 2nd (shown) column.

The problem (BUG/annoyance report here) was also, that nowhere (at least I did not find it anywhere) is stated, that the Advanced SQL must not have any line breaks, meaning, you need to put a one line SQL string there to that the AJAX finds an ORDER BY at the end.

Olaf