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?
This should work.
If the values list that contains the 'rejected' values isn't global, make it global.
Create a calculated values list using the same global values list as above with the calculation pointing to the values list in #1
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] )
For the calculation order make sure the calculated values list (#2) is BELOW the calculated numeric field (#3).