Hello,

Post# 179936 on 10/20/05 talked about concatenating two fields when copying between tables. This post and its replies almost got me to a solution to a similar problem. Maybe someone has information more specific to this situation.

Within my file I have a table labeled 'contacts' and a table labeled 'outreach'. Records in the contacts table represent unique individuals. Records within the outreach table represent interactions between my firm and individuals in the contacts table. My challenge is:

I want to be able to perform a find in the contacts table, then create a new record in the outreach table for every member of that found set. Certain fields should be copied from each file in the found set to its corresponding new record in the outreach table, while several other fields need to be populated with data that will be the same for all of these new records created in the outreach table. (e.g. we've sent a letter to everyone in the found set, and want to create an outreach record for each letter that was sent, noting date sent and enclosures).

All seems to work well except copying the 'full name' field. In the outreach table (the 'to' table in my problem) the 'full name' field is validated as a member of the value field 'names', whose values equal all 'full names' in the contacts table. 'Full name' = 'first name' + 'last name'.

This may seem like irrelevant data, but the problem with my script seems to be tied to this in some way. Here's the essence of my script thus far

perform find[]

copy [select;Contacts::Company]

Go to Layout; ["Outreach"(Outreach)]

New Record/Request

Insert Text [select; Outreach::Contact Type; "mail"]

Paste; [select; Outreach::Company]

and I go on from there. As I copy and paste the 'full name field, I encouter the following error. When I run the script it keeps trying to populate the 'full name' field with the company name, and I receive an error message saying that 'full name' is defined to contain only specific values.

Any ideas or pointers would be most welcome.

I may be a little off base here, but I wonder if you should replace the Copy function with a set-field function. Copying uses the clipboard, and who knows what's on it. I suspect there is an even better method, but I did a similar thing by the following steps:

1: establishing a global field in a source table

2: set field ( global; textA )

3: go to dest. layout

4: new record

5: set field ( textA ; Source::global )

Since my solution does this trasfer for many records, I switch back and forth using a counter to keep place in the source layout. Hopes this helps, or at least gets more people to respond.

Trying to copy the data seems to indicate that you haven't quite grasped relationhships yet. You don't need to copy any fields. You need a join table with LetterID and ContactID. That's it.

Hummm...

Thanks for the suggestion. The one point I'm not getting regaring the join table is what causes the population of new records from the source table to the destination table? When I try changing from a copy/paste to a join table, no information is carried forward from the source table (e.g. the found set) to the destination table (e.g. the interactions). This although the fields appear in both tables.

Thanks ikro. I'm still trying! Like you, I know there's a solution out there.

See the sample file for the many to many relationship.

http://www.fmforums.com/forum/showtopic.php?tid/170923/post/180289/hl//#180289

Thanks Bruce, I'll grant that my understanding of relationships is as of yet incomplete. But then, is it possible to duplicate rows of data in a remote table, which are also independent of the original table. So that deletion of rows from one table won't affect data in the other? Sorry, if this question gets in the way of the original post.

Ralph -

Sure appreciate your input, but having trouble opening the attachment after I follow your link. It looks like a FM file, but with an 'sit' extension. Can't open it in FM or Word. Any clues?

The .sit extension is a compressed format you need to expand the file with Stuffit. You can download a free expander at http://www.stuffit.com/win/index.html

Thanks Bruce, I'll grant that my understanding of relationships is as of yet incomplete. But then, is it possible to duplicate rows of data in a remote table, which are also independent of the original table. So that deletion of rows from one table won't affect data in the other? Sorry, if this question gets in the way of the original post.

You're missing a principle point of using a database. Why are you trying to duplicate data? You only need a join table; you can DISPLAY the related data without duplicating the record.

Edited October 28, 200520 yr by Guest