Adding a unique value count to a subsummary

Discussion created by Jon1 on Jul 27, 2018
Latest reply on Jul 27, 2018 by Jon1

I have an inventory application that tracks large rolls of paper. Each roll comes from the mill with a unique 11-character roll number. We can receive the rolls as a single “pack”, double packs and triple packs. By that I mean we might have a single roll by itself that weighs 1000 lbs. A double pack can contain two rolls that each weigh 500 lbs. and a triple pack might contain 3 rolls that each weigh 333 lbs. Even rolls in a multipack contain a unique 11-character roll number where the first 10 characters are the same and the only difference is the 11th character.


When a physical roll count is taken, each pack is counted as 1 regardless of how many rolls are in it. This is where my problem is. The inventory applications tracks by the roll – not the pack. What I need to do is also provide a number of “packs” so that there is something to compare the physical count to.


My approach was to convert roll numbers to pack numbers by creating a new field “RollNumber_Short” and take the first 10 characters of the roll number using the left function.

Left ( GR_OW_RollNumber; 10)

Then I created a value list of the RollNumber_Short field. This filters out the duplicates/triplicates. I then used ValueListItems ( "working_inv072618"; "GROWRollNumberShorten" ) to show a list of the now unique “pack” numbers. I then used ValueCount ( ValueListItems ( "working_inv072618"; "GR_OW_RollNumber_Shorten" ) ) to count the pack numbers in the value list.


This all works.


However, I want to add this to a report that when ran, we will get a total # of packs per type of paper per location. My report works except the ValueListItems and ValueCount. I want the ValueCount values in the subsummary when sorted by location. Only the first pack number and the count of 1 show in the subsummary (I only added the ValueListItems to the subsummary for testing purposes). Also for testing purposes, I moved both fields into the body of the report. They do not work at all until I add in a new record to the report view. Then they both work.


My questions is, how can I basically count the number of unique values and put them in a subsummary field?


Thank you for any help.