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!