I have a numeric field called 'Approval Sequence' that I would like to build logic into. Here is the dependent fields for this situation:
1. I have a text - multiple choice field called 'Generate PAR Number?'. The two drop down choices for this field are 'No' and 'Yes'.
2. I have a text - multiple choice field called 'Fiscal Year Spend'. The drop down choices for this field are '2015', '2016', '2017', etc.
Here are my requirements:
1. Once the 'Generate PAR Number?' option for 'Yes' is selected, I want QB to identify the 'Fiscal Year Spend' within that same record.
2. From there, I want QB to look at all of the other records in the table that also have the 'Generate PAR Number?' selected as 'Yes' that also have the same 'Fiscal Year Spend'.
3. I want QB to then populate the 'Approval Sequence' field with the next number in the sequence. For example, if the record we are working in has a 'Fiscal Year Spend' of 2016 and there is one other record already in the table that has that Fiscal Year Spend and has the 'Generate PAR Number' option 'Yes' selected, I want the record I am working in to be the next number up in the sequence (i.e., if the other record's 'Approval Sequence' was 001, I'd want the record I am working in to populate its 'Approval Sequence' with 002).
A little background - this is an application I am building for my accounting department. It is to keep track of approved capital projects. I want to keep a running sequence on all approved projects and I hate having to go and figure out what the next number in the sequence is. It would be beautiful if QB could do this for me! Is this possible?