Answers.Body: <p>Join on Group Subscription Histories. It's Action Date.</p>
  <p>EDIT: This version below should work with older versions of civi too.</p>
  <p><a href="https://i.sstatic.net/IYzaBB0W.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/IYzaBB0W.png" alt="enter image description here" /></a></p>
  <pre><code>{
    &quot;version&quot;: 4,
    &quot;select&quot;: [
      &quot;id&quot;,
      &quot;sort_name&quot;,
      &quot;Contact_SubscriptionHistory_contact_id_01.group_id:label&quot;,
      &quot;Contact_SubscriptionHistory_contact_id_01.status:label&quot;,
      &quot;GROUP_CONCAT(DISTINCT Contact_SubscriptionHistory_contact_id_01.date) AS GROUP_CONCAT_Contact_SubscriptionHistory_contact_id_01_date&quot;
    ],
    &quot;orderBy&quot;: {},
    &quot;where&quot;: [],
    &quot;groupBy&quot;: [
      &quot;id&quot;,
      &quot;Contact_SubscriptionHistory_contact_id_01.group_id&quot;,
      &quot;Contact_SubscriptionHistory_contact_id_01.status&quot;
    ],
    &quot;join&quot;: [
      [
        &quot;SubscriptionHistory AS Contact_SubscriptionHistory_contact_id_01&quot;,
        &quot;INNER&quot;,
        [
          &quot;id&quot;,
          &quot;=&quot;,
          &quot;Contact_SubscriptionHistory_contact_id_01.contact_id&quot;
        ]
      ]
    ],
    &quot;having&quot;: []
  }
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-31T15:59:08.330Z
Answers.Text: Fantastic, thanks a lot. Would never have found that!
Answers.UserDisplayName: Mike Wilson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-31T16:15:22.903Z
Answers.Text: Sorry @Demerit - but how do i get the join date for just that group? When I do as you say, it pulls action dates for all the groups the person has joined. So I get tons of results!
Answers.UserDisplayName: Mike Wilson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-31T16:55:11.867Z
Answers.Text: You'll need to do a "group by", but also it looks like by default it doesn't join on contact id. I'll update my answer with a screenshot.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-06-03T08:57:16.650Z
Answers.Text: Thanks Demerit. Unfortunately I am not able to import your code. I tried to rebuild it but I dont have the value or field options available.
Answers.UserDisplayName: Mike Wilson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-06-03T09:03:55.087Z
Answers.Text: https://i.postimg.cc/NFRjbzhy/Screenshot-2024-06-03-095438.png
Answers.UserDisplayName: Mike Wilson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-06-03T13:51:04.790Z
Answers.Text: Ok you're on an older version of civi. I'll update the answer.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-06-04T10:17:15.043Z
Answers.Text: Thanks that worked!
Answers.UserDisplayName: Mike Wilson
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-31T14:59:30.103Z
Answers.LastActivityDate: 2024-06-03T13:54:42.233Z
Answers.LastEditDate: 2024-06-03T13:54:42.233Z
Answers.UserDisplayName: Demerit
Body: <p>I've created this searchkit search of people who have been added to a Campaign Newsletter group.  I want to include the date they were added to the group, but I cant seem to work out how to do it. I'm sure its easy. Any help appreciated. Thanks</p>
  <pre><code>  &quot;version&quot;: 4,
    &quot;select&quot;: [
      &quot;id&quot;,
      &quot;title&quot;,
      &quot;Group_GroupContact_Contact_01.display_name&quot;
    ],
    &quot;orderBy&quot;: [],
    &quot;where&quot;: [
      [
        &quot;title&quot;,
        &quot;CONTAINS&quot;,
        &quot;Campaign newsletter&quot;
      ]
    ],
    &quot;groupBy&quot;: [],
    &quot;join&quot;: [
      [
        &quot;Contact AS Group_GroupContact_Contact_01&quot;,
        &quot;INNER&quot;,
        &quot;GroupContact&quot;,
        [
          &quot;id&quot;,
          &quot;=&quot;,
          &quot;Group_GroupContact_Contact_01.group_id&quot;
        ],
        [
          &quot;Group_GroupContact_Contact_01.status:name&quot;,
          &quot;=&quot;,
          &quot;\&quot;Added\&quot;&quot;
        ]
      ]
    ],
    &quot;having&quot;: []
  }```
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-05-31T20:03:24.310Z
Comments.Text: Mike - unless there is something preventing you, pls add your comments as Comments, not as Answers so moderators don't need to clean up. thx
Comments.UserDisplayName: petednz - fuzion
Tags: <searchkit>
Title: Date added to Group
ThreadText: # Date added to Group
  
  (Asked by: username_0 on 2024-05-31)
  
  I've created this searchkit search of people who have been added to a Campaign Newsletter group. I want to include the date they were added to the group, but I cant seem to work out how to do it. I'm sure its easy. Any help appreciated. Thanks
  
  ```
    "version": 4,
    "select": [
      "id",
      "title",
      "Group_GroupContact_Contact_01.display_name"
    ],
    "orderBy": [],
    "where": [
      [
        "title",
        "CONTAINS",
        "Campaign newsletter"
      ]
    ],
    "groupBy": [],
    "join": [
      [
        "Contact AS Group_GroupContact_Contact_01",
        "INNER",
        "GroupContact",
        [
          "id",
          "=",
          "Group_GroupContact_Contact_01.group_id"
        ],
        [
          "Group_GroupContact_Contact_01.status:name",
          "=",
          "\"Added\""
        ]
      ]
    ],
    "having": []
  }```
  
  ```
  
  **Tags:** searchkit
  
  > **Comments:**
  > **username_1**: username_0 - unless there is something preventing you, pls add your comments as Comments, not as Answers so moderators don't need to clean up. thx  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2024-05-31. Score: 2 | Accepted answer
  
  Join on Group Subscription Histories. It's Action Date.
  
  EDIT: This version below should work with older versions of civi too.
  
  [![enter image description here](https://i.sstatic.net/IYzaBB0W.png)](https://i.sstatic.net/IYzaBB0W.png)
  
  ```
  {
    "version": 4,
    "select": [
      "id",
      "sort_name",
      "Contact_SubscriptionHistory_contact_id_01.group_id:label",
      "Contact_SubscriptionHistory_contact_id_01.status:label",
      "GROUP_CONCAT(DISTINCT Contact_SubscriptionHistory_contact_id_01.date) AS GROUP_CONCAT_Contact_SubscriptionHistory_contact_id_01_date"
    ],
    "orderBy": {},
    "where": [],
    "groupBy": [
      "id",
      "Contact_SubscriptionHistory_contact_id_01.group_id",
      "Contact_SubscriptionHistory_contact_id_01.status"
    ],
    "join": [
      [
        "SubscriptionHistory AS Contact_SubscriptionHistory_contact_id_01",
        "INNER",
        [
          "id",
          "=",
          "Contact_SubscriptionHistory_contact_id_01.contact_id"
        ]
      ]
    ],
    "having": []
  }
  
  ```
  
  > **Comments:**
  > **username_0**: Fantastic, thanks a lot. Would never have found that!  
  > **username_0**: Sorry @username_2 - but how do i get the join date for just that group? When I do as you say, it pulls action dates for all the groups the person has joined. So I get tons of results!  
  > **username_2**: You'll need to do a "group by", but also it looks like by default it doesn't join on contact id. I'll update my answer with a screenshot.  
  > **username_0**: Thanks username_2. Unfortunately I am not able to import your code. I tried to rebuild it but I dont have the value or field options available.  
  > **username_0**: https://i.postimg.cc/NFRjbzhy/Screenshot-2024-06-03-095438.png  
  > **username_2**: Ok you're on an older version of civi. I'll update the answer.  
  > **username_0**: Thanks that worked!  
  
  ---
  
  