Count of Repeating Values in a Field

[Michael_Harriso]

I have an orders table that records all of the orders we get. Each line item on the order gets it’s own record on the table. I have a field in that table that is order ID which is the same order ID for each line item in an order.

I’m trying to get a count of the number of times an order ID appears. So if a customer orders three items I’ll have this field return “3” so that I know there are three items on this order. I can’t do a rollup or a count since order ID isn’t a linked field.

Basically I just need a count of how many times a value repeats in a field.

[ScottWorld]

Group your records by Order ID, and then use the summary bar.

[Michael_Harriso]

Thanks Scott. The problem with that solution is that I’m going to use the field I’m trying to get to in another field. I’m basically creating a warning field and one of the warnings is that there are multiple line items on an order (so that we make sure an entire order is shipped together). So if the number of times and order ID appears >1 it will kick out a warning. I can’t do that from the summary bar.

[ScottWorld]

Ah, right. It’s a true shame that we can’t use summary bar numbers in our formulas. It’s really one of my biggest desires for the product. If you wouldn’t mind, please add that as a feature request in the #show-and-tell:product-suggestions category.

The only way that you can do what you want is either by writing your own custom JavaScript, or by linking records across tables & using lookup/rollup fields.

[Justin_Barrett]

Consider adding an [Orders] table, where each record is a single order, and will have a unique order ID. That ID could be the primary field if you prefer, or a secondary field that is perhaps added to a more descriptive primary field via a formula. Link line items to their respective orders, and then you can see immediately on the [Orders] table (using a count field) how many line items are tied to that order.