Hi,
I would like to have the tableview not based on the actual table anymore, but I want to include some fields from linked (child) tables as well.
Thus I figure, I would need to change the source of TV to my custom SQL query AND I will need to change table headers as well: I will need a header/label for all fields in my custom query.
Could someone please point me a way (which file is best/the only I need to change)?
Is it maybe the root/tablename_view.php file only?
If so - is it possible to overwrite the code (variables) using a hooks file, so, that I will not need to repeat changes after regeneration of the application?
Why? The TV is being used as the most informative page which enables the user to search for things that are usually found in child tables only. Also the CSV export of the parent table can then include the fields from children which enables easy analysis using for example Excel.
Thank you already
Olaf
How? Change tableview source to custom SQL query
How? Change tableview source to custom SQL query
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: How? Change tableview source to custom SQL query
Hi Olaf,
what you could try is to create a "dummy" table in AppGini that holds the data you want to show. Here you can change the SQL query to your needs to show all the data that you want ($options).
Hope that gives you a starting point
what you could try is to create a "dummy" table in AppGini that holds the data you want to show. Here you can change the SQL query to your needs to show all the data that you want ($options).
Hope that gives you a starting point
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.
Re: How? Change tableview source to custom SQL query
Hi Olaf,
To piggy-back off of what Pascal has suggested, could you use this dummy table to create a view in SQL (AG thinks it is a table, but drop the table in SQL and create a view based off of your new queries)? Maybe I am not understanding the problem. A limitation of this solution is that it becomes read-only and it can be a hassle to update the schema. This is basically how we do reporting.
~Paul
To piggy-back off of what Pascal has suggested, could you use this dummy table to create a view in SQL (AG thinks it is a table, but drop the table in SQL and create a view based off of your new queries)? Maybe I am not understanding the problem. A limitation of this solution is that it becomes read-only and it can be a hassle to update the schema. This is basically how we do reporting.
~Paul
Re: How? Change tableview source to custom SQL query
Hi pbötcher & pfrumkin,
thank you for your ideas. I will probably use both and as far as I see, it's actually only the tablename_view.php file which I need to adjust: Change QueryFieldsCSV, QueryFieldsFilters, QueryFieldsQS, QueryFieldsTV and of course QueryFrom.
The idea to let AG do the work and generate the table and all other table related files and simply replace the source is good. If I can make use of a database view at this time I am not sure, but the reminder is gladly taken.
Thank you
Olaf
thank you for your ideas. I will probably use both and as far as I see, it's actually only the tablename_view.php file which I need to adjust: Change QueryFieldsCSV, QueryFieldsFilters, QueryFieldsQS, QueryFieldsTV and of course QueryFrom.
The idea to let AG do the work and generate the table and all other table related files and simply replace the source is good. If I can make use of a database view at this time I am not sure, but the reminder is gladly taken.
Thank you
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
Re: How? Change tableview source to custom SQL query
Hi Olaf,
from my exerience it is not even necessary to go the route with the view. Leave the table as is from AppGini, use the init function to handle your query.
Make the table read-only as you will have fields that you add from other tables.
from my exerience it is not even necessary to go the route with the view. Leave the table as is from AppGini, use the init function to handle your query.
Make the table read-only as you will have fields that you add from other tables.
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.
Re: How? Change tableview source to custom SQL query
Hi pbötcher,
thank you for your advice.
Olaf
thank you for your advice.
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
Re: How? Change tableview source to custom SQL query
Yes, I think this is much less maintenance than the view. However, this means you don't see the same data directly in the database as you do through AG, correct? From your description, this meets your need, you wanted to present the data in the TV.
I like it!
I like it!
Re: How? Change tableview source to custom SQL query
Hi pfrumkin,
yes, initially I did want to see different (=additional) data in the TV than in the DV. Once I went through the process, I decided against this and kept the old way: Concat data from a child table into one field and save this to the parent table as copy. I am aware that this breaks nomalization, but the copied fields are read only in DV, thus can be changed in the child tables/registers only.
Olaf
yes, initially I did want to see different (=additional) data in the TV than in the DV. Once I went through the process, I decided against this and kept the old way: Concat data from a child table into one field and save this to the parent table as copy. I am aware that this breaks nomalization, but the copied fields are read only in DV, thus can be changed in the child tables/registers only.
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