First idea to come to mind is to create a summary report on a layout based directly on the portal's table instead of using a portal.
However, with that approach, you have to separate laminate data from substrate data into different records and you are using one record for both a substrate and a laminate. (Makes sense for data entry as both laminate and substrate have the same dimensions and you don't want to have to enter this data twice.)
If I am interpreting your example correctly, you can't have a laminate without a substrate but can have a substrate without a laminate. Is that always the case or are there exceptions to that apparent pattern?
If that's always the case, I'm considering this table structure:
"Allow creation via this relationship" would be enabled for both PrintItems and Laminates in this relationship and the Laminate field in your portal would be from Laminates. Thus, selecting a laminate in this field creates a related record in laminates with a one to one relationship so that the dimensions specified for related substrate can be used to compute total area and linear feet for the specified laminates.
Problem with that is that you end up with one summary report on a PrintItems layout totaling substrate figures and a second layout totaling laminate figures--which means even the simplest print job has a two page report to give you your totals. That's not the ideal result here. Another option is to use a script to use the data from the portal to generate separate laminate and substrate records in the same report table--which gets you a report all on one page--but now you have your data duplicated in two tables. I'm just not sure which option I dislike least here...
OK well then I am bumming if i am putting PhilMod in a quandry.
Can you confirm that you never have a laminate without also having a substrate?
I assume you want a list such as:
Avery 2920 Total sq ft Total Lin feet
With one row for each substrate and laminate....
I just had a major Eureka here. I'm going to play with a simple demo file and then check back here to see if you have confirmed the above...
Ok, see if this works for you.....
Take your portal table and define the following fields:
InvoiceID (Foreign key to layout's table)
MaterialID ( auto-enter serial number)
Then, go to the relationship tab and make a new occurrence of your portal table (I'll call it printItems, name it Laminates) and link it like this:
Define the self join relationship between PrintItems and Laminates as:
PrintItems::InvoiceID = Laminates::InvoiceID AND
PrintItems::MaterialID = Laminates::LaminateID
Enable "allow creation of records..." for Laminates in this relationship.
Now return to the fields tab and add these fields:
cLaminateSqFt (calculation field)
Define it as PrintItems::SubStrateSquare Feet and select Laminate from the context drop down.
sTotalSubSqFt (Summary, total of SubstrateSquare Feet)
sTotalLamSqFt (Summary, total of cLaminateSqFt)
cMaterialType (calculation, returns text, If ( LaminateID ; "Laminate" ; "Substrate" ) , clear the "do not evaluate if all referenced fields are empty" box. )
In your portal, add a portal filter: Not LaminateID
Use PrintItems::Material for your substrate field and Laminate::Material for your laminate field.
Now each time you select a laminate, a related record is added linked by Invoice ID, but the portal filter keeps it from appearing and causing confusion in your portal, but now you can set up a summary report using the summary fields (Linear feet fields would be set up just like the square feet fields.)
In this report, you can remove the body layout part and replace it with a pair of sub summary layout parts. Make the first one "when sorted by cMaterialType and put cMaterialType in the layout part to serve as a sub heading. Make the second "when sorted by Material" and place the material field and the 4 summary fields inside this layout part. (You can stack the square feet summary fields on top of each other in one stack and the total linear feet fields in another as only one field of each pair will have data for a given material.)
To produce the report use Go To Related Records or perform a find to pull up all Print Item records with the specified InvoiceID, then sort them by cMaterialType and Material. (without sorting, the report will be blank.)
Sorry i thought I posted this but i must have just hit preview.
Must have a substrate to lam. Sometimes I suppose we could lam someone else's work, but that is really really rare, and i would suppose if that is the case i could just have the opeator enter the lam under the substrate side. I know that is funky but for those rare times it would be OK.
Then the above method should work...
I laid everything out to the best of my understanding. I posted some screen captures above. I don't think everything is correct and i have to admit i have no clue how to get the report to work, I tried a GTRR script but got nowhere.
On data input I Get this error: This field cannot be modified until “InvoiceID” is given a valid value. when I try and click in the laminates field. So I tried making InvoiceID a serial auto-entry and it worked, but you didn't say to do that in your notes. So I wonder if i screwed up.
Let me know if you see anything right off the bat that I meesed up.
PS My table that you call PrintItems I call GraphicsListings
What I call InvoiceID is what you are calling Job#. Remove Invoice ID and use your exisitng Job# field in its place.
Please see your InBox
I sent you an email
I know. Monday's are always crazy and I spent Tuesday evening filing my taxes. I should be able to take a look tonight and get a response back to you.