define a summary field in the stock table to be a total of Currency.
then set up a 1 line portal with the same relationship and filter as the main portal with that summary field in it.
Or define a calculation field in Jobs that uses Sum ( JoinTable::currency ) to compute the total. Mark's method is best if you use a portal filter, the Sum Function, however, updates with less trouble if you are editing the currency fields in the portal.
Thanks for the replies.
Mark's solution NEARLY works except that if I have the same stock item listed more than once, it is only included in the total once. For instance if the stock items for the job are codes A, B, C, D and then B again, the second B is not included.
We've assumed you wanted a total for all the items visible in the portal.
If this is not the case, we'll need a more detailed description of what you have in the portal and how you want to compute totals from it.
Just guessing here, but you might be able to do what you want with a series of one row portals, each filtering for a specific item if you want a sub total of each item listed in the portal.
Yes, your assumption is correct but it's not working. I have attached a PNG files which will show you the problem.
It shows that the job has used 2 stock items wap235 but that only 1 has been included in the total field.
Please let me know if I can provide any further information to help with solving this.
We need to know exactly how you have defined the summary field and in which table. We need to know exactly how you placed this field on your layout.
This method works. I've used it many times.
If there is no portal filtering being used, you do not need to place the summary field inside a one row portal, you can place it directly on the layout.
Thinking I may have made mistakes when creating the Portal, I have deleted and re-created it and now nothing shows in any field!
Here is the scenario:
Job_ID to fkJob_ID
Stock_ID to fkStock_ID
What I am trying to do is "debit" a Job with the various components used, and create a total of the field Retail_Net. I created a Summary field (Total_Retail_Net) in the tblStock as a Calculated Field (total of Retail_Net).
Thanks for your assistance - and patience!
I would assume that the layotu is based on tblJob.
Does the portal show tblStock or tbleJob_JOIN_Stock in "Show related records from" in portal setup...?
Yes, the layout is based on tblJob.
The portal shows that the related records are from tblStock.
It should refer to the join table instead. If needed, fields from tblStock can be added to this portal, but each row in the portal should represent a record in the join table.
Compare what you are trying to set up to this invoicing demo file created by Comment:http://fmforums.com/forum/showpost.php?post/309136/
Thanks. I will alter as you state and also look at the demo file. Once I have done this I will let you know the outcome.