how to filter and mass update a set of records

rmv_RUrmv_RU Member Posts: 119
edited 2014-06-10 in NAV Tips & Tricks
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);
Looking for part-time work.
Nav, T-SQL.

Comments

  • rmv_RUrmv_RU Member Posts: 119
    Oops. Please move topic to viewforum.php?f=5
    Looking for part-time work.
    Nav, T-SQL.
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'SQL Tips & Tricks' forum to 'NAV Tips & Tricks' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • morvanmorvan Member Posts: 1
    Great!
    How can I use filter for numeric fields?
    NAV consultant, developer
  • KishormKishorm Member Posts: 921
    It looks like it already works for numeric fields too, just filter on -999
Sign In or Register to comment.