Jump to content
Sign in to follow this  
Therber

Calculating Total - Not Report

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

Share this post


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.

Share this post


Link to post
Share on other sites

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.

Share this post


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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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