Filter on a journal form

brimfred

Hi,

My customer wants to be able to put a filter on journal lines in a form with a value which is stored in a sales document (or purchase document), the Document No. field in the journal line refers to the document.

My problem is that I'm not allowed to create a new field in the journal table which could contain this value from the document.

Does anybody know a smart way to deal with this? I'm allowed to do any changes I want to the journal form.

Thanks!

FlowerBiz

You could add a field to the form outside of the table box. Then, using the OnAfterValidate trigger of this new field, refresh the table data based on the "filter" that the user provided in this field. You would have to manually loop through each journal record and determine if the referenced sale (or purchase) record matched the user-entered "filter". It would not be efficient by any means for large journal datasets. I would only do this if my journal data was small to begin with (a few hundred rows or less) and the "filter" was used sparingly.

The code would look something like this (untested):

MARKEDONLY(FALSE);
IF UserEnteredFilter <> '' THEN BEGIN
  SalesRec.RESET;
  SalesRec.SETCURRENTKEY("No.");
  SalesRec.SETFILTER("Field To Filter",'%1',UserEnteredFilter);
  IF FINDSET THEN
    REPEAT
      SalesRec.SETRANGE("No.","Document No.");
      MARK(NOT SalesRec.ISEMPTY);
    UNTIL NEXT = 0;
  MARKEDONLY(TRUE);
END;
CurrForm.UPDATE(FALSE);

Of course, you will have the mark symbols showing up on the form. This is but one solution. Depending on what the form is used for, you could use a temporary table instead. Then you could use an unused field to hold the data from other tables on which you want to filter.