3

I want to create a search using searchkit that identifies contributions (payments) with memberships, which are connected using the civicrm_membership_payment table.

I actually want to list the contributions of a type Membership Payment that are NOT connected to a membership.

3 Answers 3

2

You could use Contribution -> Left Join line items where entity_table = civicrm_membership

Where contribution FT= ABC and line_item.id IS NULL

enter image description here

4
  • 1. Are you saying I can add table joins in searchkit even if they're not listed or directly supported? 2. What do you mean by 'add a MembershipPayment file in Civi/API4' - are you talking about a custom extension? Or a PR?
    – Alan Dixon
    Feb 13 at 22:56
  • I think core won't accept PR for MembershipPayment API4 as there was a discussion in past about deprecating the civicrm_membership_payment and participant_payment table over line_item. Going forward it will be via line item. You could implement that using custom extension. Or you could use line item table to joining to contribution Feb 14 at 16:14
  • Yes, this is now almost the right answer. If you remove the 'add a file' suggestion and just have the picture and description of using the line item, then I'll accept as correct.
    – Alan Dixon
    Feb 15 at 14:56
  • Done, i have updated my answer Feb 15 at 14:59
2

It looks like the API was missing to allow Contributions to join with Memberships. Here is a PR to add it, which allows you to do this in SearchKit:

enter image description here

3
  • Confirmed, that solves my need, thanks. I'd say that's a good sign for searchkit that it was that simple a PR.
    – Alan Dixon
    Feb 14 at 14:58
  • Unfortunately that PR was rejected because the civicrm_contribution_membership table is being (sloooowly) phased out.
    – Coleman
    Feb 14 at 15:55
  • But fortunately, matt's comment set me on the one true path of enlightenment.
    – Alan Dixon
    Feb 14 at 16:08
1

As per @mattwire's comment in @Coleman's PR, the Right Way to identify a membership payment is via the line item, when the entity_table is "civicrm_membership" and then the membership is the entity_id.

i.e. for my purposes: join the contribution table to the line items, and find contributions that have no civicrm_membership entries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.