Req: SQL Statement as source for lookup field

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Req: SQL Statement as source for lookup field

Post by onoehring » 2019-06-13 08:01

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

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Req: SQL Statement as source for lookup field

Post by a.gneady » 2019-06-22 15:12

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.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Req: SQL Statement as source for lookup field

Post by onoehring » 2019-06-23 04:52

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

Post Reply