12 Replies Latest reply on Dec 21, 2011 5:27 AM by SquealMcSqueal

    Tables

    SquealMcSqueal

      Title

      Tables& Relationships & sequential serial numbers

      Post

      Hi.

      New'ish to Databases and to filemaker Pro 11 so advanced apologies!

      I have currently made a system for myself, which allows data entry for my customer's. The data includes the usual information and also allows me to book certain dates of the year for them. Each customer can then be sent or emailed a customised letter dependant on other choices they have made. All of this information is contained within 1 table.

      So far it works perfectly for me.

      However...

      I now need to be able to create an 'invoice' for them. Currently each customer is assigned a unique reference number, started from 00001 and incrementing by 1 for each customer. Simples no ?

      I want the invoices to also start at 00001 and increment by one each time I make a new invoice. They must remain sequential.

      Now, not every customer requires an invoice which is where my problem comes in. For example:

      Cust #   Invoice #

      00001     No invoice required

      00002     00001

      00003     No Invoice required

      00004     00002

      00005     00003

      This is how the invoice number needs to work. Customer numbers will always be sequential, but the invoices will skip customers but need to remain sequential. Hope I've explained this correctly.

      I can't seem to be able to do this within one table so I am trying to incorporate a 2nd table, in which everytime you enter a new record via a seperate layout, it supplies the next invoice number in the series. That I can do.

      I cannot however get the new layout, which is tied to the new table, to display any results from the original table. I need to be able to simply find a record from the first table, have it auto populate the new layout - save it as a new record to print to a template, with original customer number, their relevant info and the new invoice number.

      I'm not working on the original template I created but with a very simple new template, just so I can get my head around the idea - which I am struggling with =D

      The new layout I am working with simply has a unique number generated, data entry for Name, Address, Company, Amount Paid. The second layout has unique Invoice number, then needs to populate the data from the first layout so I can print & store it via a template.

      I hope some of this makes sense to someone who help / show me with a simple example how this might work.

      Cheers