Searching Payments in Civi
In the deep dark past, when CiviCRM was first written, it was assumed that one ‘Order’ (Contribution) could only have one payment. How wrong that was! However, it’s an assumption that plagues us to this day, despite much work having been done to remedy it.
One particular challenge is searching for the details of payments. For example the 'check number' field exposed in advanced search searches for the check_number on the contribution record but may not find the contribution you are looking for. This is because the contribution record has, at best, a 'cheap copy' of the check_numbers from the payment records and the right place to search is the payment records. The search results can be confusing - searching for check ‘45’ might not return a result if the value stored is ‘55,45’ and it is searching for an exact or start-of-field match. The advanced search code is brittle and hard to maintain and we try not to add new features/ fixes to it (and to require a heavy barrage of tests for any changes to it).
However, the new search kit is more flexible and maintainable and under active development. In this blog I’m going to go through creating a search for payments using search kit. This blog is screen shot heavy and is intended for people to work their way through. There are also webinars out there on search kit if your preference is to watch videos.
My final dashlet is shown on the right hand side of this home page
It’s always best to use the latest CiviCRM version when trying anything in search kit as it has been moving quickly. I’m using the code soon to become the 5.41 rc but the features I’m documenting should work in the current stable (5.39.0 as of writing)
As long as you have search kit enabled you can find it in the search menu. When you create a search kit search the trick is to figure out what entities you want - in this case I want Contributions and any payments connected to them. You can see the payments are called ‘Contribution Financial Trxns’. There are other types of ‘Financial Trxns’ saved in CiviCRM so it’s worth specifying that you want payments - at this stage we are asking for “Contributions with financial transactions that are payments”
You can add in various fields - but keep an eye on where they are coming from. In this screenshot you can see that when I go to add a check number I can add it from the contribution table and/or from the payments table
Here I have added both - what you are looking at are 2 contributions (id = 5 and id = 95). The second contribution has 2 payments - both checks with the check numbers ‘45’ and ‘55’. The second check number column shows that both have been squished into the contribution check_number field as a weird work around of sorts. However, we don’t need that field so I will remove it.
Next I have the date field
It’s a bit loooonnng in the table. However, a neat new feature which is in 5.41 is the ability to specify field transformations. So I’m going to specify ‘Date Only’
And I’m now saving the search looking like
The next step is to add a table display
This is my chance to rename all those crazy long column names…. You can see header has been rewritten to ‘check number’ here
The next part requires the form builder extension to be enabled. From the saved searches screen you can add a form.
You will want to add a title and a ‘placement’
I’m also going to choose add to dashboard. Add to dashboard displayed to show as dashlets on the CiviCRM home page. I might also want to add a pagements tab to a the contact summary screen - in which case I would choose ‘Add to contact summary’ and then use the contact layout editor extension to configure it.
But the thing I really want in this search is the ability to find my payments quickly - so I’m going to add some filters from the second tab - you can see I’ve dragged check number over
As soon as I save it a link appears to go to the page next to where it said ‘Save’ before.
And now I have a search that will filter on the fly by my check number. You can add multiple fields. In this example I’ve added in the transaction ID from the contribution record as well. For most sites this is not useful but some payment processors may populate this with the ‘anticipated’ transaction id before the payment is received so for some sites it might be useful.
Comments
Very nice tutorial Eileen, thanks!
Thanks for sharing this, Eileen, I think Search Builder and is going to quickly become a really powerful feature.
Now this technology is available for admins/implementors/power-users, I think the next bottleneck will be the lack of an entity relationship diagram or diagrams that would enable people understand how to construct the queries with search builder, as that's not obvious unless you're very familiar with the internals. Tutorials like this are a great way to explain some of that with practical and specific examples, so thanks.
Just noting https://github.com/eileenmcnaughton/paymentsearch