+ Start a Discussion
Danny HartleyDanny Hartley 

SOQL Query against Apex List Variable

Hello,

I am wondering if it is possible to filter a List of records based on a text field from another Apex List Variable.  I would like the variable parLevelsNeedingStock to grab Par Level records where the field 'Unique_Id_For_Warehouse_Product__c' is not in any of the records from the Apex List<> variable 'tfrs'.   Thanks in advance for any help.
 
        AcctSeedERP__Outbound_Inventory_Movement__c[] tfrs = [SELECT 
                                                                Id, 
                                                                Warehouse_To__c, 
                                                                AcctSeedERP__Warehouse__c, 
                                                                AcctSeedERP__Product__c,
                                                                Lot__c,
                                                                Warehouse_To_and_Product_ID__c 
                                                                FROM 
                                                                AcctSeedERP__Outbound_Inventory_Movement__c 
                                                                WHERE 
                                                                Inventory_Transfer__r.Warehouse_To__r.Category__c = 'Consignment'
                                                                AND
                                                                Inventory_Transfer__r.Transfer_Shipped__c = False
                                                                AND
                                                                AcctSeedERP__Type__c = 'Transfer'];
                                                          
        List<Par_Level__c> parLevelsNeedingStock = [SELECT 
                                                    ID
                                                    FROM 
                                                    Par_Level__c 
                                                    WHERE 
                                                    Unique_Id_For_Warehouse_Product__c 
                                                    NOT IN
                                                    : tfrs
                                                    AND
                                                    Difference_From_Par__c < 0
                                                    AND
                                                    Warehouse_Category__c = 'Consignment'
                                                    ];
Best Answer chosen by Danny Hartley
Alain CabonAlain Cabon
Hello,
AcctSeedERP__Outbound_Inventory_Movement__c[] tfrs = [SELECT 
        Id, 
        Warehouse_To__c, 
        AcctSeedERP__Warehouse__c, 
        AcctSeedERP__Product__c,
        Lot__c,
        Warehouse_To_and_Product_ID__c 
    FROM  AcctSeedERP__Outbound_Inventory_Movement__c 
    WHERE 
        Inventory_Transfer__r.Warehouse_To__r.Category__c = 'Consignment'
        AND
        Inventory_Transfer__r.Transfer_Shipped__c = False
        AND
        AcctSeedERP__Type__c = 'Transfer'];
		
      Set<String> myIds = new Set<String>();														
	  for (AcctSeedERP__Outbound_Inventory_Movement__c  tfr : tfrs) {
               myIds.add(tfr.Warehouse_To_and_Product_ID__c); 
      }	 
                                                          
      List<Par_Level__c> parLevelsNeedingStock = [SELECT 
              ID
              FROM  Par_Level__c 
              WHERE 
              Unique_Id_For_Warehouse_Product__c 
              NOT IN : myIds
              AND
              Difference_From_Par__c < 0
              AND
              Warehouse_Category__c = 'Consignment'
              ];
Set<String> myIds = new Set<String>(); or  Set<Id> myIds = new Set<Id>();

Alain

All Answers

Alain CabonAlain Cabon
Hello,
AcctSeedERP__Outbound_Inventory_Movement__c[] tfrs = [SELECT 
        Id, 
        Warehouse_To__c, 
        AcctSeedERP__Warehouse__c, 
        AcctSeedERP__Product__c,
        Lot__c,
        Warehouse_To_and_Product_ID__c 
    FROM  AcctSeedERP__Outbound_Inventory_Movement__c 
    WHERE 
        Inventory_Transfer__r.Warehouse_To__r.Category__c = 'Consignment'
        AND
        Inventory_Transfer__r.Transfer_Shipped__c = False
        AND
        AcctSeedERP__Type__c = 'Transfer'];
		
      Set<String> myIds = new Set<String>();														
	  for (AcctSeedERP__Outbound_Inventory_Movement__c  tfr : tfrs) {
               myIds.add(tfr.Warehouse_To_and_Product_ID__c); 
      }	 
                                                          
      List<Par_Level__c> parLevelsNeedingStock = [SELECT 
              ID
              FROM  Par_Level__c 
              WHERE 
              Unique_Id_For_Warehouse_Product__c 
              NOT IN : myIds
              AND
              Difference_From_Par__c < 0
              AND
              Warehouse_Category__c = 'Consignment'
              ];
Set<String> myIds = new Set<String>(); or  Set<Id> myIds = new Set<Id>();

Alain
This was selected as the best answer
Danny HartleyDanny Hartley
@Alain Thanks for the help!  Worked great!
Matheus AbudMatheus Abud
Hey guys, I think I'm going through something familiar, but I can't seem to figure it out..
What I need to do is: I have a custom object (choices__c) where I'm building a VFP within it. In this VFP I have to query data from another custom object (products__c). I just can't query the data. After that I'll still have to put it in a dropdown list in order for the client to choose the products and start adding it to order.
Can you guys give me some hints?

See below my VF page:
User-added image

See below my choice controller:
So, my list is working fine. When I changed it for the 2 lines in line 6 and 7 in order to query the data in put in the list, I can't get done.

User-added image
 
Danny HartleyDanny Hartley
Database.query takes a query in the form of a string, which you seem to know, but your String should not be enclosed with brackets.  I believe it should look like this:

String querychoices = 'SELECT Name From Product__c';
List<Product__c> choices = Database.query(querychoices);
Matheus AbudMatheus Abud
Danny, thanks for the help. However, I still get an error and can run this...I'm missing some detail...
In line 9 below, I get the error: Invalid loop variable type expected product__c was String 
for (String c: choices) {
So, I changed line 9 for product__c type, such as:
for (product__c c: choices) {
And now, I'm getting this message in line 11 (body of the for loop): Constructor not defined: [System.SelectOption].<Constructor>(product__c, product__c)
I need to query this data from sObject (product__c, especifically its names) and insert them into a droplist button in visualforce page, but I can't seem to figure out my controller issue.
public with sharing class Choice 
{
    //public String querychoices {get; set;}	    
    // public String Choice()
    public Choice() 
    {
        String querychoices = 'SELECT name FROM product__c';
        List<product__c> choices = Database.query(querychoices);
        for (String c: choices) 
        {
            this.allChoices.add(new SelectOption(c,c));
        } 
        /*CREATING RANDOM OPTIONS - IT WORKS!
        List<String> choices = new List<String>();
		for (integer i=0; i<5; i++)
        {
            choices.add('apple_'+i);
        }
        this.allChoices = new SelectOption[]{};
        */        
    }

    public SelectOption[] allChoices 
    {
        public get;
        private set;
    }
}