+ Start a Discussion
Greg RohmanGreg Rohman 

SOQL Variable Field Name

Hello.

I'm using Dynamic SOQL to generate a dynamic query based on a field name that includes the year. For example, I have a field called "Sales_Goal_2014__c" on the User object, and will create subsequent fields for 2015 and so on. The Dynamic SOQL to obtain this value is functional, and is as follows:
// Determine the date for the snapshot (last day of previous month)
Date firstDayOfMonth = date.today().addMonths(-1).toStartOfMonth();
reportDate = firstDayOfMonth.addDays(Date.daysInMonth(firstDayOfMonth.year(), firstDayOfMonth.month()) - 1);
reportYear = reportDate.year();
String goalFieldName = 'Sales_Goal_' + string.valueOf(reportYear) + '__c';

String userQuery = 'SELECT Id,Name,' + goalFieldName + ' FROM User ORDER BY LastName ASC';
Map<Id,User> userMap = new Map<Id,User>((List<User>)Database.query(userQuery));
I now would like to loop on the collection of users and obtain the value of the dynamically-generated field name? I know the following code doesn't work, but it's pseudo-code for what I am looking to accomplish
for (User u : userMap.values()) {
	Decimal tempSalesGoal;
	tempSalesGoal = u.(goalFieldName);
	// rest of code here
}
Thanks in advance.

-Greg



Best Answer chosen by Greg Rohman
James LoghryJames Loghry
You can accomplish via the .get(String field) method on the record.

For instance:

for(User u : userMap.values()){
    String goal = u.get('Sales_Goal_' + String.valueOf(reportYear) + '__c');
}


All Answers

James LoghryJames Loghry
You can accomplish via the .get(String field) method on the record.

For instance:

for(User u : userMap.values()){
    String goal = u.get('Sales_Goal_' + String.valueOf(reportYear) + '__c');
}


This was selected as the best answer
Greg RohmanGreg Rohman
Hi James.

Thanks for the tip. The documentation states that the get() method returns an object, which is also consistent with the "Illegal assignment from Object to Decimal" error that I am receiving when using the code you suggested. Any ideas?

-Greg
Greg RohmanGreg Rohman
Hi James.

Nevermind! I found in the documentation that when working with sObjects, I need to reference the data type in parentheses. Something like this is functional:
for(User u : userMap.values()){
    Decimal goal = (Decimal)u.get('Sales_Goal_' + String.valueOf(reportYear) + '__c');
}
Thanks again for pointing me in the right direction.

-Greg