14 Replies Latest reply on Aug 1, 2016 1:00 AM by FileKraft

    Database of Customers and Types of Invoices

    arcphoto

      I am somewhat new to FileMaker and have done some tutorials on Lynda.com so have a general understanding of how to navigate and create databases in FileMaker. However, I'm trying to wrap my head around a database I'm creating to manage my clients and the various invoices.

       

      My database involves:

       

      1. Client List

      2. Quotes

      3. Invoices

           a. Deposit

           b. Final

       

      My database is currently set up with a Client table, a Quotes table, and an Invoice table. I originally set up my database with Client and Invoice tables only but when I created my layouts, I realized the quotes and the final invoice amounts were often dissimilar (due to changes in scope of projects). Also, upon setting up my layouts, I realized every quote I created could then be viewed as an invoice (and vice versa), which doesn't make any sense. I mean, a quote is a quote and an invoice is an invoice.

       

      Now, I'm encountering the same issues as I now have to create different invoicing options (deposit invoice, final invoice, etc.). If I keep all the values in one Invoice table but use multiple layouts, I can view my deposit invoice as a final invoice, etc., which I don't want to be able to do. So the solution is then to break out my Invoice table into two, one for deposits and another for final.

       

      So the problem is this. It seems I would need to have unique tables for all of the different types of invoices (deposit, final, ones where I bill per day, ones where I bill per item, etc.). Well that doesn't make a lot of sense! Additionally, each deposit invoice has an associated final invoice. Most of the values should be linked between the two, with the only exception being an occasional difference in the deposit amount billed versus the deposit amount paid. This means there is typically a one-to-one relationship between the Deposit invoice and the Final Invoice, suggesting that all the values should exist in a single table. But by doing so, I end up with the problem of being able to view a Final invoice as a deposit invoice (and vice versa).

       

      I think the correct approach would be to keep the values in a deposit invoice in a table separate from the values of the final invoice. If so, how do I link the values so deposit values can easily be "migrated" to the final invoice?\

       

      What am I missing here?

       

      Please keep in mind what I know about FileMaker has been through Lynda.com tutorials and I've been teaching myself. There's so much I don't know so please take it easy on me!

       

      Thanks for your help!

      Ken

        • 1. Re: Database of Customers and Types of Invoices
          Vaughan

          arcphoto wrote:

           

          ...I realized the quotes and the final invoice amounts were often dissimilar (due to changes in scope of projects). Also, upon setting up my layouts, I realized every quote I created could then be viewed as an invoice (and vice versa), which doesn't make any sense. I mean, a quote is a quote and an invoice is an invoice.

          Welcome to FileMaker.

           

          Much of what you are asking -- like, can a quote be an invoice -- comes down to business rules. Basically, the business decides what happens.

           

          The system I'm working on has a strict Quote -> PO -> Invoice work flow. One of the business rules I need to follow is that an invoice MUST never be greater than the purchase order total, and the total of all invoices must equal the purchase order amount to the cent. There is a process that chases up POs with outstanding balances (ie, unpaid or partially paid) so ultimately the invoice total must match the PO total for it to be closed.

           

          Regarding your question, all invoices in one table.

          • 2. Re: Database of Customers and Types of Invoices
            arcphoto

            Thanks for the response.

             

            For me, I'm keeping my quotes separate. However, if you put all invoices in one table, how do you avoid the problem of being able to view one type of invoice with a different invoice layout?

             

            For example, I currently have two invoice layouts. Deposit and Final. I go to create a new record using the deposit invoice layout but when I go to the final invoice layout, it shows all the existing records in the table, and lays out the deposit invoice data using the final invoice layout. I don't want to see that as it would be confusing.

             

            Is there a way to go to a layout and, by default, only view the relevant records for that layout?

            • 3. Re: Database of Customers and Types of Invoices
              FileKraft

              yes there are ways to control what is shown on a specific layout:

               

              you can use an OnOpenLayout trigger script to do a find when that layout is been navigated to

              and even use the security setup to create logical predicates to control with get ( LayoutName ) matching the particular layout in question for what shall be visible there ...

              • 4. Re: Database of Customers and Types of Invoices
                Storganise

                HI Ken,

                 

                Yes, that's very straightforward. You need to create an extra field called "Final". All Deposit invoices will have that field blank, and when they become Final invoices (either through a script or through your intervention) then you need to add in an extra step to make the "Final" field then have a value - say "Final" for instance! This can be set up as a checkbox set of all the options you have for different stages of invoice. (Deposit, Final, Quote, Disputed etc...)

                Then you should have different navigation buttons -

                one that takes you to all Deposit invoices - this will have a script step at the end which will show only records with an empty "Final" field

                one that takes you to all Final Invoices - the script step here will search for all "Final" fields with a value of "Final"

                etc...

                 

                Going back to your original thread and asking about Deposit invoices and Finals mostly being the same, but needing one for each transaction, you could just add a button on your Deposit layout called "Finalise" or something, and that would trigger a script duplicating the Deposit record, but with "Final" in the Final field, then you can edit that to amend amounts as needed, without affecting the Deposit invoice. I would be inclined to duplicate the invoice number with maybe an additional letter at the end for the Final invoice, so you can easily search for matching records.

                 

                As you progress further with this, you'll want to make a self-join table to make relating the different types of invoices together a lot easier, but for now this should keep you busy enough!

                 

                And backing up what @Vaughan said - definitely only one table for all invoice stages.

                 

                Good luck!

                 

                James

                • 5. Re: Database of Customers and Types of Invoices
                  philmodjunk

                  In addition to using the OnLayoutEnter trigger to limit a found set to invoices of just a specific type, you can also use the OnModeExit to perform a script that constrains the current found set every time the layout exits Find Mode. Thus, a user can use find mode to perform an ad hoc find, but then this additional script kicks in and filters any found set thus produced to omit any that are not of the type appropriate for the current layout.

                  1 of 1 people found this helpful
                  • 6. Re: Database of Customers and Types of Invoices
                    arcphoto

                    Thanks everyone for your responses. That really helps as I now know that it's possible and what I need to learn. Unfortunately, I no longer have access to Lynda.com and hadn't gotten into scripting yet. Any good resources you guys can recommend or and specific details for how to implement your suggestions?

                     

                    [Edit] I think I figured it out and it seems to be working. Has to play with the scripting a bit but got it going now.

                     

                    I couldn't find an OnOpenLayout trigger but there was an OnLayoutEnter trigger so I'm assuming that's the same thing. Also couldn't really figure out the differences between some of the "Find" options but got one of them to work.

                     

                    So, what are the differences with each option? Descriptions weren't super clear.

                     

                    Perform Find

                    Perform Quick Find

                    Find Matching Records

                     

                    Thanks!!

                    • 7. Re: Database of Customers and Types of Invoices
                      keywords

                      In addition to all the helpful responses so far, here is another though for you to consider—add a new Payments table. This would probably require a join table rather than a direct link to invoices (you could have a single payment from a client for multiple invoices, or you could have a single invoice paid of progressively via multiple payments, and of course payments that are a combination of these—the balance of one invoice, a part payment of a second, and a third paid in full).

                      This approach would enable you to easily keep track of where each voice is at and may obviate the need for a separate deposit invoice. It would also facilitate issuing receipts for payments with easy reference to the various invoices it covers.

                      • 8. Re: Database of Customers and Types of Invoices
                        philmodjunk

                        Here's one tip on scripting:

                         

                        A very large number of script steps exactly replicate the same actions you get by making menu selections. So, when first getting started, you might figure out how to do what you need by making menu choices, writing down each one so that you have a list in the order that you did them.

                         

                        You can then open up the scripts workspace and add steps with names matching your menu choices to build a simple script.

                         

                        From there, look up such steps as if, perform script, and loop in Help to learn how they can further enhance your scripting.

                         

                        Sent from my iPhone

                        1 of 1 people found this helpful
                        • 9. Re: Database of Customers and Types of Invoices
                          clayhendrix

                          Agreed: Only ONE table for invoices.

                          • 10. Re: Database of Customers and Types of Invoices
                            clayhendrix

                            arcphoto wrote:

                             

                            So, what are the differences with each option? Descriptions weren't super clear.

                             

                            Perform Find

                            Perform Quick Find

                            Find Matching Records

                            I am a huge FMP fan, however, your question above is still not clearly answered in my mind. You are correct that the descriptions are not super clear. I post questions on here and get help (excellent help, by the way).

                             

                            1.) What records are you trying to find?

                            2.) What table are they in?

                            3.) What table occurrence the the layout based upon? (The layout from which you are trying to find records)

                            4.) Is the script going to do something with the found records after they are found or is that the ultimate goal of the script (to find the records)?

                             

                            Depending on what data is in the file, you could send us a copy of what you are working on and see what kind of help you get. You'll probably be very pleased with the help you get.

                            • 11. Re: Database of Customers and Types of Invoices
                              arcphoto

                              Thanks for the response. As I mentioned, I think I have it working but it took a bit of time to figure out which "find" option would produce the desired result. Because of that, I wanted to know which of the find options I listed should be used under what circumstances.

                               

                              To answer your question, I have a database of clients and invoices. I have two primary layouts, one for deposit invoices and another for final invoices. As suggested by others, I should have all invoice data in one table, which means that by having two layouts to show invoice data (deposit layout and final layout), it is too easy (and confusing) to view all invoices as either a deposit or as a final invoice, even if they were created to specifically be a deposit or as a final invoice.

                               

                              The script trigger and associated script was intended to make it so when I went to view my deposit invoices, it wouldn't show the invoices that were created as final invoices (and vice versa). The OnLayoutOpen trigger was used to activate the script that automatically performs a find for a field in my table that is used to identify a record as being a deposit or a final invoice. That way, I only see my deposit invoices when I go to the deposit invoice layout and the final invoices when I go to the final invoice layout.

                               

                              The find option I chose was "Perform Find" but it seems I should be able to use "Perform Quick Find" or "Find Matching Records" as well. Problem was, only "Perform Find" seemed to work as intended. This is why I got confused as to the differences among the various find options. I wanted to understand why the other "find" options didn't work.

                              • 12. Re: Database of Customers and Types of Invoices
                                arcphoto

                                keywords wrote:

                                 

                                In addition to all the helpful responses so far, here is another though for you to consider—add a new Payments table. This would probably require a join table rather than a direct link to invoices (you could have a single payment from a client for multiple invoices, or you could have a single invoice paid of progressively via multiple payments, and of course payments that are a combination of these—the balance of one invoice, a part payment of a second, and a third paid in full).

                                This approach would enable you to easily keep track of where each voice is at and may obviate the need for a separate deposit invoice. It would also facilitate issuing receipts for payments with easy reference to the various invoices it covers.

                                I actually kinda have that implemented in the layout. At the top, I created essentially a "For Internal Use" section in which I could enter data for "Paid" and "Date Paid". It's set up to be hidden during print so when I invoice the client (sent as PDF), they don't see it.

                                 

                                Having it right in the table/layout makes it easily searchable and keeps all the data in once table. For my work, I don't typically have to issue a receipt.

                                • 13. Re: Database of Customers and Types of Invoices
                                  arcphoto

                                  FileKraft wrote:

                                   

                                  yes there are ways to control what is shown on a specific layout:

                                   

                                  you can use an OnOpenLayout trigger script to do a find when that layout is been navigated to

                                  and even use the security setup to create logical predicates to control with get ( LayoutName ) matching the particular layout in question for what shall be visible there ...

                                  Just out of curiosity, did you mean OnLayoutEnter? I didn't see an OnOpenLayout trigger and the OnLayoutEnter option worked. I'm using FMP15 so am just curious if the trigger terminology has changed.

                                  1 of 1 people found this helpful
                                  • 14. Re: Database of Customers and Types of Invoices
                                    FileKraft

                                    of course i meant OnLayoutEnter - sorry for the confusion