Does anyone know how I can have a calculation field that counts the number of unique values in a field?

I have a line item portal on my purchase order layout. Each line item is tied to a workorder via a workorder ID. I would like a calculation to return a value representing the number of unique workorders included in one purchase order.

I want to use a value of "1" (number of unique workorders) so that I know the entire purchase order contains products for only one workorder. Likewise a value of "2 or more" (number of unique workorders) would tell me that the purchase order conatins products for many workorders.

There are a couple of ways to get the result you want. You could use a recursive custom function with GetNthRecord but using a value list will be much easier. Start by creating a conditional value list showing the same values as your portal. In your case, you will want to display the workorder ID in your value list. Since a value list only displays unique values, you will have a value list with just unique work orders. You can grab all the values from a value list in a return-separated list using the ValueListItems function. Then, use the ValueCount function to count the number of values. Here is the formula:

ValueCount(ValueListItems(Get(FileName); "ProductsForInvoice"))

I have attached an example demonstrating the technique by including a calculation that returns the number of unique values. Make sure this calculation is unstored. But, you could use the same formula in a script if you want.

You'll need to add a Case statement to the formula to make it tell you whether you have one workorder id or more than one, but the hard part of the formula is already done.

BTW, my solution counts the number of unique products not workorders. All you have to do is modify the value list but I wanted to make sure it was clear.

workorders.fp7.zip

VERY COOL!

I never would have thought of approching it this way.

Thanks a ton.