how to filter and mass update a set of records

rmv_RU

Our department often gets requests from users:
"Please, update this list of items with SOMECATEGORY"
"Please, re-assign "Customer Posting Group" to SomeGroup, see attached file".. etc
Usually, we solve this kind of requests by writing dataports or using Excel Import with mapping functionality, but these tasks takes more and more time.
Now we have released two tasks, thats can help end users to manipulate with lot of records

The first task is - How we can set filters from Excel List?
The idea is simple - choose a magic word (FromExcel for example), check filters for magic word on a Form - OnActivateForm() trigger. If checkOK - paste clipboard content into a new excel sheet, read it and then apply composite filter or mark records.

CheckRecRefForExcelFilters(VAR RecRef : RecordRef;VAR FRef : FieldRef;lNeedConfirm : Boolean) ExcelFiltersExists : Boolean
--Common function. Checking filters for magic word, returns fieldref
index:=0;
IF NOT RecRef.HASFILTER THEN EXIT(FALSE); // 
FOR j:=1 TO RecRef.FIELDCOUNT DO BEGIN
  FRef:=RecRef.FIELDINDEX(j);
  IF (UPPERCASE(FORMAT(FRef.GETFILTER))='FROMEXCEL') OR
     (FORMAT(FRef.GETFILTER)='-999')
  THEN
     index:=j;
END;
IF index=0 THEN EXIT(FALSE); // 
FRef:=RecRef.FIELDINDEX(index);
if lNeedConfirm then 
  IF NOT CONFIRM(Text013,TRUE) THEN EXIT(false);
EXIT(TRUE);


Excel2Buffer(VAR buffer : Record Buffer): Text[400]
--Common function. Copyng clipboard to temporary buffer
CLEAR(objExcel);
CREATE(objExcel);
objBook := objExcel.Workbooks.Add;
objSheet := objBook.ActiveSheet;

celSLC:='$A$1';

objSheet.Range(celSLC+':'+celSLC).Select;       
objSheet.Paste; 
REPEAT
    txt:= objSheet.Range(celSLC).Text;
    if txt<>'' then begin
      buffer.init;
      buffer.Id+=1;
      buffer.text:=txt;
      lValueExists:=NOT buffer.INSERT;
      IF NOT lSimpleFilterOverflow AND (STRLEN(txt+lSimpleFilter)<MAXSTRLEN(lSimpleFilter)) THEN BEGIN
        IF NOT lValueExists THEN BEGIN
          IF (lSimpleFilter<>'') THEN
            lSimpleFilter+='|';
          lSimpleFilter+=txt;
        END;
      END
      ELSE BEGIN
        lSimpleFilterOverflow:=TRUE;
      END;
    end;
UNTIL txt='';
ExcelCleanup(objExcel, objBook);
IF NOT lSimpleFilterOverflow THEN
  EXIT(lSimpleFilter)
ELSE
  EXIT('');



ItemSetFieldFilterFromExcel()
--Calling from Form.Activate trigger
recRef.GETTABLE(Rec);
IF NOT st.CheckRecRefForExcelFilters(recRef, fRef, TRUE) THEN
  EXIT;
lSimpleFilter:=st.Excel2Buffer(buffer);
recRef.SETVIEW(Rec.GETVIEW(FALSE));
IF lSimpleFilter<>'' THEN BEGIN
  fRef.SETFILTER(lSimpleFilter);
  Rec.SETVIEW(recRef.GETVIEW);
END
ELSE BEGIN
  IF NOT buffer.FIND('-') THEN EXIT;
  REPEAT
    fRef.SETFILTER(buffer.Txt);
    Rec.SETVIEW(recRef.GETVIEW);
    IF Rec.FIND('-') THEN
       REPEAT
         Rec.MARK(TRUE);
       UNTIL Rec.NEXT=0;
  UNTIL buffer.NEXT=0;
  fRef.SETRANGE;
  Rec.SETVIEW(recRef.GETVIEW);
  Rec.MARKEDONLY(TRUE);
END;

The second task is - How we can update any field in all filtered records?
The idea is simple too - use filters in a report object as field value.
Create a new report with a Item table as a first dataitem. Add frequently used fields into a ReqFilterFields property. Add following code

Item - OnPreDataItem()
recRef.GETTABLE(Item);
IF NOT recRef.HASFILTER THEN
  ERROR('Choose Value');
FOR i:=1 TO recRef.FIELDCOUNT DO BEGIN
  fRef:=recRef.FIELDINDEX(i);
  IF FORMAT(fRef.GETFILTER)<>'' THEN BEGIN
    lValue:=fRef.GETRANGEMIN;
    index:=i;
  END;
END;
fRef:=recRef.FIELDINDEX(index);

recRef.GETTABLE(SourceItem);
IF NOT CONFIRM(STRSUBSTNO('Replace field %1 value with %2?\Table filter is %3.', fRef.CAPTION, FORMAT(lValue), recRef.GETFILTERS))
  THEN
  EXIT;
IF recRef.FIND('-') THEN REPEAT
  xRecRef:=recRef.DUPLICATE;
  fRef:=recRef.FIELDINDEX(index);
  fRef.VALIDATE(lValue);
  recRef.MODIFY(TRUE);
  ChangeLogMgt.LogModification(recRef,xRecRef);
UNTIL recRef.NEXT=0;




SetSource(VAR lItem : Record Item)
--copyng filters from original record
SourceItem.COPYFILTERS(lItem);

rmv_RU

Oops. Please move topic to viewforum.php?f=5

kriki

[Topic moved from 'SQL Tips & Tricks' forum to 'NAV Tips & Tricks' forum]

morvan

Great!
How can I use filter for numeric fields?

Kishorm

It looks like it already works for numeric fields too, just filter on -999