number2text relationship problem

[cpaul]

i am trying to link two tables where one ID field information "is contained" in the text field of another table. for instance, a company has reviewed information in another record and I want to have a history list of who has seen this info. the history field is a long string of continous information that contains the company name and ID. I want to have a portal that list the names of these companies based on a relationship that "finds" all the instances of the matching ID in the long string. any ideas?

[Fenton]

I don't think it's often a good idea to store "multi-valued" information in an unstructured text field. Easy to get in, but hard to get out or do much with.

You would be much better off to put the history in a structured form. A table with ID, Company, Date, Note. One record per event. Then it would be a piece of cake to related to the ID and view the relevant records.

All you'd need for entry would be a button to create a new note.

Having it all in a big text field would work I suppose, if you ran a Find, ie., a script. But even then it would have trouble showing you what you want to see. It would have to show the whole text field, because it wouldn't be able to separate one line from another.