Self Joins and Getting All Sums to Show.

[Therber]

I have a line item file with products to be ordered by a business. Each product has a field called Supplier_Code indicating the company which supplies that product. I want to sum the total dollar amount of all products from each supplier and display the total for each supplier on a single page in browse mode. I know I need to use a self join and a sum(selfjoin::supplier_code) calculation, but I have not been able to get this to work properly.

Am I correct that I need to create a separate self join for each supplier?

Do I need to use a calculation with a Case and Status?

Every time I try this (assuming my self joins are correct), only one supplier total at a time will display in browse mode (related to the record number). What do I need to do to get all sums to display at the same time in display mode? It could be that I just need to make a change in the layout to have all sums display at once.

Thank You

[Pupiweb]

You'd be better off using a subsummary layout using the SupplierCode as the break field ... this however will not function in browse mode

You have alternatives:

1. Use selfjoins, as you're doing. That's fine (as long as you always want totals on all the line items) but you'll have a record per line item: you need to add a script to "filter" only one record per SupplierCode

2. Use a field calculated as GetSummary (Total,SupplierCode)

this will return the subtotal by SupplierCode once you've sorted records by that field, but you're left with the problem of having a record per line item rather than per SupplierCode

It has the advantage to calculate on the found set

3. Run a looping script that will write in a global field the total-per-SupplierCode using the GetSummary (Total,SupplierCode), one line per SupplierCode

4. Why not showing these totals in the Suppliers file?

Run a script that puts the SupplierCodes in a global text field, separated by returns; relate the global to the SupplierCode field in Suppliers and use a Go To related Record step to isolate relevant Suppliers: make a calc field Sum(RelationToLI::Amount) to show the total-per-Supplier