10 Replies Latest reply on Aug 3, 2017 11:47 AM by techt

    Script to create monthly invoices

    lmack

      I am going to take a one-table database that has been used only for data entry (and treated like a spreadsheet) and add an invoicing feature, so the data entered can populate invoices and make the workflow easier.

       

      The users want to invoice once a month. Invoices are generated by location. Not every location will have an invoice every month. Invoices will take the data entered as line items and show them in a portal on each invoice.

       

      I'm struggling with how to auto-create invoices that group by location so I don't end up with a separate invoice for each line item. I'm come up with a hypothetical script that I think will work, but it seems clunky and I'm hoping someone can suggest a more elegant solution.

       

      There will be three tables: data entry (line items), invoices, and locations.

       

      Here is my current method (untested):

      Add a script trigger to the line items table in the Location field (which is also the join field to the Location table)

      Import (update matching records in Invoice table, add remaining records as new) - will import the invoice ID and location. This should create new invoices and prevent duplicate invoices for the same location/same month.

      To make this work, I think I need to have an invoice ID calculation in the line items table that creates an invoice number from locationID.month.year. So the July invoice for location 19 would be 19.7.2017. This will ensure that the line items that belong together get matching invoice IDs.

       

      What I was hoping to do but couldn't figure out how was to write a script that would scoop up all the records from the month to be billed (previous month) and create the appropriate invoices. Any suggestions on how to do this?

        • 1. Re: Script to create monthly invoices
          techt

          Some of this will be controlled by the table you're creating the report on. We have a similar system that we use and the report is build on the line items table, that has relationships to the invoice and location tables, though you could build the report from invoices alternatively.

           

          Once line item(s) are set to invoice, we create a new invoice number, add that to the line items (this links the relationship between the tables), then referencing that invoice number from the report layout will gather all of the line items under that invoice number.

           

          The difference here is that the line items are getting the invoice number from the invoice table (or another source) and the same invoice number can be on multiple line items. That way they are grouped on the invoice instead of separated into different invoices.

           

          HTH

          • 2. Re: Script to create monthly invoices
            lmack

            Thank you! My challenge is that I'm trying to automate this task. I want the script to create multiple invoices to capture all of the previous month's line items and group them onto invoices by location.

             

            I wonder if your approach would work for me if I could loop through line items and add the first invoice number to all the line items from the first location, a second invoice number to those from the second location, etc.

            • 3. Re: Script to create monthly invoices
              techt

              Sounds feasible to me. I don't think you need a loop, if you will, a related report from the other table (line items or invoices) would work. Just do a break in the report based on location and you're set.

              1 of 1 people found this helpful
              • 4. Re: Script to create monthly invoices
                lmack

                So you're suggesting running the monthly invoices as a report? I like it! That would eliminate any locations for which there were no line items that month.

                 

                In fact, I might be able to eliminate the invoices table and just run the report and all the invoicing functions through locations. The only downside is that I wouldn't have invoices in the database and it might be harder to track paid and unpaid invoices. But I could create a report for unpaid invoices that would allow them to rerun the bills.

                 

                thank you! Great ideas.

                • 5. Re: Script to create monthly invoices
                  techt

                  Originally we skipped the invoice table and that worked for a number of years. Over time though, we wanted more of an overview, and a view by client to their invoices, which an invoice table would make much easier. We're currently revising our solution to cover that.

                   

                  As to not having an invoice table, you can still have an AP and AR report layout that shows all "invoices" that you haven't marked as paid, so you still have visibility into that aspect of your system. You could even build a statement report from a similar view to show people past dues amounts, etc..

                   

                  Glad you like. As I mentioned, we've used ours like this for years and we've been very happy. Just as time going on, we're looking for more details. On the plus side, you can always add and not lose anything.

                  1 of 1 people found this helpful
                  • 6. Re: Script to create monthly invoices
                    lmack

                    One more question: using the report technique, how do you mark "invoices" as paid? Do you have to mark each line item as paid?

                     

                    Thanks for all your help!

                    • 7. Re: Script to create monthly invoices
                      keywords

                      I would not eliminate the Invoice table if it were me. Think of a table as defining an entity—Location defines a place; Invoice Item defines an object or service; Invoice defines a billing document. Think of fields as being characteristics of the entity their table defines. You CAN build a layout within your line item table (or your location table) and produce a form that looks like an invoice, but to do so almost certainly requires creating fields within that table which are in reality characteristics of an entity other than the table they are part of. It makes better sense in the long run to keep your data structure sound.

                      1 of 1 people found this helpful
                      • 8. Re: Script to create monthly invoices
                        beverly

                        Yes, you can have a "Paid" flag field. I also have related payments (payment splits, because one payment may be for several invoices or be partial payment for an invoice). IF the total payments do NOT equal the invoice, I have a big red:

                             UNPAID

                        everywhere I need to see it.

                        Beverly

                        1 of 1 people found this helpful
                        • 9. Re: Script to create monthly invoices
                          lmack

                          I couldn't get an invoices script to work based on the way they enter data right now - but I set up an invoice report and it works beautifully and actually solves some problems they would have had with monthly invoicing.

                           

                          Thank you for this suggestion! I would not have though of that.

                          • 10. Re: Script to create monthly invoices
                            techt

                            Glad it helped! Have a great day!