3 Replies Latest reply on Oct 18, 2013 10:17 AM by philmodjunk

    One to Many Relationship Issue



      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!




        • 1. Re: One to Many Relationship Issue

               Problem 1

               IF you can invoice the same job more than once, the job (job jacket) table should not be the table used for invoices. A separate invoice table linked by job number should be used for invoices.

               You Invoice status table is a start in the right direction, but instead of invoice STATUS, it should just be your table of invoices. The data pertinent to that one invoice should be recorded either in fields in Invoices or in a related table linked directly to Invoices instead of Job Jacket.

               Problem 2

               I see a group of fields: Item Number1, Item Number2....

               These fields probably list specific items for a given invoice. Using separate fields in the same table limits you, only 4 items can be listed in any given invoice and complicate your database design. (Note the 4 separate relationship lines that, I'm guessing, all link to different Tutorial: What are Table Occurrences? of the same table.)

               Instead, a related table should be defined for this info and it looks like data that should be linked to the new Invoices table rather than job jackets with a portal (think MS Access Subform) used to work with those individual items. That's an approach that both simplifies your relationship graph and allows a user to list any number of items for a given invoice.

               To sum up, your relationships should look like:

               Company----<Job Jackets----<Invoices----<LineItems>-----ProductsServices

               Getting your existing database converted over to such a restructured design will need to be done with care. Not only can you use Import records to move data from fields in existing records into such new tables, you can also use Replace Field Contents and looping scripts to move the data into the new tables. Once testing shows that the data has all be successfully moved, you can delete the fields and relationships that are no longer needed.

          • 2. Re: One to Many Relationship Issue

                 I agree with your approach but that will take some time to fix. My current solution is to create the invoice, save it as a PDF & store it in the Container field so that there's a record of the original invoice (Job #-1) when it gets overlaid with the next one for that job (Job #-2). However, the InvoiceStatus table keeps track of all invoices sent & payments rec'd. 

                 It would make everyone's job easier if the attached form could automatically populate invoice #  and amounts when an invoice is created instead of manually typing that in, leaving room for error.

            • 3. Re: One to Many Relationship Issue

                   On what table occurrence is this layout based? Job Jacket?

                   Assuming that it is, you need two fields for now, and eventually, I'd use one more once you added an actual invoices table.

                   One field should just store the sequence for that job's invoices, 1, 2, 3, 4

                   One field should store the original job number.

                   Your invoice number field would then combine the original job number with this sequence  in a calculation:

                   FirstJobNumber & "-" & InvoiceSequence

                   A script run from Job Jackets could then run like this:

                   Set Variable [$JobNumber ; value: JobJacket::JobNumber ]
                   Set Variable [$InvSeq ; value: Max ( InvoiceStatus::InvoiceSequence ) + 1 ]
                   Go to Layout ["InvoiceStatus" (InvoiceStatus) ]
                   new Record/Request
                   Set field [ InvoiceStatus::JobNumber ; $JobNumber ]
                   Set field [InvoiceStatus::InvoiceSequence ; $InvSeq ]
                   Set FIeld [InvoiceStatus::FirstJobNumber ; LeftWords ( $JobNumber ; 1 ) // strip off any -1, -2....]
                   Go to Layout [original layout]

                   But please note that two users performing this script from the same record in Job Jacket at the same time could produce two new records in InvoiceStatus with the same InvoiceNumber.

                   Make InvoiceNumber a text field and set up the specified calculation as an auto-enter calculation, clear the "do not replace existing value" check box. Then click the validation tab and specify unique values for this field to guard against that issue.