I'm attempting to calculate the total in a line item file for under the following conditions (and unfortunately I need to have the solution in a field in a layer and not a report).

Each record in the line item file contains a name, a dollar amount total (a calculation), and a pop-up list.

(The name is a product supplier name and will appear multiple times).

I need to find the total for each name (supplier) in the line item file by looking at each record, determining if the pop-up list has a specific selection, and then adding that record’s total to a grand total for that name.

I'll try and have an example graphic of how the line item file looks here:

Thanks

There are several ways to approach your problem. What I would recommend is the creation of a dynamic calculating summary field based on a self-join. To do this you would need to:

1. Create a calculating field called "Summary_[key]" (result type = Text) with a formula along the lines of Case(Status = "NOT ORDERED", SupplierID)

2. Go into 'Define Relationships' from the File menu and create a relationship called "NotOrderedSummary" which links the file to itself, matching "Summary_[key]" to itself on both sides of the relationship.

3. Create an unstored calculation (result type = Number) called 'SupplierTotal' with the formula: Sum(NotOrderedSummary::LI total).

4. Place the 'SupplierTotal' on your layout in the line item file.

That's it. Now all the line items showing a Status on "NOT ORDERED" for a particular company will also display the current balance of all "NOT ORDERED" items in the file for that company. The balances will update dynamically as changes are made or new records added.

Got it to work! Thanks!

The only thing I want to do now is to have a field for each supplier on a single layout so that the totals for all suppliers can be seen at a glance.

Thanks.

The most common way to create a summary of amounts by all suppliers would be by previewing a sorted report which summary within sub-summary layout parts by supplier.

However if you prefer dynamic calculating fields directly placed on a standard layout (as with the previous solution I suggested on this thread) that can also be done. The approach is not dissimilar from the one I previously described. What you should do is:

1. Create a separate calculating field for each supplier, with a formula along the lines of: Case(Status = "NOT ORDERED" and SupplierID = "Company A", SupplierID).

2. Define a separate relationship for each supplier which links the file to itself, matching "CompanyX_Summary_[key]" to itself on both sides of the relationship.

3. Create an unstored calculation (result type = Number) called 'CompanyX_SupplierTotal' with the formula: Sum(CompanyX_Summary::LI total).

You'll need one of sets of fields and relationships hard coded for each company, and will then be able to display the CompanyX_SupplierTotal fields on a layout of your choosing.