Import transfers proper number of records but the contents from only one of the Source records.

[Doug57]

I am trying to move records between two FM files. The source is a file called Recipe Ingredients which has fields Recipe ID# and Ingredient ID#. The Target is a Shopping List file which also contains these same 2 fields Recipe ID# and Ingredient ID#. The two files are related through the Recipe ID# fields (I have tried the Ingredient ID# a the relating field as well but the same problem occurs)

I do a find in the Recipe Ingredient file for all Ingredients for a specific Recipe, then an Import in the Shopping List file. I have tested this and it works fine.

The Import definition has the Source and Target specified as previously mentioned. I have matched up the 2 Source and Target fields ... Recipe ID# and Ingredient ID#. And I have selected Add New Records.

When I run the scripts the Find occurs and works fine in the Source file, but the Target file ends up with the proper number of records imported but each record imported shows up in the Target as only one of the Source records ... specifically the last record in the Source Found set. In other words in the Target file I end up with the same Receipt ID# in each record but I have all the same Ingredient ID's in each record.

Can you give me any clues as to what could be causing this to happen?

Any help would be much appreciated.

Thanks

[jdu98a]

From the information you've provided it's difficult to say exactly what might be causing the particular behavior. It could be some calculation/auto-enter field interfering with the values brought over in your import. In any case, (and just ignore this if it doesn't apply at all) it sounds like your solution would be better served with a little different table structure. (see pic)

Consider using 3 tables. As shown in the picture the [color:black]Recipe table would contain a portal showing records from the [color:black]Shopping List table created using values from the [color:black]Ingredients table. This is a common practice using what's called a "join table". There's a little more that goes into making it work smoothly, but you'd be better off searching for posts that already exist on the subject. You may already be aware of all this, but I thought I'd point it out.

Edited May 27, 2010 by Guest

[Doug57]

Thanks for your insights.

Your first comment caused me to go back and look carefully at my Lookups and sure enough there was a problem. As I was trying different approaches to get around this issue I had forgotten to change the field definitions back, thus creating the problem. A "senior's moment". Cleared it up fine.

By the way I had tried your thought on the Join table but had run into a problem unrelated to Joins but due to the specifics of what I am trying to do. So, I had gone onto my current approach.

Appreciate the time and work you put into making the attachment to clarify your suggestion!