AnsweredAssumed Answered

One to Many Relationship Issue

Question asked by Riverview on Oct 18, 2013
Latest reply on Oct 18, 2013 by philmodjunk


One to Many Relationship Issue


     Hi, I'm new to FM (FMP 12) but have experience with Access DB & SQL. I'm trying to improve an existing database used for everything from quoting estimates & invoicing to time card tracking and client info.

     There is one giant table (Jobs) that includes almost all the info about a client including the estimates & invoicing. The current process for invoicing is to use the Invoice form & assigning it the Job # as the Invoice #. Because Job #'s are unique, if a 2nd invoice is needed for the same Job, a "-1" is added to the job #, thus causing confusion and no easy way to total amounts or report on payments.

     I'm trying to revise the process by creating a new form/table (InvoiceStatus) to keep track of the invoices sent out by Job #.  I created a form with a portal where you pull up the job # and then enter invoice info for multiple invoices. The Jobs table is linked to the InvoiceStatus table using the unique Job # field. However, when we create a new 2nd invoice using the Invoice form (using Jobs table fields), it will overwrite the 1st invoice # if it has the same Job #.

     I know it's a problem with the relationship b/w the 2 tables but can't figure out where. Hopefully, I made sense explaining this. I've a attached a screen print of the 2 tables. Thanks for any help!