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
Req: SQL Statement as source for lookup field
Req: SQL Statement as source for lookup field
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Req: SQL Statement as source for lookup field
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.
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.
Re: Req: SQL Statement as source for lookup field
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view