9 Replies Latest reply on Nov 22, 2010 11:42 AM by philmodjunk

    Sumary of categorys

    AlexGodines

      Title

      Sumary of categorys

      Your post

      I got a setup where i make my invoices. in the same invoice layout i want to be able to make a sumary of the categorys in the line items in another portal  or anywhere else. i want to do this because when i do invoices i give discounts on categorys and there all different % i want to be able to use this sumary to give diffrent amounts of disscounts to each category without effecting each other. any ideas or help is appreciated

        • 1. Re: Sumary of categorys
          philmodjunk

          How many categories do you have? (What is the maximum number possible for one invoice?)

          Do you have a field in the lineitems table that identifies the category for that line item?

          • 2. Re: Sumary of categorys
            AlexGodines

            Max Possible categorys is 10. yes i do have a field in the line items that identifys the category for all the lineitems.

            • 3. Re: Sumary of categorys
              philmodjunk

              Hmmm, with 10 max, I can see the advantage of using an additional portal. Given the type of relational links needed, I was considering whether a portal was really needed, but that scroll bar equipped portal makes sense.

              Your basic table relationship should look like this:

              Invoices----<LineItems>----Products

              What I am picturing in my mind is this addition to your relationships:

              Invoices----<Category_LineItems>---LineItemsByCategory

              Where LineItemsByCategory is a new table occurrence of LineItems.

              The fields would match up like this:
              Invoices::InvoiceID = Category_LineItems::InvoiceID

              Category_LineItems::InvoiceID = LineItemsByCategory::InvoiceID AND
              Category_LineItems::Category = LineItemsByCategory::Category

              This would enable you to define calculation fields in Category_LineItems that pull totals from related records in LineItemsByCategory.

              The trick to making this work is that you'd need to add a record in Category_LineItems each time you add a line item record in the invoice portal that belongs to a new category. This can be done with script trigger controlled scripts.

              • 4. Re: Sumary of categorys
                AlexGodines

                Or is there a way i can make it so i can choose from fields with drop down list and then give them a % of disscount.

                • 5. Re: Sumary of categorys
                  philmodjunk

                  Certainly. Since you have a category field in line items, you can set up calculation fields that compute a discount based on the item's category.

                  It's hard to go into detail without a clearer picture of how the item's category affects any potential discount.

                  • 6. Re: Sumary of categorys
                    AlexGodines

                    Well i got a summary page i was thinking i can use the subtotals are all listed for each category already all i would need to do this is a calculation right now each time i try it, it keeps discount the total invoice instead of the subsubtotal. im not sure if im getting the calculations rightl.

                    • 7. Re: Sumary of categorys
                      philmodjunk

                      There's a function for that: GetSummary. It will allow you to get the subtotal of a summary field rather than the grand total so that you can use it in a calculaiton.

                      • 8. Re: Sumary of categorys
                        AlexGodines

                        Ok pretty much got it so i can take a percentage from the subtotal of the category that works fine so what im trying to do now is get that to subtract from the subsubtotal but then it just shows a question mark this is what im doing.

                        Sub_Subtotal1:   GetSummary ( Subtotal Sum ; Category ) - Discount

                        Discount: Subtotal Sum1 * Discount_Rate

                        Discount_Rate: Number

                        Not sure what im doing wrong any advice would be great

                        • 9. Re: Sumary of categorys
                          philmodjunk

                          What kind of field is Subtotal Sum1?

                          You can get a ? in a calculation field for a number of reasons. It can even be due to the field being too narrow to display the value. You can click into the field and see if this is the case.