Jump to content

Calculating Total - Not Report


This topic is 6566 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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:

example.gif

Thanks

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

This topic is 6566 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.