I have a por tal that contains purchase orders. I'm trying to combine records which have the same Purchase Order No & same product code, so that the Qty column gives a total quantity of the combined items.
Make a composite key as a stored text calculation from Purchase Order and Product Code.
Find records with same composite key, sum Quantities. Delete all but one of this records and set new Quantity.
@pauldstewart and @thgagel , welcome to our Community !
That method achieves the stated goal, however it is highly destructive. There may be other data stored on the records which will be lost if they are deleted.
Generating a total of products by purchase order is easy to do in a sub-summary list. Is that an option?
The behaviour of related records is context sensitive. What is the relationship between the layout table name and the portal? Is it a customer context?
1 Like
what @thgagel proposes would actually be merging of records into a single one, thus deleting any duplicates. I'm not sure that's what you want. Sounds like you just want to display the totals and retain the existing records as opposed to deleting them.
Attached is an example. There is a relationship from PO to PO for matching on product code and PO number. This relationship for any given record gives you all matching records for PO/Code combination.
Using that, you can add a calculation which is the total of all quantities for those records.
The next step is to have a portal just showing 1 row for each unique combination. it doesn't necessarily matter which record, so long as there is 1.
To do this, a portal level filter is applied, and it is using the same relationship that was built to determine the total.
The filter just looks through the relationship, and if the portal records primary key value matches the first record through the relationship, it is shown. Only 1 record will ever be the first for any given combination, thus only 1 record shows per combination.
PortalSummaries.fmp12 (420 KB)
1 Like