Count

siyak · 2008-06-30

Hi pips i've created the a report and i need to see the missing no between the First number and last no.i need a code to find if there's a number that is missing in that range,eg 1 to 10 and i want to see if there's a number which is not there for instance 6. :-k

Savatage · 2008-06-30

This is probably a translation problem. It's not clear what you are trying to do. What is this report doing and what do you want to have happen?

i want to see if there's a number which is not there

:?: :?: :-k

kine · 2008-06-30

Easies is to remember previous record and just look at the difference between the numbers. Or you can go through integer table and look if the number exists etc...

jlandeen · 2008-07-01

I'm assuming that when you mean number in your question you mean something like a customer number or other code that was generated by a number series (e.g. CUST0001 to CUST9999)

I think the fastes way of figuring that out may perform slowly but it'll work (i.e. brute force it).

If you use the Incrstr (increment string function) you can loop through all of the possibile permutations of the number and make sure that they exist in your set of records.

siyak · 2008-07-01

I'm assuming that when you mean number in your question you mean something like a customer number or other code that was generated by a number series (e.g. CUST0001 to CUST9999

Thanks!! thats is what i mean and i've tried to use the INCSTR(NO.),but it looks like it gives me the next number that must in there,so how am i gonna know that there is a number missing there!!![/quote]

Nagi · 2008-07-01

You could try to use GET (or SETRANGE if the field you're checking on isn't the primary key, but it sounds like it is) together with a variable that you have used INCRSTR on. Whenever GET fails to retrieve a record, you know that there was a number missing.

[Edit]
Use FINDFIRST and FINDLAST to get lower and upper bound values in the range you're checking on.
[/Edit]

Sandeep_Prajapati · 2008-07-01

siyak wrote:

Thanks!! thats is what i mean and i've tried to use the INCSTR(NO.),but it looks like it gives me the next number that must in there,so how am i gonna know that there is a number missing there!!!

Here is the code ..... O:)

Suppose you need to check the missing nos in "No." for Item table.... then

Name DataType Subtype
Item1 Record Item
Item2 Record Item
NoToBeChecked Code
FromValue Code
ToValue Code
MissingNOString Code

Item1.Reset;
Item1.SETRANGE("No.",FromValue,ToValue);
if Item1.FINDSET then BEGIN
MissingNOString := '';
NoToBeChecked := INCSTR(FromValue);
REPEAT
NoToBeChecked := INCSTR(NoToBeChecked);
Item2.RESET;
Item2.SETRANGE("No.",NoToBeChecked);
IF NOT Item2.FINDFIRST THEN
MissingNOString := MissingNOString + ',' + NoToBeChecked;
UNTIL Item1.NEXT = 0;
message('Nos Misssing : %1',MissingNOString);
END ELSE
message('NO Number exists in the given range...!!');

Didn't check myself ...... It should work

As Nagi said use GET if the field to be checked is Primary Key else use SETRANGE. (though I havent done that above

)

jlandeen · 2008-07-01

Ok well I think you will have to know the beginning and the end of the Number range that you're trying to check. Let's say CUST0001 to CUST2001 is what we are checking. This means you need to loop and check 2000 times.

lcTestValue := 'CUST0000';
FOR i := 1 to 2000 DO BEGIN
  lcTestValue := INCSTR(lcTestValue);
  if not lrCustomer.get(lctestvalue) then BEGIN
    clear(lrTEMPCustDNE);
    lrTEMPCustDNE."no." := lcTestValue;
    lrTEMPCustDNE.insert;
  END;   
END;

The above is just some pseudo code so you may have to make some changes but I think it demonstrates the idea. When the code finished the for loop the the lrCustDNE recordset (which I would define as Temporary) will have a set of all customer numbers that do not exist in your existing customer table. You should be able to apply this same logic to whatever string, number or other data you're trying to check for gaps in.

siyak · 2008-07-02

Thanks guyz for the reply they helped a lot!!! especially that code from Sandeep Prajapati

Count

Comments

Categories