6 Replies Latest reply on Sep 13, 2011 1:28 PM by MichaelVoccola

    Editable Summary for invoice system

    MichaelVoccola

      Title

      Editable Summary for invoice system

      Post

      I have a portion of my database that acts essentially as an invoice system. The setup currently works great with one exception; I can't edit the discount percent from vendors (a cost per line-item) once the item in in the inventory.

       

      I do have a section setup that calculated the discount when the inventory is added to the "line-items" table, but if I wanted to change it per-invoice, I am a little stuck without having to do it manually.

      What I am trying to end up with is a portal that shows all the vendors present on the invoice, and a column with the discount percent in it. There, I would like to be able to edit the discount percent and have it reflect accordingly on the line items that vendor has present on the current invoice, without affecting other invoices or the "inventory" table.

       

      I am comfortable with most of the process of creating this, with the exception of the actual portal itself; I am not sure how to actually get only on occurence of each vendor that is present on the invoice in the table and being able to globally adjust that discount amount on all line item records for the invoice.

        • 1. Re: Editable Summary for invoice system
          philmodjunk

          I'm not sure which approach you need here:

          Specify a discount % on the invoice and have it apply to all line items in the invoice--overriding the computed/looked up pct that was added automatically for all the items.

          OR

          Specify a different discount % line by line to override the original discount for just that line.

          Assuming the first option, you can define a number field in your Invoices table, DiscountOverride. Then modify your calculation in line items as:

          If ( Invoices::DiscountOverride ; //compute percentage discount using DiscountOverride here ; // put your original calculation here )

          • 2. Re: Editable Summary for invoice system
            MichaelVoccola

            Apologies for the delay Phil,

             

            On my invoices, which are rental invoices, I have a cost involved for my rental, and each invoice may have more than one vendor. I would like to modify the discount amount for each involved vendor individually, so I can essentially adjust my cost once the items are on the invoice.

             

            Right now, I have a vendor record with a default discount, when I add an item on an invoice that I buy from that vendor, it looks up that discount and performs the approprate calculation so I can cet get my cost. The flaw in the system is that I can't "renegotiate" my discount once the line items are added, without doing each one manually, or including a discount percent field in the line items table.

            Maybe it would be best to perform this with a script? Either way, my end vision for this is to have a portal on each invoice that shows all the vendors I am getting items from for that invoice, and a discount percentage for each vendor so I can apply it to all related line items at once.

            • 3. Re: Editable Summary for invoice system
              philmodjunk

              Sounds like you need another join table. The LineItems table serves as a join table between the rental invoice and the list of items that can be rented. Now you'll add a table that links the invoice to all vendors that supply one or more of the items listed in LineItems for this invoice. You'll need this table to record override discounts for each vendor. The table can "look up" the default percentage, but since these records are in their own table, you can "renegotiate" by editing the values in this new join table.

              I'm guessing you have at least these tables in your database system:

              Invoices----<LineItems>-----ItemsForRent>-----Vendor   (>---- means "many to one")

              We need to add a join table for Vendor_LineItems while keeping the above relationships in place:

              Invoices----<LineItems>----Vendor_LineItems>------VendorsByInvoice

              LineItems::VendorID = Vendor_LineItems::VendorID AND
              LineItems::InvoiceID = Vendor_LineItems::InvoiceID  (Enable "Allow creation of records..." for Vendor_LineItems.)

              where VendorsByInvoice is a new Table Occurence (TO) of Vendor. LineItems::VendorID will be a value looked up from ItemsForRent.

              A script trigger will be needed to add records to Vendor_LineItems each time you add a record to the LineItems table for a vendor not already listed for this invoice.

              If [Not Vendor_LineItems::VendorID]
                 Set Field [Vendor_LineItems::VendorID ; LineItems::VendorID // this triggers the creation of a new related record in Vendor_LineItems]
              End IF

              A number field in vendor_LineItems can look up the standard discount from VendorsByInvoice, but now you can override the discount by editing this field in your portal. Your LineItems calculations can then refer to this field in Vendor_LineItems to calculated the correct discount.

              • 4. Re: Editable Summary for invoice system
                MichaelVoccola

                Phil, would this be something that is backwards-compatible with data already in the system?

                • 5. Re: Editable Summary for invoice system
                  philmodjunk

                  Your new join table won't automatically populate with records for existing invoices. To show this for existing invoices you'd need to, either by hand or with a script, create the needed records in the new join table. But I don't think that you would really need to do that as this new capability would be primarily needed for all new invoices so that you can better manage the discounts.

                  • 6. Re: Editable Summary for invoice system
                    MichaelVoccola

                    Thanks Phil, I will dive into this and hopefully come out with a working solution!