Return unique values in list

A puzzle for y'all:

I'm trying to generate a list that returns only unique values, like an SQL Select Distinct query.

Essentially, I have a list of stores, and want to show a list of the products carried at each store. I populate this list based on line items on connected invoices. This works well, except if a store has ordered the product multiple times, it will show up on the product listing once for each time it was ordered.

Any solutions / workarounds?

Thanks for reading!


I'm not quite sure I understand your use case. See if this idea can help you. You can have three record types, a store, items in a store (which can also hold summary data), and purchase orders per store. On the submission form of a purchase order, use Record Rules, to update or insert an item record for the store. That will provide a list of unique items per store, and have the orders list connected to the items. You're actually splitting the list of items from the purchase orders this way.