when to join and when not to join

[nati]

I understand the many to many and have several solutions where it's working fine. However, in each of those solutions there is a lookup in the join file to the other many (invoice file has portal to join file which looks up product information in product file). However I'm now interested in redoing the payment file which is currently a one to many (one invoice has many payments and each payment is associated with one invoice and is entered in a portal on the invoice window). If I want to make this a many to many there is nothing to lookup so how do I populate the new many payment file? Right now it populates itself because the relationship allows creation of records but that's to what would become the join file... how does the many payment file become populated?

Many thanks,

Nati

[mcstar]

Maybe you don't need a many to many relationship here. Perhaps you want to be able to list all the payments from a company as well as all the payments on a single invoice. If so, add a company field to the payment file and make it a auto-lookup based on the invoice relationship. Now in the invoice file add a relationship to payment file based on company. Does that get what you want?

[LiveOak]

The need for a join file between payments and invoices is dictated when one invoice can have many payments AND one payment can be applied to many invoices. There are a lot of ways to perform the data entry into the join file. If you allocate payments from the payment file, you can have a portal showing invoices with an unpaid balance by customer. Another portal shows records for this customer from the join file. Clicking on an outstanding invoice in the invoice portal can create a record for that invoice in the portal to the join file. You can then enter the portion of the payment to be allocated to this invoice.

-bd

[nati]

thanks for the replies! I'll look this over and see what I can do.

Thanks again,

nati