sequential numbers by question/response

[ThornhillTaylor]

I have survey questions that can change by number at anytime. when the each question has a noted response that is given a special ID.

after all responses have been gathered for a given survey, the grouping starts over at 0.. I want each grouping, no matter how many surveys, to have a unique id for each grouping see below and would be dynamic if/when additional surveys were added with their own questions.

example... 

grouping by question                         should be

0                                  response id             100

0                                                                  100

0                                                                  100

1                                  response id             101

1                                                                  101

1                                                                  101

0                new survey response id             200

0                                                                   200

0                                                                   200

1                                                                   201

1                                                                   201

1                                                                   201

2                                                                   202

2                                                                   202

2                                                                   202

[ed_hayter · Accepted solution]

I think this meets the request. Bit confused about when we get a new response but assuming its when we return to questionID 0 then mark as a new responder. From there some string manipulation to make the responseID

[ThornhillTaylor]

Ed, thanks for responding.. you are correct... the 0 stands for a new question/ new survey has been added

[ThornhillTaylor]

i noticed this doesnt work for any number after 9 .. i.e. 110+ return all null values.. any thoughts?

[ed_hayter]

Had an error in the string function at the end of the calc.

The NULL was the product of mixing data types as the questionID was being stored as an Integer but we were trying to concatenate as a string. Wrapping the second part of the IIF clause in TOSTRING should resovle the issue