But exactly what are you totaling?
All the records shown in the portal or just some of them?
If just some of them, what determines which portal records are included in the total and which are not?
Each record in the <Line_Item> table includes an "Unit_ID" field that identifies which Unit the line item is related to, and a "total_price" field. (cost, times quantity less discount). I want to sum the "total_price" fields for each unit.
Using the example below I want Unit 1111 to display a total price of 200,000 and unit 2222 to display a total price of 100,000, and Quote ABC to display a total price of 300,000.
Quote ABC Unit ID Total Price 1111 100,000 1111 100,000 2222 50,000 2222 50,000
And which do you want to see in your portal?
Individual line items are still displayed
Unit ID Total Price
individual line items replaced with one row for each unit ID
The first option can be set up via a self join relationship that matches LineItems to a new table occurrence of the same but with two pairs of match fields, the quote ID and Unit ID fields. Then Sum ( NewTableOccurrence::Net ) can be used to compute the subtotal for each unit.
ExecuteSQL could also be set up to compute and display the same value and this would not require adding the additional table occurrence to your Relationship map.
Ideally, each line in the portal for the UNIT will display one grand total for all of the line items related to it.
Currently, I can get a total for whatever unit is currently selected, but that only works on the screen, and only if I click somewhere outside of the portal. That will not carry over to a printed version of this layout, which I eventually have to do.
I am researching some posts where people have asked for a FileMaker version of SumIf (from Excel) which might be the right direction. Most things I have found assume that you are able to enter a constant into your calculation (like a month). I cannot do that because my identifying field is calculated and I may have hundreds of them over time.
The method that I am describing will provide a total for every unit you list in Line Items. No SumIF--which really can't be done except via ExecuteSQL--you have to get the same result with a "work around", needed.
But it would display that sub total as a column in the portal as you can't add sub summary layout parts as a row inside a portal.
I don't think that I have anything in the <Line_Item> table that will allow me to relate it back to the <Quote> table directly. Line items are related to Units, which are then related to Quotes. I'll have to stare at it a while and see if I can see somehow to connect it.
While you already have a relationship that links quotes to line items--otherwise your portal wouldn't work, this isn't what I am suggesting. You would link LIneitems to an additional Tutorial: What are Table Occurrences? based on LineItems, not Quotes.
Well, the link between the Quote and the Line Items is a field that is set whenever you click the "select" button. It only shows the current record, so it won't work for all of the records.
If I create a self-join on the Line Item table, I don't have a quote field to use as the second relationship. I suppose that I could try to set a field with the Quote ID when I create a line item?
If I create a self-join on the Line Item table, I don't have a quote field to use as the second relationship.
I'm afraid that statment does not make any sense.
You already have the fields that you need or your portals would not work on your layout as they do now.
While the names will be different, you appear to have these relationships now and they are the standard relationships used in most invoicing systems:
Quotes::__pkQuotesID = LineITems::_fkQuotesID
ProductsServices::__pkProdID = LIneItems::_fkProdID
So if you duplicate the LIneItems table occurrence, you can set up this relationship:
LineItems::_fkQuotesID = LineItems|SameUnit::_fkQuotesID AND
LIneItems::_fkProdID = LineItems|SameUnit::_fkProdID
Then you can define cUnitSubTotal as a calculation field in the LineItems table with "lineItems" specified as the context table:
Sum ( LineItems|SameUnit::Total Price )
And you will get a different sub total in the cUnitSubTotal field for each unit specified in the portal tot LineItems.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Can't seem to get anything to total at all, it just puts the first record in the <line items> table into each unit. I am confused about Sum ( LineItems|SameUnit::Total Price ) What is |SameUnit? How do I express that?
Crap, I just found an error on my side. Still working on it...
But I can certainly include the QuoteID on the Items table.
Yes, but it will have to be a stored, indexed field in order for this to work and many methods you might use for that won't be stored/indexed.
An auto-enter calculation can copy the value.
But what purpose is served by placing Units between quotes and Items? Is the layout you show'd based on Quotes or Units?
The layout is based on quotes. A quote can have multiple units, so there is a direct relationship there and a corresponding portal.
Each unit however, is made up of a series of line items that determine the configuration and price of the unit. That is the portal on the right. The relationship there is between whatever unit is currently selected and the quote. When you click the select button, it sets a field in the QUOTE table as that UNIT_ID. So that field is constantly changing. It is a one-to-many relationship, but the one can be manually changed by clicking "select".
However, whenever you add a line item, the new record includes the current Quote_ID and the UNITI_ID for the active unit. So I think there is a way to use that to calculate the summary.
I need a field in the <UNIT> table that says:
"go out to the <Line Items> table, and sum the "TOTAL COST" field for all records in the table where the "for_UNIT_ID" field matches the "ID" field of this record, and put the total in this field. And update it as new records are added to or deleted from the <Line ITems> table so that it i a running total."
That would give me a total cost for each unit that I could display as I am building a unit. Then I could add all of those units to get a quote total.