Hi all,

Does anyone know how to write a SOQL which has a separate maths formula for individual fields?  I'm trying to get average values for several different fields in the same query, but I don't want to include any zero value records in the calculation.  Something like this:

Select AVG(Field1) only where Field1>0, AVG(Field2) only where Field2>0, AVG(Field3)only where Field3>0, AVG(Field4) only where Field4>0  etc etc

I thought of getting the zeros to read null by using a formula in the field construction itself, but the trouble with that is that my Google Charts don't work when even one of the summary fields are null.  I'm trying to query the database to do some google charts, but trying to incorporate calculations into the coding is a little bit beyond me.

Hope someone can help.

Hi there

Though Captain Obvious gave me a good resolution for my earlier question on this issue, I now see I need to reframe my requirement so that I can run reports from the data collected. So I need to rewrite the code.  I hope someone can help.

The basic set up:  we have a class object, a test object and a results object:

1. Each record in my class object stores a series of objectives (Objective 1, Objective 2, Objective 3 etc).  
2. My test object is set up with the same fields so that when a new test record is created and the user choses a particular class record from a lookup field, the same Objective 1, Objective 2, Objective 3, etc fields are automatically populated with their text.  
3. In the individual test record the user will then tick only those objectives which are relevant for the test.   Some tests will only be examining Objectives 1 and 3, other tests may be examining only Objective 4,  i.e. any combination of any number of objectives.
4. In my results object I want only the objectives which have been ticked in the relevant test record to appear on the individual results record (so that the candidate can't see the objectives which were not ticked), and we don't know how many will be ticked.  Each of these must appear as individual fields so that next to each objective there will be an associated field in which the examiner can put a percentage grade (I can then run reports from each piece of data).  So I need a formula for whichever is first ticked populates Objective A, whichever is second ticked populates Objective B, and if no more ticks no further Objective is populated.  I don't know if I can say something like: where the first Objective which is ticked (in the test record) create a field (in the results record) called Objective A and populate it with the text associated with that ticked Objective.  If there is another Objective which  is ticked then create a field called Objective B and populate it with the text associated with that ticked Objective, and so on.  If there are no further ticks then no more fields to be populated.

• I realise that we can't ask for fields to be created dynamically like that, they must already exist right?  So I need to perhaps create some fields called Objective A, B, C with corresponding Grade A, Grade B, Grade C fields (for % marks) and if not all of them are populated it means that there were fewer ticks in the corresponding test record.  I know it doesn't look very neat if there are Objective D, Objective E unpopulated, but I'm not sure if there is a way around that.

• I will also need to marry back the results entered in the Grade A, B, C etc to their original Objective 1, 2, 3, so that when I run a report it will recognise how that individual candidate did on Objective 1 on all tests, on objective 2 on all tests etc, as well as how all candidates did on each objective.  This way we can see if there is a problem with how objective 1 is being communicated.

The existing formula I have is for bringing the objectives from the test record into the results record by putting all objectives that are ticked collectively into one field:

& IF (Homework__r.Objective_6__c,Homework__r.Objectives_6__c,'')

IF (Homework__r.Objective_1__c,Homework__r.Objectives_1__c, '' ) 

& IF (!Homework__r.Objective_1__c,'', IF(OR(Homework__r.Objective_2__c,Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c), BR() ,'')) 

& IF (Homework__r.Objective_2__c,Homework__r.Objectives_2__c,'') 

& IF (!Homework__r.Objective_2__c,'', IF(OR(Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c), BR() ,'')) 

& IF (Homework__r.Objective_3__c,Homework__r.Objectives_3__c,'') 

& IF (!Homework__r.Objective_3__c,'', IF(OR(Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c), BR() ,'')) 

& IF (Homework__r.Objective_4__c,Homework__r.Objectives_4__c,'') 

& IF (!Homework__r.Objective_4__c,'', IF(OR(Homework__r.Objective_5__c,Homework__r.Objective_6__c), BR() ,'')) 

& IF (Homework__r.Objective_5__c,Homework__r.Objectives_5__c,'') 

& IF (!Homework__r.Objective_5__c,'', IF(OR(Homework__r.Objective_6__c),', ','')) 

If someone can help me amend this formula so that it works to populate separate fields I'd really appreciate it.  And then also recommend how I'd make sure the grade field is picked up as refering to the right Objective 1, 2 or 3 etc. Thanks in advance guys!

Hope someone can help.  Am writing a formula field in one object (B) where I want only the values associated with checkboxes on another object (A) to appear.  There may be one, two or even three checkboxes ticked in object A, so I would want some way of all the values which are ticked appearing in object B, but not the other values which are not ticked.

It seems that if I do an IF formula, I can only get the first ticked value in the field to appear, but not the other ticked values.  I tried using a "," separator so that a list of values would appear separated by a comma, but I couldn't get the syntax to work.  I would prefer to have each field labelled (Objective 1, Objective 2 etc), and was trying to think of a way for the field label itself not showing if the value was null, but I can't see a way to do that. This is my formula:

IF(Homework__r.Objective_1__c  = TRUE, Homework__r.Objectives_1__c,  IF(Homework__r.Objective_2__c  = TRUE, Homework__r.Objectives_2__c, IF(Homework__r.Objective_3__c  = TRUE, Homework__r.Objectives_3__c, IF(Homework__r.Objective_4__c  = TRUE, Homework__r.Objectives_4__c,  IF(Homework__r.Objective_5__c  = TRUE, Homework__r.Objectives_5__c, IF(Homework__r.Objective_6__c  = TRUE, Homework__r.Objectives_6__c, null))))))

Any help on this would really be appreciated.  Thanks!

Hope someone can help.  I am new to cloud/force.com.  I am trying to get text data from one object to show up in another object while it is in edit mode.  

Object A contains text fields (objectives).  In Object B we have a Lookup field to select a particular Object A record.  This matches a new B record to a particular A record.  Then we have formula fields for the objectives which automatically draw the text from the A record so that they can be seen in the new B record.  The idea of this is that the A record objectives are the same for every new B record associated with that A record (one to many relationship).  

I want the user to fill in the B record with new information and tick checkboxes while referencing the objectives that have been pulled in from the A record.  But the problem is that while we are in edit mode of the new B record, the user cannot see the objectives that will be populated from the A record until the record is saved.  Even after saving and populating those objectives fields, if we go back into edit mode to tick the checkboxes and fill in the information, we again can't see the objectives fields.

Anyone any ideas of how can I do this?  

I've just joined and wanted to start looking at database.com...but I can't find where to start?  Is it just not available yet...so what's the head start they were suggesting on the sign up page?

Hope someone can help.  Am writing a formula field in one object (B) where I want only the values associated with checkboxes on another object (A) to appear.  There may be one, two or even three checkboxes ticked in object A, so I would want some way of all the values which are ticked appearing in object B, but not the other values which are not ticked.

It seems that if I do an IF formula, I can only get the first ticked value in the field to appear, but not the other ticked values.  I tried using a "," separator so that a list of values would appear separated by a comma, but I couldn't get the syntax to work.  I would prefer to have each field labelled (Objective 1, Objective 2 etc), and was trying to think of a way for the field label itself not showing if the value was null, but I can't see a way to do that. This is my formula:

IF(Homework__r.Objective_1__c  = TRUE, Homework__r.Objectives_1__c,  IF(Homework__r.Objective_2__c  = TRUE, Homework__r.Objectives_2__c, IF(Homework__r.Objective_3__c  = TRUE, Homework__r.Objectives_3__c, IF(Homework__r.Objective_4__c  = TRUE, Homework__r.Objectives_4__c,  IF(Homework__r.Objective_5__c  = TRUE, Homework__r.Objectives_5__c, IF(Homework__r.Objective_6__c  = TRUE, Homework__r.Objectives_6__c, null))))))

Any help on this would really be appreciated.  Thanks!

Hope someone can help.  I am new to cloud/force.com.  I am trying to get text data from one object to show up in another object while it is in edit mode.  

Object A contains text fields (objectives).  In Object B we have a Lookup field to select a particular Object A record.  This matches a new B record to a particular A record.  Then we have formula fields for the objectives which automatically draw the text from the A record so that they can be seen in the new B record.  The idea of this is that the A record objectives are the same for every new B record associated with that A record (one to many relationship).  

I want the user to fill in the B record with new information and tick checkboxes while referencing the objectives that have been pulled in from the A record.  But the problem is that while we are in edit mode of the new B record, the user cannot see the objectives that will be populated from the A record until the record is saved.  Even after saving and populating those objectives fields, if we go back into edit mode to tick the checkboxes and fill in the information, we again can't see the objectives fields.

Anyone any ideas of how can I do this?  

I've just joined and wanted to start looking at database.com...but I can't find where to start?  Is it just not available yet...so what's the head start they were suggesting on the sign up page?