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!