3

Alright, maybe what I am trying to do is not possible, but before I rule it out, I thought I would ask (usually anything is possible if approached correctly!).

First, the goal. I am using a multipicklist as settings in one (settings) object for when I am running a class and searching against another (course) object. The picklists attached to each one are identical. I would like to receive back all of the records from the course object that are identified as the same value from it's picklist against the settings objects picklist. Pictures for clarification :)

Settings object (chosen for the semester field used in query): enter image description here

Course object with exact same values(searching Semesters Offered using semester from settings object): enter image description here

Now, I don't care how I get to my objective. if I have Fall selected, I would like to receive back all courses that have fall, (Fall, Fall A, Fall b) like a wildcard. However, it seems I can not use "LIKE" on a multipicklist, so that does not seem to be an option. INCLUDES is though, so I am using that for right now.

And, I do know that if I have an "INCLUDES" set up like this: INCLUDES('Fall','Fall B','Fall C') -- See full query:

for(Course__c y : [select ID, Name, Credits__c, Total_Price__c, 
Semesters_Offered__c from Course__c WHERE (Semesters_Offered__c 
INCLUDES('Fall','Fall B','Fall C')  AND Id = : pa.Course__c)]){
                     courseList.add(new cCourse(y));}

then I will get the results I want (find courses that have Fall, OR Fall A, OR Fall B).

However, I would really rather not hard code it, that is just a bad idea...

So I tried to work with a .join like this:

public string semester = [SELECT Semester__c FROM Registration_Settings__c WHERE Name = 'Registration Settings'].Semester__c;
 string[] values = semester.split(';',0);
 string semesters = string.join(values, ',');

However, this will not run in my query?:

for(Course__c y : [select ID, Name, Credits__c, Total_Price__c, 
Semesters_Offered__c from Course__c WHERE (Semesters_Offered__c 
INCLUDES(:semesters)  AND Id = : pa.Course__c)]){
                     courseList.add(new cCourse(y));}

I thought because maybe I am missing the apostrophes?

My debug log on this string looks like this: enter image description here

So my join seems to doing exactly what I want (removing the AND ';' and inserting and OR ',') but why oh why is it not running, is it the apostrophes, is it something else?

So I tried to fix the apostrophe issue with something like this and it also fails, which I just can not figure out why:

    public string getSemesters(){
    if(semester=='Fall'){
            semesters='\'Fall\',\'Fall A\',\'Fall B\'';
            return semesters;}
    else
    return null;} 

it outputs:

enter image description here

which seems 100% correct to me, it has the right syntax for the query anyways ('Fall','Fall A','Fall B') or so I think. However, this too will also not work in the query, why?

Ultimately I would love to be able to use the .join after looking at my settings, and not have to touch the code again, any ideas?

Thank you so much in advance for looking at this, and I apologize for all of the questions within the questions. It seems like the more I learn, the less I know. Again, thank you though, I appreciate your time! Nic.

2

you can use dynamic query for that, all you need to do is generate string with query and then use Database.query() method.

Follow sample code below:

String semesters = '\'Fall\',\'Fall A\',\'Fall B\''; // You can modify this string
String query = 'SELECT ID, Name, Credits__c, Total_Price__c, Semesters_Offered__c 
FROM Course__c 
WHERE (Semesters_Offered__c INCLUDES(' + semesters + ') 
AND Id = \'' +  pa.Course__c + '\')'; 

for(Course__c y : Database.query(query)){ 
    courseList.add(new cCourse(y));
}

If you would like to use String.join() method, in your case, you would ned to do this in following way:

String semesters = '\'';
semesters += String.join(list, '\',\'');
semesters += '\'';

You can create method to reuse code:

public String returnSemesters(List<String> strings){
    String semesters = '\'';
    semesters += String.join(list, '\',\'');
    semesters += '\'';
    return semesters;
}
| improve this answer | |
  • I have been playing around with code like that since I posted this. I feel this is the right path as well, however, I can not seem to make this work. I ran your code exactly as you show, and this is the response: System.QueryException: expecting a colon, found 'a01G000000HJmINIA1' Error is in expression '{!step1}' in component <apex:commandButton> in page studentregistration: Class.RegistrationController.step1: line 365, column 1 That is where the query is at, so it is losing it there, looks like due to a colon. I am tinkering with it now, hopefully I can make it go. Thank you again. – Nic McBride May 31 '15 at 20:39
  • I just edited code, I forgot about apostrophes surrounding pa.Course__c. – Kamil Grzywacz May 31 '15 at 20:45
  • Ok, it works perfect now per what you wrote. Thank you so much. I am going to mark this is as answered. One other question though pertaining to the question at hand. How to get it working with the .join method. So when you assign semesters that string, it works flawlessly, which I am assuming is because it includes the ( ' ) apostrophes. When I do the .join, do you know how to make it set it with apostrophes as well? Or is there a better method I could call for this? – Nic McBride May 31 '15 at 21:00
  • @NicMcBride I've updated answer with example of using String.join(). Please, check it out. – Kamil Grzywacz May 31 '15 at 21:14
  • Ok, this looks genius, thank you again! Since you are quick with this, and I just learning as we go, this is the error I am getting : extent:[ApexClass] id:[01p16000004IPdzAAG] name:[RegistrationController] line:[10] problem:[unexpected token: '+='] That is at the second line @ semester+=String.join(list, '\',\''); – Nic McBride May 31 '15 at 21:23

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.