Relate Two Tables, Create New Data

[Jonathan Veit]

Thanks in advance for taking my question. I hope I'm not being too novice about this. I also hope I can explain my problem clearly.

So I have two tables whose records want to relate to each other. Specifically, any number of invoices can relate to any number of shipment invoices and vice versa. Having this work in both ways took a repeating field and some scripting. This is not my problem.

My dilema is then creating unique information for that relationship. I can't simply reference information in a record in Table A from record in Table B because that record in Table A might be related to multiple records in from Table B, all which require a unique value. I thought maybe a repeating field to store each different value a record in Table B could reference but I can't seem to find a way for this to work. I can't refer to specific values in the repeating field throug the portal as I would in a function, using the [ ] notation.

I even thought of creating a new table and portal system but I started realizing trouble in this direction as well.

Any help?

[comment]

Specifically, any number of invoices can relate to any number of shipment invoices and vice versa. Having this work in both ways took a repeating field and some scripting. This is not my problem.

Actually, this is your problem - and your question is merely a symptom of it. You have a many-to-many relationship between the two tables, and you need a join table to resolve it. The join table will hold the IDs of the two parent records, as well as any data that describes the specific join of the two records. See a very basic example here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[Vaughan]

Having this work in both ways took a repeating field and some scripting. This is not my problem.

Actually, this is your problem...

That is not my dog.

[Jonathan Veit]

Ahh.. thanks so much, that makes so much more sense!