Help! I am looking for a way to either export multiple associate text strings from a single text field that are associated with a unique contact Id number. I the past I have used a single field to first put in a date of communication with a person then the call notes associated with that date. Over time of course this single text field has become rather bulky, plus I can not do simple searches in the field for specific dates or ranges of dates. What I am thinking now is that I should have a relational database called Call Logg which has a relation to the unique contact id number and fields such as date of the call, time, call notes, and other important information. I would then use a portal in the contacts database to view the list of calls made over time. Does anyone know if you can break apart a single text field this way by using a script to insert some sort of character or line break before each occurance of a date so I can use excel or something to clean up the record and then reimport the data into multiple records for each contact?

Thanks, Don

Duplicate post!!!

Please, never post anything twice. Choose one forum, and post there. Don't worry if it doesn't exactly fit; that happens sometimes.

Sorry about the duplicate post. I think I may have begun a solution but still need help. I decided to create some calculation fields to see if that would work using the substitute function.

So first I set up 10 global fields. In each field I put in a return character and a number from 0 to 9 and named each field respectively return0, return1, etc.

Then I created 10 calculation fields. Each calculation field is set to substitute first the original text field with dates first and then the call notes. This field is named: notes individual. So the first calculation field named text1 = Substitute(notes individual, return1, return & return & "1").

My hope here was that it would search and replace any text which started with a number 1 (from a date in the text field) and was preceded by a return character with two return characters and the original number 1.

The remaining 9 calculation fields would subsequently substitute and replace the previous calculation field until all date occurrences in the original notes individual field would have 2 return characters before them.

I thought this would help to break out the text field better because so of the original notes individual records already had a date then the notes containing a return.

It seemed to me I could then do a substitute for only double return characters into something that would allow me to export as an individual date and notes item.

The problem I am having now is that with this current set of calculations I am not getting the double returns I was hoping for. The calculations actually resolve in just concatenating the two separated items in the text field. For example:

1/05/1999 contacted and updated fax number.

2/12/1999 faxed summary report

results as:

1/05/1999 contacted and updated fax number.2/12/1999 faxed summary report

Does anyone know why I can not get a double return so it looks like this:

1/05/1999 contacted and updated fax number.

2/12/1999 faxed summary report

My initial trial did a single substitute for single to double return characters and that worked. The only issue there was that it did it for every occurrence of a return character which I do not want. I only want to break out the original text field at each occurrence of a date.

Thanks,

Don

Found my problem. I now am able to get the double returns between each occurrence of a date in a single text field. Now all I need to know is how to break out the single text field into multiple text fields at the double return.