-1

I have customobject with groups__c text field which hold id of different public groups Id with ","

groups__c = 0011U00000oyv2AQAQ,0011U00000oyv2BQAQ

I'm using this field in where clause

List<String> groupIds = new List<String>();
for(GroupMember gm :[SELECT Id,GroupId,UserOrGroupId FROM GroupMember WHERE UserOrGroupId = :userId]){
    groupIds.add(gm.GroupId);
}

for(CustomObj__c obj : [SELECT Id, Name FROM CustomObj__c where groups__c IN :groupIds])

here it returns records of CustomObj__c for only 0011U00000oyv2AQAQ UserOrGroupId

Is it possible to query so that it will return for all mention groupIds in groups__c field?

2
  • 1
    have you considered normalizing the DB so you don't have to save multiple IDs in one field? What you want to do is incredibly hard for how simple the request is. – Sebastian Kessel Apr 6 '20 at 16:37
  • Could you elaborate more on your suggestion? As per my reqirement we have specific record on custom object which I want to pull in Flow. This record should only available to users who are part of perticular public group. so if Record A have 3 public group id saved in field, that record pulled for that public group user when he runs flow. – Watson Apr 9 '20 at 1:33
4

This is because where groups__c IN :groupIds will only return against hard matches of each single value, instead of checking against a LIKE operator. Simply change your groupIds.add(gm.GroupId); line to groupIds.add('%' + gm.GroupId + '%');, and your IN clause to a LIKE, and it should function as you expect.

List<String> groupIds = new List<String>();
for(GroupMember gm :[SELECT Id,GroupId,UserOrGroupId FROM GroupMember WHERE UserOrGroupId = :userId]){
    groupIds.add('%' + gm.GroupId + '%');
}

for(CustomObj__c obj : [SELECT Id, Name FROM CustomObj__c where groups__c LIKE :groupIds])
5
  • 1
    I don't think I ever tried this, but can you use a List against a LIKE comparator? – Sebastian Kessel Apr 6 '20 at 16:42
  • 1
    @SebastianKessel You absolutely can. :) – Drew Kennedy Apr 6 '20 at 16:43
  • 3
    I just learned something new – Sebastian Kessel Apr 6 '20 at 16:43
  • @DrewKennedy Thank you so much for answer. I'm still confused on groupIds.add('%' + gm.GroupId + '%'); part, I'll try system.debug to see output of groupIds if that make sense to me. – Watson Apr 6 '20 at 18:04
  • 1
    @Ghosty With the LIKE clause you can use % to act as a wildcard. '%' + gm.GroupId would represent ends with, gm.GroupId + '%' would represent starts with and '%' + gm.GroupId + '%' respresents contains. – Drew Kennedy Apr 6 '20 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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