Jump to content

Counting Unique Values

This topic is 5652 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

This topic is 5652 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.