AnsweredAssumed Answered

Single Field Splitter

Question asked by sparknet on Jul 11, 2018
Latest reply on Jul 11, 2018 by philmodjunk

I built a database for a client with a People table and a Comments table, related by a people id number. Bretty basic stuff. The idea was that Sales Reps would enter a new comment for each People record they contacted. Over the course of a year they would have several comment records in the Comment table for many people in the People table. We would then have a  report that searches on the comment modification field so we can show all contacts made by a given Sales Rep within a given date range.


Well it turns out that the sales reps have not been using it that way. They make one comment record per year and populate that one field with Date, Sales Rep, Comment. Like this (a single field in a single record):


Jul 1'18-AB-Here are some notes

July 15'18-JT- Additional notes

Aug 23/18 EP - Some more notes for you.


So Date, then Rep then note. Add to this, it's inconsistent entered at best and the client doesn't want a change to the way the comments are entered. My client still wants the report on contacts to work, but the modification date I was counting on is now useless since it is not used that way. Is there a way I can parse out the dates using a calculation field, and then search on this field to fix the existing report? Rather than just telling them the report can't work, I though I'd put it out there first.