3 Replies Latest reply on Dec 17, 2014 7:17 AM by philmodjunk

    Creating Department Dependent Discounts

    JimO'Connell

      Title

      Creating Department Dependent Discounts

      Post

      Hi All,

      The invoice section of my solution is fairly basic, an Invoices table related to my Line Items Table related to my inventory.  My inventory, and therefore my line items, are split into separate departments: Audio, Lighting, Rigging, Staging, Cabling, Misc, and Extra.  

      On my print layout, which is based on Line Items, I have a Sub-Summary when sorted by department that houses my department totals.

      What I want to do is have 2 separate totals, one pre department discount, and one after the discount, along with the discount for that department.  

      The way I am currently doing it is by creating a separate discount field and total after discount field for each department based in my Invoices Table.  Then layering all the department discounts on top of each other and hiding the object when the Department does not equal the sub-summary department.

      While this works and isn't a terrible way of achieving my goal, I can't stop thinking that there has got to be a more efficient way of doing this. Like one that doesn't require 14 extra fields. But I have been scratching my head for days and haven't come up with a better solution.

      Any advice?

      Thanks

        • 1. Re: Creating Department Dependent Discounts
          philmodjunk

          Assuming that each item in inventory is a member of one and only one department, you can link your inventory table to a department table by a department ID field in both tables. You can then use a single table in the department table to record the percent discount.

          A corresponding field in LineItems can then look up that discount amount and this field can be part of the cost calculation fields. And there's no reason why you can't have a field that includes the looked up discount in it's calculation and one that doesn't with a summary field for each.

          • 2. Re: Creating Department Dependent Discounts
            JimO'Connell

            Unfortunately I don't think that would work for me, for a couple reasons. The department discount is not static, and will need to change from invoice to invoice. Also the discount will be a currency amount instead of a percent, which I think forces me to apply the discount after all the line items have been added together. 

             

            <Quote>Assuming that each item in inventory is a member of one and only one department, you can link your inventory table to a department table by a department ID field in both tables. You can then use a single table in the department table to record the percent discount.

            A corresponding field in LineItems can then look up that discount amount and this field can be part of the cost calculation fields. And there's no reason why you can't have a field that includes the looked up discount in it's calculation and one that doesn't with a summary field for each.<Quote/>

            • 3. Re: Creating Department Dependent Discounts
              philmodjunk

              The department discount is not static, and will need to change from invoice to invoice.

              If it needs to change from invoice to invoice, then I don't see that you have any department discounts as this data would not be specific to a given department and don't see how you would manage this at all. I must assume that there is more to this than I currently understand. If, on the other hand, you mean that the department discount that you specify for today may be different from that used yesterday, the look up method that I described would not be affected by that as ongoing changes to the data would only appear in records created after the change.

              A currency amount discount....

              Is that a different amount for each item in inventory or a flat rate discount for all orders on behalf of that department? Both options can be handled. If the discounted amounts are specific to each item in inventory, you'll need an additional table between Inventory and departments that matches by ItemID to inventory and by department ID to Department and you'd look up your discount amounts from that table.