I don't see a table for the chemical groups. I would guess that you need something like this:
Order_lineItem_PRODUCT::_fkChemGroupID = ChemGroups::__pkChemGroupID
Then a list view layout based on Order_lineItem_PRODUCT could be used to list the items on one or more invoices and a sub summary layout part could be added to show the name of the Chemical Group and you would sort the records by _fkChemGroupID in order to group them by chemical group. (It's also possible to get an alphabetical order for these groups.)
PS. Since you are familiar with SQL queries, the ExecuteSQL function (NOT the script step) may be of interest to you. It's not the "full up" SQL based query system that you are used to, but it has its uses in FileMaker.
Design note: I noticed that you have this relationship:
order_LINEITEM::_fk_Product = Order_lineItem_PRODUCT::_kp_ProductID AND
order_LINEITEM::_fk_Product = Order_lineItem_PRODUCT::ChemCode
This appears to have an unnecessary pair of match fields. Either you should not have ChemCode linked to _fk_Product or _kp_ProductID and ChemCode store identical values--in which case you only need one of the two fields for use in your Product table.
I did have a chem code table but could not get it to work so then went over to a chem code field in the product table generated by a value list (manually entered.)
I will try again and show you the results
Whether you need the Chem Group table will depend on whether or not you need record any data other than the name for a given chemical group.
If all you need is the name or a code, you don't need the added table, a field in Products will do the job. But a field that identifies a group of products should not be a match field to your line items table.
I started to put a chem group table back in but having got your last post have gone back to a field in the products table which identifies a group.
My goal now is to split transaction no 402 ( a field in the Order Table) so that it totals up the money spent on each chemical group.
The answer should look like
Cereal Fungicide Total £4113.70
Cereal Herbicide Total £3443.00
Growth Regulator £1588.45
Foliar Feed £1184.40
Grand total £10557.47
I enclose a couple of screen shots
Before I make a hash of it again perhaps you could give me some direction
sorry wrong screen shot here is transaction 402
and the relationship diagram
If I understand you correctly, you want a single invoice but with the transactions grouped by chemical group with a sub total for each group. Is that correct?
If so, first take a look at how the invoices starter solution that comes with FileMaker 11, 12 or 13 prints an invoice. It pulls up a found set of lineitem records an a lineitems based layout (The table is called InvoiceData instead of line items in versions 12 and 13), with fields from the Invoice record in the header and the footer.
By printing from such a layout, you can add a sub summary layout part "when sorted by" your chemical group field to group the line items and provide a sub summary "sub head" and/or "sub footer" for each such group. You can use a summary field defined in the line items table to show the sub total for each group if you put that summary field inside the sub summary layout part.
If, on the other hand, you want to show a "summary recap" listing just each chemical group and the sub total for it at the bottom of your layout, use ExecuteSQL to produce a small "table" of that information inside a single field: FMP 12 Tip: Summary Recaps (Portal Subtotals)
I want to group the chemical groups on Transition No which may have more than one invoice.
I will look at the examples as you suggest thanks
The Invoice DB on from the starter solution is very impressive-- far beyond me at present .
I know how to group now
if I could just get the product field CHEM CODE onto the line item layout from the PRODUCTS table.
How can I do this simply ?
I enclose screen shots
You can enter layout mode and use the field tool to add any field from products (such as ChemGroup) to the portal row (if you have a portal to lineItems) or the layout (if the layout is based on lineitems) and it will correctly display data from products for the selected product in that lineItem record.
But you have several table occurrences of lineItems and several table occurrences of Products. So you need to select the Tutorial: What are Table Occurrences? of Products that is directly linked to that portal or layout's table occurrence for line items from the drop down at the top of Specify Fields before selecting the ChemGroup field that you want to add to your layout.