The custom queries queries contain some values that assume the default configuration of classifications. These issues can be fixed by updating the way the queries are configured in the database. I encourage people building new installations to sort out any customization of classifications, family roles, custom fields etc early on because they are difficult to change later. If you are set with the way classifications are configured now it would make sense to update the affected queries.
Mike
Michael Wilt
ChurchInfo Team Leader
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I guess my thoughts were that A lot of time when a custom attribute is added the system automatically takes that into account.
It seems that the query options for query 18 are in queryparameteroptions_qpo table, but most maybe all other options such as the names of classifications are in list_lst
I fixed the query by adding this ...
SELECT lst_OptionId AS Value, lst_OptionName as Display
FROM list_lst
WHERE lst_ID= '1'
to qrp_OptionSQL.queryparameters_qrp
and changing "1" to "2" in qrp_Type in the same table.
The query now uses all of the classifications that I have made.
Last edit: Luke 2015-03-17
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I ran into this same problem a while back and the 'solution' was to update another table with the same information, which seems to defeat the purpose of having a relational database in the first place. It makes more sense to use the same table for all queries.
Luke, I didn't completely follow what you said you did above. (I don't really know sql commands.) What file did you update? Where were the above commands put in that file?
Mike, might this be a solution to include in a future release of churchinfo?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The custom queries are modified using phpMyAdmin to work directly on the tables used by the custom query system. It is certainly a challenge to create or modify these queries but it is hopefully easier than working on php code. The reason they have hard-coded values is to keep them from getting ridiculously complex. If they contained the logic to look up all the values that might be changed by customization they would be terribly unwieldy.
Here are some of the techniques I use to work on these queries.
I run the query that I want to modify or one that resembles something I want to create. When it runs it displays the actual SQL used at the bottom of the page. I look at the SQL to figure out how it is working. I often copy the SQL and run it in the custom query box to experiment with modifications. If I wind up with a new query that is useful I make a new record in the table query_qry.
Creating a query that has customization options is a bit more work. There is a table for options and another for choices to present in a drop-down list. The query string includes special fields that are filled in with the user choices when the query is run. The easiest way I have found to develop queries like this is to refer to SQL/Install.sql to see how these sorts of queries are built.
There was an effort to create an editing page for making and modifying queries but it never finished up to the point where we could release it.
Mike
Michael Wilt
ChurchInfo Team Leader
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello,
There appears to be a glitch in the query system, when I add new classifications I cannot search by them in the birthdays.
To be more concrete...
/QueryView.php?QueryID=18 Should have my custom added Classes, but it does not. Did I do something wrong?
Hi Luke,
The custom queries queries contain some values that assume the default configuration of classifications. These issues can be fixed by updating the way the queries are configured in the database. I encourage people building new installations to sort out any customization of classifications, family roles, custom fields etc early on because they are difficult to change later. If you are set with the way classifications are configured now it would make sense to update the affected queries.
Mike
Michael Wilt
ChurchInfo Team Leader
I guess my thoughts were that A lot of time when a custom attribute is added the system automatically takes that into account.
It seems that the query options for query 18 are in queryparameteroptions_qpo table, but most maybe all other options such as the names of classifications are in list_lst
I fixed the query by adding this ...
SELECT lst_OptionId AS Value, lst_OptionName as Display
FROM list_lst
WHERE lst_ID= '1'
to qrp_OptionSQL.queryparameters_qrp
and changing "1" to "2" in qrp_Type in the same table.
The query now uses all of the classifications that I have made.
Last edit: Luke 2015-03-17
I ran into this same problem a while back and the 'solution' was to update another table with the same information, which seems to defeat the purpose of having a relational database in the first place. It makes more sense to use the same table for all queries.
Luke, I didn't completely follow what you said you did above. (I don't really know sql commands.) What file did you update? Where were the above commands put in that file?
Mike, might this be a solution to include in a future release of churchinfo?
The custom queries are modified using phpMyAdmin to work directly on the tables used by the custom query system. It is certainly a challenge to create or modify these queries but it is hopefully easier than working on php code. The reason they have hard-coded values is to keep them from getting ridiculously complex. If they contained the logic to look up all the values that might be changed by customization they would be terribly unwieldy.
Here are some of the techniques I use to work on these queries.
I run the query that I want to modify or one that resembles something I want to create. When it runs it displays the actual SQL used at the bottom of the page. I look at the SQL to figure out how it is working. I often copy the SQL and run it in the custom query box to experiment with modifications. If I wind up with a new query that is useful I make a new record in the table query_qry.
Creating a query that has customization options is a bit more work. There is a table for options and another for choices to present in a drop-down list. The query string includes special fields that are filled in with the user choices when the query is run. The easiest way I have found to develop queries like this is to refer to SQL/Install.sql to see how these sorts of queries are built.
There was an effort to create an editing page for making and modifying queries but it never finished up to the point where we could release it.
Mike
Michael Wilt
ChurchInfo Team Leader