8 Replies Latest reply on Nov 16, 2010 9:19 AM by philmodjunk

    Client Invoice Statements

    NevilleHornsey

      Title

      Client Invoice Statements

      Post

      Hello,

      Newbie here so please be gentle.

      I have a database with a table for clients names, a table with jobs, a table with invoices. I am trying to create a table for showing outstanding invoices, sorted for an individual client and in date order.

      I create the relationship between the current tables based on clients name from a drop down list. I can get the table to show all the invoices ever created just not the ones that are unpaid.

      Should I be trying to sort the invoice records in the invoice layout or am I going the right way round by creating a new table specifically for unpaid invoices?

      Or if anyone knows a way of doing this even more elegantly I'd be pleased to hear from you.

      Best regards,

      invoice.jpg

        • 1. Re: Client Invoice Statements
          philmodjunk

          Using a new table for unpaid invoices is not a good idea. That requires you to physically copy the data from one table to another everytime an invoice changes status from "unpaid" to "paid".

          Instead, you can use a filtered portal (FileMaker 11 only) or filtered relationship to list only unpaid invoices in a portal. If you base your layout directly on the invoices layout, you can perform a find for all invoices where a status field stores a value that tells you the invoice is still "unpaid".

          On another note, using a client name field to link your tables is not a good idea. Client names are not unique, they change their names and it's easy to incorrectly enter a name. Dealing with all of those issues can cause a lot of problems when your client's name is used in the relationship between tables. Instead, you should define a serial number field in your Contact Management table and use it to link client data to other tables.

          • 2. Re: Client Invoice Statements
            NevilleHornsey

            Hello Phil,

            Thank you for your quick reply.

            I am currently using FM9Pro, so don't have filtered portals.

            However my clients do have a unique ID. I'll base my layout on the invoices tab though the table "Job Sheet" has the record of whether I'm awaiting payment or not. So I figure there will have to be a way of sorting via that relationship.

            Any ideas on the best resource for learning about filtered relationships in a portal. I've been crawling the internet and tryings stuff but it still means nothing to me.

            Thank you in advance for any further assistance.

            • 3. Re: Client Invoice Statements
              philmodjunk

              On what layout do you want to locate the portal?

              When you open up Layout Setup... for this layout what table occurrence is shown in the "show records from" box?

              What field in the Invoices table marks it as "paid" or "Unpaid"? You'll need a field in this table, before you can use a relationship.

              We need to know that starting poing before we can set up a relationship that links to the invoices in a way such that only unpaid invoices are related.

              The other alternative, performing a find for all unpaid invoices for a given client can be done without defining any new relationships--it just has to be done on a layout that lists the invoices as individual records instead of in a portal. This approach can be much more flexible than using a portal.

              • 4. Re: Client Invoice Statements
                NevilleHornsey

                Okay I feel like I am getting close.

                I have a "job sheet" & "invoices"

                From a sort (which is scripted) I can get all of the jobs which have an unpaid invoice to appear in "Table View" as a list. Unfortunately this is regardless of which client.

                How do I then perform an additional find for the specific clients?

                Ideally I would like to have a layout where I place a drop down menu at the top. Select a clients name (already have a client list) and from the sorted records described above the outstanding invoices for that client appear in one list on one layout.

                Thank you.

                • 5. Re: Client Invoice Statements
                  philmodjunk

                  It easiest to find the client records then sort by paid status.

                  Make your drop down list attach to a global field (Select global storage in Field Options) and use this as a script:

                  Enter Find Mode []
                  Set Field [YourTable::ClientID ; YourTable::GlobalField ]
                  Set Error Capture [on]
                  Perform Find []
                  Sort [Restore ; no dialog ]

                  • 6. Re: Client Invoice Statements
                    NevilleHornsey

                    Thank you once again Phil.

                    Unfortunately I am confused to the max now. I wrote the script like you wrote and tried to perform the find but it just shows me the current record.

                    I may need to start again or get someone to help / do it for me. Any ideas on the best place to start for doing it myself or where to find a database programmer?

                    • 7. Re: Client Invoice Statements
                      NevilleHornsey

                      snap shot

                      The attached image shows how the results are displayed in table view.

                      Ideally I would be able to sort through the results only displaying for one name. eg. will

                      statement

                      Currently I am manually inserting the details into a spread sheet. I feel File Maker would be able to simolify this process.

                      Thanks for any further assistance

                      • 8. Re: Client Invoice Statements
                        philmodjunk

                        A summary report based on the invoices table with related fields from Job Sheet would allow you to create this report.

                        Here's a tutorial, on summary reports that you can look at for a step by step description on how to set them up.

                        Creating Filemaker Pro summary reports--Tutorial