8 Replies Latest reply on Oct 26, 2012 8:53 AM by blumalice

    How to build relational database for invoicing...help with general set-up?


      I am building an invoicing database in FMP 12. My experience has primarily been with older versions of FMP (upgraded from 5 to 12). FMP 12 is a bit different in building relational databases with the use of tables, etc. Can someone please give me the easy steps to developing relational data or refer me to simple explanations online.


        • 1. Re: How to build relational database for invoicing...help with general set-up?

          Take a look at the starter solutions that come bundled with FileMaker. There is an invoicing systerm there. It may or may not suit your needs but would be a useful place to start looking.

          • 2. Re: How to build relational database for invoicing...help with general set-up?

            Thanks David but I already looked at the invoicing system template.  My question is more associated with how to set up and deal with the relational side of the set up as it differs from what I was used to in older versions.  If there is a link or any other suggestions you have, I would be greatly appreciative. Thanks!

            • 3. Re: How to build relational database for invoicing...help with general set-up?

              The setup in the relationship graph is essentially the same in versions 9 - 12.  The one new piece is the ability to use ExecuteSQL to pull information into a field or variable with out setting up a relationship.


              It might be helpful to take a look at some of the books available and the FileMaker Training Series.  As for books take a look at the Missing Manual Series and The FileMaker Bible series.  Books starting at version 9 of FileMaker should be helpful with the relationship graph.




              • 4. Re: How to build relational database for invoicing...help with general set-up?

                My experiences are up to FMP10/11.

                To set up a relational database is quite easy, so please stay with me and simply follow these steps:


                In your Invoice database go to

                File/Manage/Exterrnal Data Services

                First, enter a simple name reference for the "other" database then click on [Add file]

                find and select the database fle, select [Open] then [OK]

                That's part one complete.


                Repeat the above for any other Database(s) you need to set a relation with.

                At this stage, we are just identifying them to your Invoice Database

                Once you have completed the above, there's not much more to do - here goes...


                File/Manage/Database then click (at the top of the Fields "window") onto Relationships


                This is where we're going to link the two (or more) databases together


                You will see your Invoice database and all its fields listed in a tall box  - if it's not in the middle of the "window" I would suggest you move it there.  This will help if you need to link many parts of the other database(s) to the Invoice ... or each other (wow).


                At the base of the "window" you will see 4 small icons.  Try the first one - like a small screen with a green plus in the top righthand corner.  Click on it


                Choose your Data Source - the name(s) you initally created (in External Data Services) will appear here, select the (first) one you need.  The Table name will appear under it in the bigger box.  In the small/long box at the base of the "window" enter a name for the element(s) of the database you are going to link with. An example being the Product Nu0mbers and their names. The [OK]


                A similar. but smaller, list is now shown to the side of your Invoice database fields listing.


                You may need to make the newest "box"longer/taller to see more field names.


                he minimum requirement is to find one field in each, which is identical in teir content - such as Product Number. Click on the field in the "other" database and drag your mouse to th matching field in your Invoice database, once you are "touching it" let go.  A line links the two databases with an equal symbol in the middle.


                Click on the [ = ] in the middle of the line - this is where you can refine/edit the relationship.  Handy if you sleted the wrong field(s) - you acn select new one(s) in this area. You can also dd/Delete/Manage other fields here - the dropdown box between the two databases listing (left and right of the dropdown box) the default is = , you can select many other situations (such as not equal, less than, greater than.....) you may need to have  second decider to dtermine if the refeence is the correct one to b shown on the Invoice.  Something like Colour/Size/Version.  If this is the case, select the two matching fields (one from each of the sides) and click on [Add] this implies you need to match the first AND the second pair of fields to choose the matching data to be aded to the Invoice (such as Product Name).


                There are various other selection buttons in and around the base of this area.  You are best to decide which (if any) you need to choose.


                When done click om [OK]


                From here you have two choices.


                1. Use the additional database fields in your Fields (especially when using Calculation mode).

                2. Use the additional database fields in your Layout, without doing any thing to it.


                     e.g.     Product Number     Product Name     Qty     Unit          Total

                               101-1234               Hitachi 3TB HD     1     £175.00     £175.00

                               129-4131               SATA Cable          2     £   7.50     £  15.00


                The Product Name is selected via the link you have made between the two databases.  In fact any field from the "other" database(s) will match and can be included on the Invoice - thanks to the relationship of (at least) the Product Number.


                I hope the above has helped in some way.


                Regards, Terence

                • 7. Re: How to build relational database for invoicing...help with general set-up?

                  You have had a variety of solutions given to you, are none of them any use at all?


                  Regards, Terence

                  • 8. Re: How to build relational database for invoicing...help with general set-up?



                    sorry for my bad english, but i prefer to ask few info here that inside italian forum..it look like nowhereland..


                    I have similar problem.. i would like to developer apps and this is my first approuch to FM/12..


                    To understand better, i used the sample invoice... i now understand more respect 1 week ago but i got problem with totals.


                    I have tried to improve the clients details with 3 different status of inovice...actually the sample use  only 2.



                    So, everythings work properly but the clients amount is zero..



                    The only things i did it was:

                    -  change name of duplicate files as INVOICE PAID AND INVOICE UNPAID into ORDER and CLOSE.

                    - change calculation field where appears PAID/UNPAID

                    - i change everywhere the the words macthes...Files, fields, Forms scripts, Table, ect.ect.


                    and i checked with printed database report and i did not found anywhere the old words but only the new ones..


                    At end the programs run OK but amount on Clients details form are alwys zero..





                    Apparently if i change back word/status of incoice from ORDER to PAID, the amount is correct.. restore new word, zero amount is show on form


                    I cannot found where is the problem...


                    Somebody may helo me to understand?


                    thanks for you kind attention..


                    Have you a nice day.


                    Angelo from italy