Calculating Sub-Total

[amidyne]

I'm trying to generate a sub-total for my database. Currently I can sum the total value of every part ordered by every company. But what I want is to get a subtotal for the value of each part ordered by sales order (I hope that's not to confusing)

I've included my "pseudo code", and i've commented with //

Sales Container = Sales Order# // Sales Container just a generic variable//

Project ID = 1 //Trying to start with the first project//

Loop

If (Sales Container = Sales Order#)

SubTotal = SubTotal + Amount //Amount being the value of each part ordered//

Project ID = Project ID +1

Else

Sales Container = Sales Order#

Exit Loop if

Project ID = [Total Number of Projects] //I put the value in [] because I couldn't find the appropriate Get Function//

End Loop

[Søren Dyhr]

I would say this isn't requiring a looped script at all, try to investigate this:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

--sd

[amidyne]

Thanks for the link to the site, it looks like I might be able to use some of the info, but I'm still having some trouble, first I'll describe how the data is displayed:

Each new part of a sales order is displayed on its own record. I then have the product series and sales order on separate drop down lists for the user to select from.

And I'll also provide how I've set up my fields in regards to the example on the website: (= is the relationship in the fields, ==is how the field is setup)

Category = Product Series

MonthsTotal = SalesTotal==Total of Sales Order#

Month Totals by Category = Sales Total by Product Series==GetSummary(SalesTotal; Product Series)

Totals of Month Totals by Category = SubTotal==Sum(Sales Total by Product Series)

I have left out the Equivalent to the months field because I don't use repeating fields.

Any more insight into this would be great thanks

[dansmith65]

I'd like to help, but I dont quite understand what's going on. However; If you want to display sub-total's in your database then using summary field's are the way to go.

I can think of three ways to display the appropriate data using summary fields:

1) in preview mode (must have the correct found set, sort order, and summary part defenitions on the layout) (this is the method used in Søren's link)

2) in browse mode using a portal (relationship used for portal determines which records would be displayed/totaled)

3) in browse mode, viewed as list or table (found set determines which records would be displayed/totaled)

I suspect if you research these three methods, you may find a solution.

Are you trying to display these sub-totals while entering data, viewing data on-screen, or printing data?

[amidyne]

Ok so right now I've displayed all the requisite sales order date through a portal, I've set it up to display by product series, there's 10 of them. But each product series is further sub-divided by sales order number. What I'm trying to do is display the total amount by Sales Order # (my subTotal) I should mention that each Sales Order has many Line Items, the sum of the cost of each Line Item will make up the suTotal

I didn't know how to set this up through a portal without creating separate portals for each part, I'd much rather just have the subTotal show up next to each purchase order

Thanks

Edited July 17, 2008 by Guest

[dansmith65]

OK, I think we're getting somewhere here. Try this...

Make a self-join relationship for the table that has the sales order number in it. In the relationship, match SalesOrderNo field to SalesOrderNo field.

Put the summary field from this new table occurence into your portal. That should display the total of the every SalesOrder.

One more question: Does a SalesOrder ever contain more than one product series?

[amidyne]

Simple answer, yes

[dansmith65]

Do you want the sub-total to be a total of the entire sales order, or a sub-total of that product series on the sales order?

Also, do you understand my last post? I'm not sure of your skill level or my clarity :P

[amidyne]

Thanks the related tables worked. I had thought about something similar earlier, but it seemed to easy for it to be right

[dansmith65]

your welcome! I'm glad I could help.