Gap detection when number resets

3 posts / 0 new
Last post
jclausnitzer
Offline
Joined: 01/17/2019 - 13:12
Gap detection when number resets

How do i run a gap detection when the number resets daily and change in sequence. For instance:
Date                                            Check No. 
1/1/14                                          100001 
1/1/14                                          100002
1/1/14                                          100004
1/1/14                                          100005
1/1/14                                          200001
1/1/14                                          200003
1/1/14                                          200005
 
The detection should show 3 gaps. Each day the squence of checks restarts.
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Looks like you would have two choices.  One you break-up the file by date or gap sequence and run the gab detection script on that or you do this through a script that would know the logic of the gaps.

ravisdxb
Offline
Joined: 01/13/2019 - 06:12

Please try creating a new field with date + check number and run gap detection on the same to take care of resetting of the check number. You need to convert date filed to string by using @Dtoc(DATE_FIELD,"YYYYMMD") and convert check number to another string value using @Str(CHECK_NUM,6,0) (if not already a string). Your formula for the new virtual field may look something like:
 
@Dtoc(INV_DATE,"YYYYMMDD") + @Str(CHECK_NUM,6,0)