List Out Missing Numbers of a Number Range

[dylim]

Hi Guys,

Me again. I need to show the user which invoice numbers are missing given a number range.

Suppose the following:

Actual Invoice Numbers:   1,2,3,4,5,8,9,11,12,13,15   (each of these numbers are in their respective invoice records)
Number Range: 1 to 15                                 (passed as a parameter, 2 values)

Missing numbers should be: 6,7,10,14

My knee jerk algorithm would be to:

1. Execute a SELECT statement to get the actual numbers within the range into a cursor.
2. LOOP thru the Number Range and do a SEEK or LOCATE against the Actual Invoice Numbers.

Perhaps the experts can chime in on this?

Thanks in advance. Long Live the Fox!

 

[Chriss Miller]

In general: Google, obviously. Just search whatever you search and add VFP as keyword in front.

Tamar's site: http://www.tomorrowssolutionsllc.com/
Doug Hennigs blog: https://doughennig.blogspot.com/
Rick Strahl: https://weblog.west-wind.com/
Rick Schummer: http://www.rickschummer.com/blog/
Woody: https://woody-prolib.blogspot.com/

Well, again, just google your favorite VFP MVPS, many of them have a website to look into.
But other MVPs (most valuable posters, not the same as Microsoft Valuable Professional) here have blogs, like Mike Lewis http://www.ml-consult.co.uk/
With exceptions, like me. Not interested in maintaining a site and writing content.

 

[dylim]

In general: Google, obviously. Just search whatever you search and add VFP as keyword in front.

Tamar's site: http://www.tomorrowssolutionsllc.com/
Doug Hennigs blog: https://doughennig.blogspot.com/
Rick Strahl: https://weblog.west-wind.com/
Rick Schummer: http://www.rickschummer.com/blog/
Woody: https://woody-prolib.blogspot.com/

Well, again, just google your favorite VFP MVPS, many of them have a website to look into. With exceptions, like me. Not interested in maintaining a site and writing content.
But other MVPs (most valuable posters, not the same as Microsoft Valuable Professional) here have blogs, like Mike Lewis http://www.ml-consult.co.uk/

Ah yes. Been to Doug Hennigs', Rick Strahl's and Mike Lewis'.

 

[mjcmkrsr]

Hi,

Just for fun : sketch of code to show the correct values, the missing values and duplicate values in a given range.

*!*    good values, missing values and duplicates

PUBLIC goFormSales

goFormSales=NEWOBJECT("clsForm")
goFormSales.Show

Read Events

Close all
Clear All

RETURN


**************************************************
DEFINE CLASS clsForm AS form
    AutoCenter = .T.
    Caption = "The good, the missing and the dupes"
    Width = 660
    Height = 420
    MinHeight = This.Height
    MinWidth = This.Width
    MaxWidth = This.Width
 
    ADD OBJECT grdValues AS grid WITH ;
        Left = 12, ;
        Top = 72, ;
        Width = 300, ;
        Height = ThisForm.Height - 84, ;
        TabIndex = 4, ;
        DeleteMark = .F., ;
        RecordMark = .F., ;
        ColumnCount = -1, ;
        RecordSource = "csrValues", ;
        Anchor = 15, ;
        ReadOnly =.T.
 
        PROCEDURE grdValues.Init
             WITH This.Column1
                .Header1.Caption = "ID"
             ENDWITH

             WITH This.Column2
                .Header1.Caption = "Description"
             ENDWITH

         ENDPROC
        
    ADD OBJECT grdMissingValues AS grid WITH ;
        Left = 330, ;
        Top = 72, ;
        Width = 312, ;
        Height = ThisForm.Height - 84, ;
        TabIndex = 5, ;
        DeleteMark = .F., ;
        RecordMark = .F., ;
        Anchor = 15, ;
        ReadOnly =.T., ;
        Visible = .F.
 
    ADD OBJECT lblOData as label WITH ;
        Left = 12, Top = 42, Caption = "OData", FontSize = 12, FontBold = .T.
    
    ADD OBJECT lblResults as label WITH ;
        Left = 330, Top = 42, Caption = "Results", FontSize = 12, FontBold = .T.
    
    ADD OBJECT lblLowerValue as Label WITH ;
        Left = 12, Top = 9, Caption = "Lower value :", AutoSize = .T.

    ADD OBJECT txtLowerValue as TextBox WITH ;
        Left = 96, Top = 6, Width = 48, TabIndex = 1, Value = 1, InputMask = "999"

        PROCEDURE txtLowerValue.Valid()
            IF This.Value < 1
                = MESSAGEBOX("Value must be higher than 0", 16, "Lower value", 3000)
                RETURN 0
            ENDIF
        ENDPROC
        
    ADD OBJECT lblUpperValue as Label WITH ;
        Left = 150, Top = 9, Caption = "Upper value :", Autosize = .T.

    ADD OBJECT txtUpperValue as TextBox WITH ;
        Left = 228, Top = 6, Width = 48, TabIndex = 2, Value = 20, InputMask = "9999"
        
        PROCEDURE txtUpperValue.Valid()
            LOCAL liCheckValue
            
            liCheckValue = ThisForm.txtLowerValue.Value
            
            IF This.Value <= liCheckValue
                = MESSAGEBOX("Upper value must be higher than lower value", 16, "Upper value", 3000)
                RETURN 0
            ENDIF
        ENDPROC

    ADD OBJECT cmdDoit AS CommandButton WITH ;
        Left = 330, Top = 6, Height = 24, TabIndex = 3, Autosize = .T., BackColor = RGB(0, 240, 240), Caption = "The good, the missing and the dupes"

        PROCEDURE cmdDoit.Click()
            LOCAL liUpperValue, liLowerValue
            
            liUpperValue = ThisForm.txtUpperValue.Value
            liLowerValue = ThisForm.txtLowerValue.Value
            
            ThisForm.Caption = "The good, the missing and the dupes between " + ALLTRIM(STR(liLowerValue)) + " and " + ALLTRIM(STR(liUpperValue))

            CREATE cursor csrAllValues (iValue I)

            FOR i = 0 TO liUpperValue - liLowerValue
                INSERT INTO csrAllValues VALUES (liLowerValue + i)

            ENDFOR

            SELECT csrValues.iValue, csrValues.cName, csrDupes.lDupes, .F. as lMissing ;
                FROM csrValues ;
                    JOIN (SELECT iValue, .T. as lDupes FROM csrValues GROUP BY 1 HAVING COUNT(iValue) >= 2) csrDupes ;
                    ON csrValues.iValue = csrDupes.iValue;
                WHERE csrValues.iValue BETWEEN liLowerValue AND liUpperValue ;
                UNION ALL ;
                    SELECT csrValues.iValue, csrValues.cName, csrDupes.lDupes, .F. as lMissing ;
                        FROM csrValues ;
                            JOIN (SELECT iValue, .F. as lDupes FROM csrValues GROUP BY 1 HAVING COUNT(iValue) < 2) csrDupes ;
                            ON csrValues.iValue = csrDupes.iValue ;
                WHERE csrValues.iValue BETWEEN liLowerValue AND liUpperValue ;
                UNION ALL ;
                    SELECT csrAllValues.iValue, "*-" as cName, .F. as lDupes, .T. as lMissing ;
                        FROM csrAllValues ;
                            WHERE iValue NOT in (Select iValue FROM csrValues) ;
                ORDER BY 1 ;
                INTO CURSOR csrGoodDupesMissing

            WITH ThisForm.grdMissingValues
                .Visible = .T.
                .ColumnCount = -1
                .RecordSource = "csrGoodDupesMissing"
        
                 .Column1.Header1.Caption = "ID"
                .Column2.Header1.Caption = "Description"

                .Column3.Header1.Caption = "Dupes"
                .Column3.Width = 54
                .Column3.NewObject("chkDupes","CheckBox")
                .Column3.CurrentControl = "chkDupes"
                .Column3.chkDupes.Caption = ""
                .Column3.chkDupes.Visible = .T.
                .Column3.Sparse = .F.
                .Column3.Alignment = 2
                
                .Column4.Header1.Caption = "Missing"
                .Column4.Width = 54
                .Column4.NewObject("chkMissing","CheckBox")
                .Column4.CurrentControl = "chkMissing"
                .Column4.chkMissing.Caption = ""
                .Column4.chkMissing.Visible = .T.
                .Column4.Sparse = .F.
                .Column4.Alignment = 2

                .SetAll("DynamicFontItalic", "csrGoodDupesMissing.lDupes", "Column")     
                .SetAll("DynamicFontBold", "NOT(csrGoodDupesMissing.lDupes or csrGoodDupesMissing.lMissing)", "Column")     
                .SetAll("DynamicBackColor", "ICASE(csrGoodDupesMissing.lDupes, RGB(225, 225, 0), csrGoodDupesMissing.lMissing, RGB(255, 255, 255), RGB(0, 240, 240))", "Column")     
                
            ENDWITH             
        ENDPROC
        
PROCEDURE Destroy
    Thisform.Release()
    CLOSE ALL
    Clear Events
ENDPROC

PROCEDURE Load
    LOCAL lnI
    
    CREATE CURSOR csrValues (iValue I DEFAULT 1 + INT(RAND() * 29989), cName C(15) DEFAULT "Z" + SYS(2015))
  
    For lnI = 1 To 10000
       Append Blank
    EndFor
    
    INDEX on iValue TAG xiValue
    
    SET ORDER TO xiValue
    
    LOCATE
    
ENDPROC

ENDDEFINE
*********************************************

Enjoy

MarK

 

[Chriss Miller]

It seems though that it is not so much active for VFP posts anymore. I may be wrong on this

That's true, there's a lot of articles about any topic, though.

 

[mmerlinn]

mmerlin,

As you say yourself: "Quick and dirty so likely can be improved upon."
So I won't address that. Just one thing: In FoxPro even up to version 8 an array can at max be 64k items.

But could you point out what doesn't work with RELATION in "plain" FoxPro regarding my RELATION solution? I'm just curious what was introduced later, only.
Is it USE in conjunction with ORDER? If so, you can do that in two lines instead.
Is it SET NEAR? You could also do a second pass with Invnum-1. as said.
Is it the INSERT INTO SQL? I'm quite sure that will require APPEND BLANK followed by REPLACE, but that isn't a big hurdle.

Two nit picking things pop out. Did not dig further.

I (Integer) is not a field option
Dot notation

As for the array length, I was unaware of that since it has never bitten me, but the solution is simple, do a smaller chunk at a time. In this case an array is not likely to ever come close to the max since the array length is only as long as the range being tested.

 

[mmerlinn]

I expanded and turned this into the function NUMGAPS() that I can now use myself, although I did not address the array length issue nor did I address decimal and scientific notation issues.

**********
*
*    Get list of good, missing, or duplicate numbers within any range
*
*        = NUMGAPS(qTableName, qFieldName, qMinValue, qMaxValue[, qOutType])
*
*        qTableName    C:        Table to search
*        qFieldName    C:        Field name in search table
*        qMinValue     N:        Minimum search value
*        qMaxValue     N:        Maximum search value
*        qOutType      C:        Output type
*                                D = Duplicates, G = Good, & M = Missing in any combination
*                                OPTIONAL, Defaults to 'DGM'
*
*        RETURNs with results in cursor named "GAPS"
*
**********

PARAMETERS qTableName, qFieldName, qMinValue, qMaxValue, qOutType
PRIVATE ALL LIKE z*

IF TYPE('qOutType') = 'C' AND NOT EMPTY(CHRTRAN(qOutType, CHRTRAN(qOutType, 'DGMdgm', ''), ''))
    zouttype = UPPER(qOutType)
ELSE
    zouttype = 'DGM'
ENDIF

zCount = qMaxValue - qMinValue + 1
zOffset = qMinValue - 1
zFldWidth = LEN(ALLTRIM(STR(qMaxValue)))

CREATE CURSOR gaps (good N(zFldWidth), missing N(zFldWidth), dups N(zFldWidth))

DIMENSION Array(zCount)
Array = 0

SELECT (qTableName)
SCAN FOR BETWEEN(&qFieldName, zOffset, qMaxValue + 1)
    Array(invnum - zOffset) = Array(invnum - zOffset) + 1
ENDSCAN

FOR z = 1 TO zCount
    DO CASE
    CASE EMPTY(Array(z)) AND 'M' $ zouttype
        INSERT INTO gaps (missing) VALUES (z + zOffset)
    CASE Array(z) > 1 AND 'D' $ zouttype
        INSERT INTO gaps (dups) VALUES (z + zOffset)
    CASE Array(z) = 1 AND 'G' $ zouttype
        INSERT INTO gaps (good) VALUES (z + zOffset)
    ENDCASE
ENDFOR

RETURN

 

[Chriss Miller]

Too bad you don't answer any questions. Well, so be it.

 

[mmerlinn]

Too bad you don't answer any questions. Well, so be it.

If you are talking to me, I DID answer some of your questions, but only those that I could answer. Useless trying to answer questions that I have not looked into. And no point in digging deeper than I needed to dig to determine that your code would not fly in plain Jane Foxpro.