1 2 Previous Next 21 Replies Latest reply on Jan 27, 2010 8:49 AM by philmodjunk

    Brand New to Databases / Filemaker  a few questions...

    paintboothguy

      Title

      Brand New to Databases / Filemaker  a few questions...

      Post

      Hello all, 

      I am brand new to this software and don't know much of anything.  I am using the trial version of Pro 10.  I am working on a Mac.  My first task is to try and set up a product list that has product ID #'s, descriptions, retail and dealer costs.  I then need to be able to generate Invoices and have all fields populated automatically when I enter a product ID#.

       

      I have downloaded the 'Invoices Demo,' and I believe I generally understand the concept, but I am confused on the following.

       

      #1.  Why is there a 'Line Items' table in between the Invoices and Products tables.  What does this accomplish?

      #2.  If I want to generate (hopefully) lots of invoices in the future, do I make a new 'Layout/Report' and Call it an Invoice? 

       

      Any advice would be greatly appreciated!

      -Thanks, Andrew 

        • 1. Re: Brand New to Databases / Filemaker  a few questions...
          philmodjunk
            

          Answering number 1:

           

          LineItems are used in a portal so that one invoice can list any number of individual items purchased on that one invoice. If you are unfamiliar with portals, this is a useful term to look up in Filemaker's help in order to learn more about it.

           

          On question #2:

           

          Are you asking "do I create a new layout for each invoice?" If so, then no. You will create a new record on an invoices layout for each new invoice.

          • 2. Re: Brand New to Databases / Filemaker  a few questions...
            mrvodka
              

            The line items exists because it serves as a join table when you have many records to many records between the Invoice itself which should store all pertinent invoice specific information such as date, customer, etc and the table of products.

             

            IOW, you can have many invoices using many of the same products. Since prices can change and the invoice should have point in time information, you would "lookup" the price information from the products table and store it a lineitems record.

            • 3. Re: Brand New to Databases / Filemaker  a few questions...
              paintboothguy
                

              Thanks for the help guys!  I'm off and running but have encountered a little problem.

              I have created several tables, but some of the fields that I have created within the tables are not appearing in browse mode.

              Simple problem, right? (I hope!)

               

              Thanks,

              Andrew 

               

              • 4. Re: Brand New to Databases / Filemaker  a few questions...
                philmodjunk
                   Depending in part on the preferences you select in edit | Preferences | layout, newly defined fields may not be automatically added to your layout until you enter layout mode and use either the field tool or Insert | Field to add the field to your layout.
                • 5. Re: Brand New to Databases / Filemaker  a few questions...
                  paintboothguy
                    

                  Thanks for your time, Phil.  That worked and I'll keep on pluggin'.

                  -Andrew 

                  • 6. Re: Brand New to Databases / Filemaker  a few questions...
                    paintboothguy
                      

                    Good Morning All, 

                     

                    I have successfully created working relationships to auto-populate a portal in my 'customer invoices' layout.  Now, I would like to do the same thing with the clients billing / shipping info on the top of the invoice.  Maybe I could enter the customer ID and have it 'look-up' the fields I need (such as address, etc.)

                    I know this has something to do with relationships, but I can't get my mind around it.

                     

                    FYI:

                    I am using the 'Contact Management' template, but have built other tables from scratch based on the 'invoice-demo' I downloaded.

                     

                    Any Ideas?

                    Thanks,

                    Andrew 

                    • 7. Re: Brand New to Databases / Filemaker  a few questions...
                      philmodjunk
                        

                      First you'll need to decide between two options: Do you want the customer information on past invoices to change if the customer's current contact information is updated? Here where I work we decided we wanted to keep the contact information in the record as it existed at the time the transaction took place and we printed the invoice. Other businesses may prefer to see this info update auotmatically.

                       

                      For either option you need a relationship linking a field in invoices to a field in contacts. Ideally, this should be your contact ID number.

                       

                      If you want the contact data in an invoice to remain unchanged, use the looked up value option. Define a field in invoices for each such contact field (name, address, phone...) and use the looked up value field option for each field to select a field in the contacts table to copy data from when the customer's contact number is selected.

                       

                      If you want the data shown to update automtically, use the same relationship, but simply place fields from the contacts table on your invoice.

                      • 8. Re: Brand New to Databases / Filemaker  a few questions...
                        paintboothguy
                          

                        Phil,

                        Thanks again for your reply.  It seems the key here is the relationship tables and their positions.

                        If I had a better understanding of how things will flow in the end, maybe I'd understand how to implement you instructions better.

                         

                        SO:

                         

                        Let me ask one question first:

                         

                        #1) I currently have 'products', 'line items,' 'invoices' , then 'customers' linked in series, exactly like the ''invoice-demo'.  Where do I create SEPERATE invoices for individual clients and store them?

                         

                        Thanks, 

                        Andrew 

                         

                        • 9. Re: Brand New to Databases / Filemaker  a few questions...
                          philmodjunk
                            

                          Each separate invoice is a new record in the invoices table.

                           

                          Select your invoices layout and choose new record.

                          • 10. Re: Brand New to Databases / Filemaker  a few questions...
                            paintboothguy
                              

                            Phil, 

                            Awesome!  Done!  I have created a portal on the invoices layout which successfully populates retail price and product description after I enter a product ID number.

                             

                            Now,

                            I'd like to (maybe create another portal?) on the same layout wherein I can enter a customer ID number, and have that portal populate the clients name and address, etc.  Given the relationship structure I mentioned in an earlier post, can I do this?

                             

                            Thanks,

                            Andrew 

                            • 11. Re: Brand New to Databases / Filemaker  a few questions...
                              philmodjunk
                                 If there is always only one customer record for a given invoice, you don't even need the portal. One of my previous posts outlined two different approaches for this. Check them out and let me know what you think.
                              • 12. Re: Brand New to Databases / Filemaker  a few questions...
                                paintboothguy
                                  

                                Phil, 

                                Got It!  I was trying to use a portal, but just needed individual fields.  I did not realize that it would 'Look-Up' that way.

                                 

                                On individual contact management records, is it possible to set a script to 'generate an invoice?'  Upon running this script (maybe via a button), I would like the invoice page to appear, then a new record created, then the clients info to magically appear so I can start filling in invoice info.

                                 

                                I can't tell you how much I appreciate your help.  If there were a way I could digitally buy you a beer I would!

                                 

                                Thanks,

                                Andrew 

                                • 13. Re: Brand New to Databases / Filemaker  a few questions...
                                  philmodjunk
                                    

                                  Use your table and field names in place of mine...

                                   

                                  Set Variable [$ContactID; Value: Contacts::ContactID]

                                  Go To Layout ["Invoices" (Invoices)]

                                  New Record/request

                                  Set Field [Invoices::ContactID; $ContactID]

                                   

                                  If you use looked up values, the last step will trigger the lookup to copy over the values. If you just placed the related contact fields on your layout the last step should cause the right related data to appear.

                                  • 14. Re: Brand New to Databases / Filemaker  a few questions...
                                    paintboothguy
                                      

                                    Phil, 

                                    I thought I'd replied to this message, but I guess I deleted it.  Thank you for the formula for the script.  All but the last part is working. Here are the names of my tables / fields:

                                     

                                    Tables: 

                                    Contact Management / All Invoices

                                     

                                    Pertinent fields:

                                    k_ID_Contact (an auto-serial number field in the Contact Management Table)

                                    Customer ID (this field is in the All Invoices table set lo Lookup the k_ID_Contact value.)  

                                     

                                    In the Invoices Layout, I have placed fields (one at a time with the field tool) from the Contact Management table for all of the clients billing and shipping info.  I also have placed the Customer ID field from the  All Invoices table in the same Layout.

                                     

                                    When I run the script, I get sent to the All Invoices layout, a new record is created, but the fields do not populate.  If I enter the Customer ID manually, the fields DO populate.

                                     

                                    I know I am close...I can taste it!

                                     

                                    Thanks for the help, Phil!

                                    -Andrew 

                                     

                                    1 2 Previous Next