I hope someone can help with a little issue I'm having.

 

I am running a report based around the contact trail for our order processing software. It's a log of notes relating to the customer service history of an order. The full data for each of the contacts is made up of a combination of two tables - T_ContactHistory + a table relating to the type of contact it was ( telephone, Email, Letter, Notes etc). I can happily display a list of all contacts, with their contact ID's, the date they were created, the user that created them and the type of contact they are.

 

The problem comes when I want to display the content of the contacts as the body of the contact trail note is stored in the table relating to the contact type and not T_ContactHistory. If I add the "body" field from the telephone table it only then displays those contacts of telephone type. The same applies when I try and add the body filed from any of the other tables.

 

If I add all of the body fields to the report, it then contains no data as it looks for a contact type with a value in each of these fields. This is clearly impossible as the notes are only stored in the relevant body field.

 

So what I would like to do is display a text field if possible that contains some kind of formula to display the body field appropriate to that type of contact. I.e it looks at the contact type and then returns the appropriate body field, otherwise leaves the field blank.

 

Please let me know if this is at all possible as I would like to be able to retrieve a whole history of all contacts raised by a particular user on a particular date.