5 Replies Latest reply on Nov 26, 2010 12:53 PM by philmodjunk

    Advice - Duplicating a Table and Layout

    JayCornelius

      Title

      Advice - Duplicating a Table and Layout

      Post

      I'm trying to duplicate a Table and Layout (playing with the Invoices starter solution).

      I've managed to duplicate the table but battling a little to duplicate the layouts with links to the new table - espcially considering the Line Items portal.

      Any advice would be appreciated.

        • 1. Re: Advice - Duplicating a Table and Layout
          philmodjunk

          Why did you choose to duplicate the table? (New users often attempt this in situations where it's not a good design choice. There may be a better approach.)

          You can enter layout mode and choose Duplicate Layout from the Layout menu to duplicate a given layout. To update this layout to refer to the new table, you can select Layout Setup... and choose its Table Occurrence name (Table occurrences are the "boxes" you find in Manage | Databases | Relationships.) from the Show Records From drop down. You then have to double click each field that refers to your original table and specify that it now comes from your newly duplicated table.

          If your layout includes portals and/or fields from related tables, you'll need to replicate the relationships that exist for your original table and then update each such portal and field to also use the new table occurrences.

          If "table occurrence" is a new term for you, you might want to read this explanation on the term and how they are useful with FileMaker Pro databases:  Tutorial: What are Table Occurrences?

          • 2. Re: Advice - Duplicating a Table and Layout
            JayCornelius

            Tx Phil

            Thank you for taking the time to help a newbie.. well I worked with Filemaker 7years ago so just getting into it again now.

            The reason I'm playing with duplicating the Invoices table is that I want to sets of Invoices running on the same Invetory - basically two companies invoicing from one Inventory (Product).

            The next complication would be building in Quotes that can become invoices.

            The basic setup probably;

            Project with Jobs (Jobs Sheets)
            Jobs would raise Quotes
            Invoices then raised from based on Quotes
            Invoices need to update Inventory

            Jobs sheet should ideally list related quotes and invoices

            I'm still thinking things through but any advice/suggestions are very welcome.

            • 3. Re: Advice - Duplicating a Table and Layout
              philmodjunk

              The reason I'm playing with duplicating the Invoices table is that I want to sets of Invoices running on the same Invetory - basically two companies invoicing from one Inventory (Product).

              I thought it might be something like that.

              This does not require separate tables and will be simpler to implement if you just add a field for company ID in your invoices table. This company ID field can link to a companies table where you can store the company name and other infor specific to each company. If you later need a third company, you'd just add another record to this table instead of adding another table, layout and system of related table occurrences.

              Quotes can be treated as another kind of invoice. Depending on your business model, you can "mature" your quote record into an invoice by updating a status field from "Quote" to "Invoice", or you can duplicate your quote record to start an invoice for that quote. (And a script can be constructed to duplicate the line items when you duplicate the Invoice_Quote record.)

              • 4. Re: Advice - Duplicating a Table and Layout
                JayCornelius

                Tx again Phil!

                Just one thing I'm not sure of on your suggested setup...

                How can I number the Invoices independantly? i.e. a sequential range per company.

                Also like the idea of Quote maturing to invoice via status and probably from ProForma to Invoice instead of Quote to Invoice. Agin though just not clear on how to treat the numbering.

                Really do appreciate your assistance.

                • 5. Re: Advice - Duplicating a Table and Layout
                  philmodjunk

                  You'd need to manage invoice numbers with a field that is separate from the InvoiceID field that uniquely identifies each invoice record.

                  Question, is it possible that two users might be creating invoice records at the same time for the same company? You'll need a scripted approach for this and making sure that two users can't generate a new invoice in the same series at almost exactly the same time without getting duplicate invoice numbers can be a bit of a pain to set up.