3 Replies Latest reply on Apr 5, 2014 9:44 PM by TKnTexas

    Creating invoices, pulling info from Client table



      Creating invoices, pulling info from Client table


           I've created two tables, first one being my clients and all related information and the second table for my products. 

           What I would like is to be able to click a drop-down box, click the client name and have Name and address populate automatically.  From there, the same for products. 

           If the process is any easier...  Maybe a button in my client table that when pressed sends Name and Address to a new invoice.

           If someone can point me in the right direction, I'd truly appreciate it.  I'm sure I'll have more questions, but this will keep me busy for now.


           Thanks a million,


        • 1. Re: Creating invoices, pulling info from Client table


               I don't see anyone answering this, so I'll give it a try. First, you didn't say you have an Invoice table. You need one. Also, unless every sale is for one (and only one) object, then you need another table, usually called something like "invoice lines" ("InvoiceLines", "Invoice_Lines", "Invoice Lines"). 
               An Invoice tells you WHEN something happened to WHO. It also has the total of the sale(s), though this is a calculation field, adding up the amounts of the related InvoiceLines records (of that Invoice), and adds whatever else needed. The Invoice also has (locally, usually) the "payment" (or payments, though this may require another table, if it comes in multiple pieces; but we'll ignore that for now).
               InvoiceLines is where the records are which have both the ID of the Invoice (which is the same each item of the invoice's objects). It also has the ID of the Product, which would (likely) be different for each object of a particular Invoice (as you use a number field to say how many of that object on that Invoice).
               A decent example is in the folder you got from FileMaker, in the Business - General folder, named "Invoices". I do not know which version(s) of FileMaker you're running. I think the ".fp7" one is a little easier to learn from. The ".fmp12" is good also. In the guts of the Relationship Graph, they are very similar on the overall view.*
               The ".fmp12" adds lots of "script triggers", to move things around as needed, as well as layouts and scripts for "iPad" and "iPhone"; which will useful these days… I kind of which there were also "basic templates", for beginners. Those would be easier to learn from. However, they would need more work to handle todays world, so what to do?
               So I'd say look at the basics, Tables, Relationship Graph, Layouts (most), Fields (most). You need need a "Invoice" table and an "InvoiceLines" table.
               P.S. The  ".fmp12" template file uses "Invoice Data" for the name, instead of "Invoice Lines", which seems a little silly to me. But I'm an old, and pretty much obsolete. 
               I'm no longer a "developer", as brain injury from a stroke (3 years ago) makes this (and much else) a "hobby", not "work". For one thing, I have trouble reading sentences (even those I type). Fortunately, computers can talk :-]. The basics of FileMaker database design are so basic that one does not forget (all of) them.
          • 2. Re: Creating invoices, pulling info from Client table

                 Thanks for all the great info Fenton.  I'm familiar with most of it.  I'm taking this one step at a time.  For the most part, it really would be only one item purchased per invoice, buts thats for another day.  For now, I'm just trying to get the info from one table to another.  I'm trying to get the Name and address from my 'clients' table to my 'invoice' table via a drop-down menu showing 'client' names.  Its driving me insane, I'm sure its something simple that I'm missing, but I just can't get it.





            • 3. Re: Creating invoices, pulling info from Client table

                   From your description, you will need an Invoice Table.  Putting the Client_ID on the record links it to the Client Table,  You can show the name and address on the invoice by showing the remote fields (from the Client Table).

                   Show the Product ID and you have the link to that Table.  Add a quantity bought (if appropriate), add a calculation field for the extension, and other amounts, i.e. shipping and sales taxes.