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?
Max Possible categorys is 10. yes i do have a field in the line items that identifys the category for all the lineitems.
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:
What I am picturing in my mind is this addition to your relationships:
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.
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.
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.
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.
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.
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
Not sure what im doing wrong any advice would be great
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.