Breaking apart a single text field.

[dondawg]

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

[danjacoby]

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.

[dondawg]

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

[dondawg]

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.