Search on and use of custom fields

Help
2012-02-02
2014-03-09
  • Bob Schaefer
    Bob Schaefer
    2012-02-02

    I've added quite a few custom fields to my flesh out my member profiles - fields such as: Preferred Name; Date Joined; Date Removed; HS Class Of; Occupation; Ethnicity; etc.

    How can I search or create reports on these fields? And is there a way to modify the existing reports to use some of them - i.e., use the Preferred Name instead of the First Name in directories and labels?

    It's nice to be able to store this additional information, but I'd really like to be able to work with it like any other field.

     
  • Michael Wilt
    Michael Wilt
    2012-02-02

    The query feature in ChurchInfo is very powerful but also complex.  I'll try to make an example showing how you can search on custom fields.

    The reports all use specific fields for specific purposes.  Some of them are configurable.  For example, the directory report can be configured to include custom fields. 

    At my own church we put a preferred name right in the First name field, knowing how it will be used.  We don't have as much need for a legal name.

    We occasionally get requests for a general-purpose report generator but I have not seen any really clear vision of how it might work.

    Mike

    Michael Wilt
    ChurchInfo Team Leader

     
  • Bob Schaefer
    Bob Schaefer
    2012-07-18

    Mike,

    Any progress on that example?

    You mentioned that there wasn't a clear vision in the requests you've received for how a general-purpose report generator might work. If what you mean by "work" is how it might be used by an end user, I think that's the point - that there are scenarios where users will want to sift and sort their data in ways that the developers can't (and shouldn't try to) anticipate.

    I think what I'm trying to get at is that its possible to store data in ChurchInfo but have no way to access that data, short of viewing individual records, or knowing enough SQL to be able to compose your own query. That's less than ideal. The custom fields seem to be second-class citizens compared to the built-in ones, since they're not searchable.

    Let me give you an example of how I personally might put this to use…

    For our congregational report each year, I need to gather several pieces of information about our congregation, such as:

    * How many members did we gain, and how did they come to us (transfer, baptism, etc.)?
    * How many members did we lose, and for what reason (death, transfer, inactivity, etc.)?
    * Which members were… (baptized, buried, confirmed, married, etc.)?

    I currently store this data in custom fields - an individual's record includes their date of baptism, confirmation, etc, as well as when and how they came and departed the congregation, etc. I just have no way of asking the database to show me these specific records. It's good to have the data for reference, but it's even better when it can be actively searched and filtered, like the built-in fields.

    The custom fields really need to be first-class citizens. Different denominations, and even individual congregations, will have record-keeping needs that you can't be expected to account for. That's the whole reason for offering custom fields - to make your product flexible enough to accommodate churches of every flavor, while including the most common fields out of the box.

    I admit that I don't know much about PHP or MySQL - is it particularly difficult to include all the possible fields on a search page, rather than just the built-in ones? Even if this functionality were only exposed in the export search, it would be better than not having it at all.

    I hope it doesn't sound as though I am unhappy with ChurchInfo - I think it's a tremendous product to be offered for no charge! And it's still quite superior to the spreadsheet my church was using two years ago. So please take my comments as suggestions for making a strong product better, from a user who's trying hard to make ChurchInfo fit his congregation's particular needs.

    Thanks!
    -B.

     
  • Michael Wilt
    Michael Wilt
    2012-07-20

    I'm sorry this is taking so long.  There is so much to do.  I will try to put together an example query and hopefully that will make the custom fields more useful.

    Mike

    Michael Wilt
    ChurchInfo Team Leader

     
  • Michael Wilt
    Michael Wilt
    2012-07-22

    Well this isn't really much of a step toward making custom fields on par with built-in fields but I did figure out how to make a query that searches on a custom field.  To add this query, run these in phpMyAdmin.  The will add records into the query tables to make a new query for custom person fields.

    INSERT INTO `queryparameters_qrp` (`qrp_ID`, `qrp_qry_ID`, `qrp_Type`, `qrp_OptionSQL`, `qrp_Name`, `qrp_Description`, `qrp_Alias`, `qrp_Default`, `qrp_Required`, `qrp_InputBoxSize`, `qrp_Validation`, `qrp_NumericMax`, `qrp_NumericMin`, `qrp_AlphaMinLength`, `qrp_AlphaMaxLength`) VALUES
    (200, 200, 2, 'SELECT custom_field as Value, custom_Name as Display FROM person_custom_master', 'Custom field', 'Choose customer person field', 'custom', '1', 0, 0, '', 0, 0, 0, 0),
    (201, 200, 0, '', 'Field value', 'Match custom field to this value', 'value', '1', 0, 0, '', 0, 0, 0, 0);

    INSERT INTO `query_qry` (`qry_ID`, `qry_SQL`, `qry_Name`, `qry_Description`, `qry_Count`) VALUES
    (200, 'SELECT a.per_ID as AddToCart, CONCAT(''<a href=PersonView.php?PersonID='',a.per_ID,''>'',a.per_FirstName,'' '',a.per_LastName,''</a>'') AS Name FROM person_per AS a LEFT JOIN person_custom pc ON a.per_id = pc.per_ID WHERE pc.~custom~=''~value~'' ORDER BY per_LastName', 'CustomSearch', 'Find people with a custom field value', 1);

    This simple query assumes you know how to format the thing being matched.  For example, a custom field that is boolean will match against 'true' or 'false'

    Mike

    Michael Wilt
    ChurchInfo Team Leader

     
  • Bob Schaefer
    Bob Schaefer
    2012-07-25

    Hi, Mike…

    Thanks for the work you've done on this! I understand how many requests there must be, to say nothing of the team's own plans for ChurchInfo. I appreciate the snappy response.

    And the code you offered is a definite step in the right direction, too. I can easily use this new search to search for students by their class, for example. And the ability to either add the results to the cart or to intersect them with the cart makes it possible to effectively search multiple terms or in multiple fields. It takes a little work, but it's significantly better than not having those fields exposed to search at all!

    I hope you'll continue to refine this, so that there's eventually a search page similar to the CSV export page, which includes the custom fields. That's the holy grail to me. :)

    In the meanwhile, I had a question and a correction.

    First the question: Is it possible to use wildcards or partial matches in this custom field search? This is necessary for custom date fields to be searched in a useful manner. For example, if I know someone was baptized this Sunday, I can search for the string "2012-07-22" and turn up their record. But I wasn't able to search for the string "2012" in that field to find all of those baptized this year.

    Now the correction: In your code above, there's a type. It reads 'Choose customer person field' instead of 'Choose custom person field'. It doesn't affect the functionality, of course, but I wanted to point it out for others who may use this method.

    Thanks again! It's great to have a dev team that takes its user comments seriously.

     
  • MrEagle
    MrEagle
    2012-07-25

    Hi, had the same problem - played around a bit with google - and searching for SQL and got this solution.  Works great - capitalisation - partial words etc. does not matter - anything that is in a custom field can be found.

    I have a Custom field labelled Interest - and can have multiple interest listed there - previously could not search for a single entry - now I can search for all. 

    in  query_qry #200

    SELECT a.per_ID as AddToCart, CONCAT('<a href=PersonView.php?PersonID=',a.per_ID,'>',a.per_FirstName,' ',a.per_LastName,'</a>') AS Name FROM person_per AS a LEFT JOIN person_custom pc ON a.per_id = pc.per_ID WHERE pc.~custom~ LIKE '%~value~%' ORDER BY per_LastName

    Regards
    Thorsten

     
  • I just wanted to bump this thread, as this discussion made a lot of sense to me. I do have a question as well.

    Is baptism date a custom field only? That seems to be one of the most important pieces of information a church might want to have on record... when they baptized someone. Seems a lot more important than friend date, for example. Just thinking out loud.

     
  • Michael Wilt
    Michael Wilt
    2012-12-19

    The friend date was created to help with tracking visitors, which is an important membership database function in any religion. Baptism date is more of a record-keeping function that makes sense to customize for each church.

    We have shied away from including religion-specific features and record-keeping that is not associated with day-to-day operations. One area where this creates a bit of a conflict is with deceased members. I have been thinking about an elegant way to archive information about deceased members, so the records are still available but don't pop up in the middle of day-to-day operations.

    Mike

    Michael Wilt
    ChurchInfo Team Leader

     
    • Doug
      Doug
      2012-12-19

      " I have been thinking about an elegant way to archive information about deceased members..."
      We have created a Deceased Family and set the properties so it does not show up on other functions. When a Person passes, we transfer them to this Deceased Family while retaining the source Family so all associated information is still available (Pledges, Payments, etc...).
      Probably does not meet the elegant requirement but it's a quick solution that works for us.
      Great efforts on this application BTW...
      Thanks!

       
  • Eear
    Eear
    2014-03-09

    I'm confused by why, what I believe are standard, SQL wildcards do not function. If a person had a custom text filed called, "How Joined" with the field text set to, "Joined after attending with a friend" then in the Query view, Custom Search, Custom field the user should be able to select, "How Joined" and in the field value type "%Joined after%" and get a hit on the text. A % sign is supposed to be a standard SQL wildcard. Like wise if the Custom Field were a text string with a date set to exactly, "2012-07-22" then a search with the Field Value set to, "2012------" should also return a result as - is supposed to be a standard SQL character wildcard.

    Also why don't Pulldown Lists, Boolean True/False, Date fields and other specific field types switch the field value to the field type of their given field? I don't understand why a person would have to type for fields like these? As of right now I can search for exact text in a Text Field by copying and pasting the exact string, but for a Pulldown List I copy and paste the exact text and get 0 hits.

    I've tried copying a functioning Query from a different type of Query search and replacing bits in free-text query, and haven't had much success. I've attempted to use all the SQL examples on this post in the free-text query with no success. I am very new to using SQL, and it seems very well documented on sites like w3schools, but right now I'm at a loss for how to use the Query system for custom fields.