Bin Contents Filtering Question

thetallbloke

Hi All,

I'm trying to find the best way to filter the "Bin Content" table..

I have added a field called "Prev Qty" to the table and I want to store a value in it and then update all the ones that have changed..

Here is my code...

IF rBinContents.FINDSET THEN REPEAT
  rBinContents.CALCFIELDS(Quantity);
  IF rBinContents.Quantity <> rBinContents."Prev Qty" THEN BEGIN
    rBinContents.VALIDATE("Prev Qty", rBinContents.Quantity);
    rBinContents.MODIFY(TRUE);
  END;
UNTIL rBinContents.NEXT = 0;

I'm not quite sure how or where to put the CALCFIELDS line... Does CALCFIELDS calculate every record at once and therefore am I doing it 4000+ times for every record...??? or does the CALCFIELDS only do the current record..???

I've found that if I take the middle few lines and just have the following code:

IF rBinContents.FINDSET THEN REPEAT
  rBinContents.CALCFIELDS(Quantity);
  END;
UNTIL rBinContents.NEXT = 0;

It takes a number of seconds.. only about 2 or 3 but that seems a long time to me...

What I'd really like to do is have something like:

rBinContents.CALCFIELDS(Quantity);
rBinContents.SETFILTER(Quantity, '<>%1', rBinContents."Prev Qty");

IF rBinContents.FINDSET THEN REPEAT
    rBinContents.VALIDATE("Prev Qty", rBinContents.Quantity);
    rBinContents.MODIFY(TRUE);
UNTIL rBinContents.NEXT = 0;

So that the CALCFIELDS and the Filter return <20 records depending on the system activity, etc...

When I ran the code just above the Quantity field is not calculated and every record is being updated...

Any suggestions would be much appreciated..

My Bin Content table has about 4300 records in it.. 2-3 seconds a normal amount of time to run this code..??

Thanks..

ara3n

Your only choice in CAL is

IF rBinContents.FINDSET THEN REPEAT
  rBinContents.CALCFIELDS(Quantity);
  IF rBinContents.Quantity <> rBinContents."Prev Qty" THEN BEGIN
    rBinContents.VALIDATE("Prev Qty", rBinContents.Quantity);
    rBinContents.MODIFY(TRUE);
  END;
UNTIL rBinContents.NEXT = 0;

Calcfields calculates one record at a time. That is why it has to be in the repeal loop.


You can use ADO and write your own sql statement is another option.

kriki

[If you found the reply to your question, why not post it? You may help others that way.]

Andwian

thetallbloke wrote:

I have added a field called "Prev Qty" to the table and I want to store a value in it and then update all the ones that have changed..

What is the "Prev Qty" field supposed to be?

thetallbloke

Hi,

The Quantity field is a calculated field, so I was going to store the quantity value that was calculated at a point in time in that field, then next time, compare the calculated version against the physical value in "Prev Qty", and therefore determine which values have changed..
A very clunky, slow way to do it...
It was all too messy... and I decided to scrap that idea...