How would you increment a value when every time a single record is rejected multiple times?

Question

I am trying to capture a number when a record gets rejected multiple times until it is approved completely. For example, lets just say, a questionnaire record which has been submitted for review, was rejected by the representative and has been resent for review. So that would be rejection 1. If the submitter has resubmitted and has been again rejected for lack of information, that's rejection two. How do I capture it? I do have workaround, like I can create a sub-form for every rejection and count the rejections. But I would like to avoid that. I was thinking SELECTEDVALUENUMBER function, but I just can't think of how to increment it. Any ideas?

Answer 1

This should work.

1. If the values list that contains the 'rejected' values isn't global, make it global.

2. Create a calculated values list using the same global values list as above with the calculation pointing to the values list in #1

3. Create a numeric calculated field with the following calculation. [Status] is the field the user selects 'Rejected' the [Previous Status] is the calculated values list in #1. [Times Rejected] is this calculated numeric field.

   IF( AND( CONTAINS(EXACT,VALUEOF([Status],"Rejected")), NOT(CONTAINS(EXACT,VALUEOF([Previous Status],"Rejected"))) ), SUM([Times Rejected],1), [Times Rejected] )

4. For the calculation order make sure the calculated values list (#2) is BELOW the calculated numeric field (#3).

Answer 2

They are same. So here's what I did. For #2, I created a calc'd values list pointing to values list in the main field. So the calc in #2, would look like Previous Status =[Status] and the numeric calc would look like :
IF( AND( CONTAINS(EXACT,[Status], VALUEOF([Status],"Rejected")), NOT(CONTAINS(EXACT,[Previous Status], VALUEOF([Previous Status],"Rejected"))) ), SUM([Times Rejected] ,1), [Times Rejected] )

I had to improvise the IF statement since CONTAINS has less parameters in the first scenario and it throws me an error.

Also, if you are not using the SLECTEDVALUENUMBER function, how does it increment when it is rejected multiple times?