How to automate sequence based on existing records?

  • 0
  • 2
  • Question
  • Updated 4 years ago
  • Answered

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?

Photo of Brett Telford

Brett Telford

  • 20 Points

Posted 4 years ago

  • 0
  • 2
Yes, this is possible to do, but requires some setup.  You would need a table containing all the possible combinations of Fiscal Year send and division in the format

2015-NS

2016-NS

2017-NS

2018-NS

etc up to about 20 years out. Maybe as well just to it once

2015-TN



etc

This would be done in excel and uploaded to a new table with that text field as the Key field of the table

Then  you would build that same concatenated field in your details table  as

list("-", [fiscal year spend[, [Division]) 


Then make a relationship and based on that field.

Next a summary count of the # of Projects called [# of Projects].

Then look that up down to the child table

THEN make a numeric field on Projects called [# of Projects snapshot] and set the field properties at the bottom to be a snapshot of the lookup field. 

That should result in a field that shows the last number used.  You should then be able to make an auto sequence number based on that number plus 1.

You will need to do testing on this, perhaps in the copy of your app, and no pressure, but 2016 is knocking on your door now, so you need to get this working in 2015 or you will find it hard to get a proper sequence.  If you need some help with this you can contact me off line via my Profile. Or I can try to help via this forum as time allows.
Photo of Brett Telford

Brett Telford

  • 20 Points
Mark - I follow the logic.  Would I still have to create the external table and relationship based on the edit that I made to the original request above?  Note, I was misinformed about the sequence.  Division does not matter.  We only sequence within each fiscal year spend.  So I would only care about the last number in the sequence for that fiscal year.
You will still need a similar sequence, but the Key field to the table would just be the Year.   Also, when you do that Summary count of the # of projects, the Summary field needs to only count where that [Generate PAR Number] is checked.  But the method will not work if you start flipping that off and on.

Also, I recall now that I did this for a client and it did take me a few hours to get this working.  As I recall now, I have to do a summary maximum of the highest sequence number so far and not actually a summary count since records could be deleted or in your case the [Generate PAR Number] could be changed.

You can read up on what a snapshot field is, and then start playing with the concept.