3 Replies Latest reply on Apr 12, 2011 11:37 AM by philmodjunk

    Setting up Invoice Relationships

    user20843

      Title

      Setting up Invoice Relationships

      Post

      I have a basic invoice set up with:

      Customers::CustomerID = Invoice::CustomerID

      Invoice::InvoiceID = LineItem::InvoiceID

      LineItem::ProductID = Product::ProductID

      Which works fine for billing products, I would like to take this ones step further and bill hours. Currently hours is set up as a single product where the quantity is put in but I would like to have the ability to track billable/actual hours. I think I would need two different tables for Product and Billable Hours because I want to add Product by qty and add Hours by start/end times, pulling from a list of multiple types of time so that I can set rules for # of hours billable and track if billable roughtly equals actual.

      What is the best way to get both products and billable hours into the  same invoice if they come from two different table? Through the invoice  table with two line items tables(one for hours and one for products) or through one  invoice lines table? Can this be done without multiple portals on the invoice page?

      Thank you.

        • 1. Re: Setting up Invoice Relationships
          philmodjunk

          I'd add a "billable" field to the lineItems table. Then you can log hours just as though it is a product. But hours not marked billable can be excluded from an invoice presented to your customer if you want that and a report built on a layout based on LineItems can group billable and non-billable entries so that you can track them.

          • 2. Re: Setting up Invoice Relationships
            user20843

            I have gone ahead with this suggestion, added Line Items::Billable (y/n checkbox) and also added this to the protal in Invoices.  Individuals will enter all items and checkbox will be set to yes/no based on a calculation using Item::Category. Then I will go to the Invoice (Line Items portal) and make any changes to the Line Items::Billable field based on contracts.  I took the Invoice Print layout from the Invoice template and used that to print invoices.

            Two questions:

            How do I exclude the items flagged as not billable from the final invoice? Can it be done in the transition from Invoice layout to printing of the invoice (using script from Invoices template)?

            When going from the Invoice layout to the printing of the invoice can multiple line itmes with the same items but different quantity be added together.  For example, if one invoice two line items:

            Product ID = 2, Qty=3

            Product ID=2, Qty=1

            Can the printed invocie read:

            Product ID=2, Qty=4

            I don't want to manually delete the line items records becuase they are used to track which employee billed them and when but I want to send a nice clean bill to our customers. 

            Thank you, I have been able to go from the Invoices template and convert it to something that will really be a custom soltion to our needs pluss add additional service and vendor tracking with the help that I have gotten here.

            • 3. Re: Setting up Invoice Relationships
              philmodjunk

              How do I exclude the items flagged as not billable from the final invoice? Can it be done in the transition from Invoice layout to printing of the invoice (using script from Invoices template)?

              If the layout for printing your invoice uses a portal for the line items, you can filter the portal to exclude the items.

              If you use a list type layout based on the line items (a more flexible approach), you can perform a find or use constrain found set to filter out the unbillable items and your script that takes you to this layout for printing can do this for you automatically.

              When going from the Invoice layout to the printing of the invoice can multiple line itmes with the same items but different quantity be added together.

              Assuming that you set up the list type layout I have already mentioned, you can define a summary field as the total of Qty. Then replace the body part of this layout with a sub summary part "when sorted by Product ID". Place the Product ID, Description and other fields here, but replace Qty with the new summary field. Make sure that you then sort your records by Product ID before you view or print this layout or this won't work as the sub summary part will not appear until the records are correctly sorted.