Re-sequence row number

Get Started
1

I have a query that returns hundres of records and a field called [query].currentrow. This works great as it displays the row number for the entire recordset from 1 to xxx.

However, my customer would like this number to reset when a specific field changes. How does one do that? I am putting some sample code in for claification:

Amount Product Number

130 A 001

155 A 002

104 A 003

198 B 004 (Customer wants this to be 001)

133 B 005 (Should be 002)

176 C 006 (Should be again 001)

<cfloop query=“[queryname]”>

<cfoutput>

#Amount# #Product# #Number#

</cfoutput>

</cfloop>

Thanks in advance for your help!

2

I can’t help but think that there’s a better way to reset that counter variable since you have the groupby parameter in the cfoutput…*but I can’t think of anything right now.

Well with cfoutput group, it’s a little simpler. But not by much. You create a counter variable and use it instead of currentRow. Reset #counter# to 1 inside the outer <cfoutput> and increment it by 1 inside the inner <cfoutput>.

It’s 1 variable instead of 2. But not a massive improvement imo :wink: Too bad there’s no “grouped” currentRow variable or something.


<cfoutput query="q" group="Product">
	<cfset counter = 1>
	<cfoutput>
		#Amount# #Product# #NumberFormat(counter, "000")#<br>
		<cfset counter = counter + 1>
	</cfoutput>
</cfoutput>
3

You’re going to have to use cfoutput with the groupby parameter instead of cflooping through your list. Also, instead of using currentRow you’re going to have to create two variables, one that does the counting for you and then one that holds the value of the field that will be changing. Then in your loop test the previous column value (‘holder variable’) with the current column value (query column) and if they don’t match, then reset the counter variable, else add one to it and output.

I can’t help but think that there’s a better way to reset that counter variable since you have the groupby parameter in the cfoutput…*but I can’t think of anything right now.