Filter on Form on the basis of more than one field

varunkg

I have three different fields in Sales Header Table
1. Location 1
2. Location 2
3. Location 3
I had attched Location code on each user on user setup table.
My client want that when any user open sales order/sales invoice form he can view only those orders or invoices in which
(Location = UserSetup.Location) OR (Location 1 = UserSetup.Location)
OR (Location 2 = UserSetup.Location) OR (Location 3 = UserSetup.Location)
How it will possible please let me know if any body knows



Thanks in advance.

matttrax

There are many examples / posts about this on the forums, although it can be hard to get the right search terms for it.

In your case you will have to loop through all of your records and mark the ones that match your filtering criteria. Then set a MARKEDONLY filter so that you only see those records. The other options is almost the same, but instead of marking them you insert them into a temporary table and use that temp table to display the data.

[Edit] A better solution would be a table that lists the allowed locations by user id instead of having Location 1, Location 2, and Location 3. Then you could just set a filter on one field based on the data found in that table.

KYDutchie

Hi,

sorry, my suggestion doesn't work, use the suggestions that the others gave you.
It was too early and haven't started on my second pot of coffee yet... 8)

Regards,

Willy

mohana_cse06

http://www.mibuso.com/forum/viewtopic.php?f=23&t=46238&hilit=transfer+from+or+to

varunkg

How can i put Filtergroup on Markedonly data.

vaprog

I don't think FILTERGROUP will affect MARKs in any way.

If you need to preserve MARKs, you might try another approach. Use the form's OnFindRecord and OnNextRecord triggers to filter your record set like this:

OBJECT Form 50003 Cust Filtered
{
  OBJECT-PROPERTIES
  {
    Date=24.04.11;
    Time=23:20:37;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=10340;
    Height=8800;
    TableBoxID=1000000010;
    SourceTable=Table18;
    OnFindRecord=VAR
                   i@1000000000 : Integer;
                 BEGIN
                   FOR i := 1 TO STRLEN(Which) DO BEGIN
                     CASE Which[i] OF
                       '+' :
                         IF FIND(FORMAT(Which[i])) THEN REPEAT
                           IF FilterOk THEN
                             EXIT(TRUE);
                         UNTIL NEXT(-1) = 0;
                       '-' :
                         IF FIND(FORMAT(Which[i])) THEN REPEAT
                           IF FilterOk THEN
                             EXIT(TRUE);
                         UNTIL NEXT(1) = 0;
                       '=' :
                         IF FIND(FORMAT(Which[i])) THEN
                           IF FilterOk THEN
                             EXIT(TRUE);
                       '>','<' :
                         WHILE FIND(FORMAT(Which[i])) DO
                           IF FilterOk THEN
                             EXIT(TRUE);
                     ELSE
                       EXIT(FALSE);
                     END;
                   END;
                   EXIT(FALSE);
                 END;

    OnNextRecord=VAR
                   Dir@1000000000 : Integer;
                   i@1000000001 : Integer;
                 BEGIN
                   IF Steps = 0 THEN
                     EXIT(0)
                   ELSE IF Steps > 0 THEN
                     Dir := 1
                   ELSE
                     Dir := -1;
                   i := 0;
                   REPEAT
                     IF NEXT(Dir) = Dir THEN BEGIN
                       IF FilterOk THEN
                         i += Dir;
                     END ELSE
                       EXIT(i);
                   UNTIL i = Steps;
                   EXIT(Steps);
                 END;

  }
  CONTROLS
  {
    { 1000000000;TabControl;220;220 ;9900 ;7590 ;HorzGlue=Both;
                                                 VertGlue=Both;
                                                 PageNamesML=DEU=Allgemein }
    { 1000000001;TextBox;3850 ;990  ;2750 ;440  ;ParentControl=1000000000;
                                                 InPage=0;
                                                 SourceExpr="No." }
    { 1000000002;Label  ;440  ;990  ;3300 ;440  ;ParentControl=1000000001 }
    { 1000000003;TextBox;3850 ;1540 ;5500 ;440  ;ParentControl=1000000000;
                                                 InPage=0;
                                                 SourceExpr=Name }
    { 1000000004;Label  ;440  ;1540 ;3300 ;440  ;ParentControl=1000000003 }
    { 1000000005;TextBox;3850 ;2090 ;2750 ;440  ;ParentControl=1000000000;
                                                 InPage=0;
                                                 SourceExpr="Currency Code" }
    { 1000000006;Label  ;440  ;2090 ;3300 ;440  ;ParentControl=1000000005 }
    { 1000000007;TextBox;3850 ;2640 ;2750 ;440  ;ParentControl=1000000000;
                                                 InPage=0;
                                                 SourceExpr="Language Code" }
    { 1000000008;Label  ;440  ;2640 ;3300 ;440  ;ParentControl=1000000007 }
    { 1000000010;TableBox;440 ;3300 ;9350 ;4290 ;HorzGlue=Both;
                                                 VertGlue=Both;
                                                 ParentControl=1000000000;
                                                 InPage=0 }
    { 1000000011;TextBox;2296 ;4730 ;1700 ;440  ;ParentControl=1000000010;
                                                 InColumn=Yes;
                                                 SourceExpr="No." }
    { 1000000012;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000011;
                                                 InColumnHeading=Yes }
    { 1000000013;TextBox;3996 ;4730 ;4400 ;440  ;HorzGlue=Both;
                                                 ParentControl=1000000010;
                                                 InColumn=Yes;
                                                 SourceExpr=Name }
    { 1000000014;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000013;
                                                 InColumnHeading=Yes }
    { 1000000015;TextBox;8396 ;4730 ;1700 ;440  ;ParentControl=1000000010;
                                                 InColumn=Yes;
                                                 SourceExpr="Currency Code" }
    { 1000000016;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000015;
                                                 InColumnHeading=Yes }
    { 1000000017;TextBox;10096;4730 ;1700 ;440  ;ParentControl=1000000010;
                                                 InColumn=Yes;
                                                 SourceExpr="Language Code" }
    { 1000000018;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000017;
                                                 InColumnHeading=Yes }
    { 1000000009;CommandButton;7920;8030;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 PushAction=FormHelp }
  }
  CODE
  {

    PROCEDURE FilterOk@1000000002() : Boolean;
    BEGIN
      EXIT(("Currency Code" = '') OR ("Language Code" = ''));
    END;

    BEGIN
    END.
  }
}

Note: this was only a quick exercise. It is not tested extensively. In fact, you probably needed to do the search with a local record in order to return the correct data in Rec at all times.

Replace the code of the FilterOk function to match your need (and the SourceTable property of course).

The code works with a card as well as with a list.

EDIT: There was an error in the code which led to an endless loop. Replaced FIND(Which) by FIND(FORMAT(Which)) in all sections of the CASE statement.

Andwian

Another workaround:

If 1 user only assigned 1 Location Code, then you could create a form that filtered for 1 Location Code only, and assigned that to the user.

Sales Header.

SourceTableView: WHERE(Document Type=FILTER(Order),Location Code=CONST(BLUE))

alok_kul

belowe is the basic code for your requirement but it is not considering 3 location on sales heaer.
bcoz i am confuess with 3 location concept.

usersetup.RESET;
usersetup.SETRANGE(usersetup."User ID",USERID);
IF usersetup.FINDFIRST THEN
REPEAT
SalesHeader.RESET;
SalesHeader.SETRANGE(SalesHeader."Location Code",usersetup.Location);
IF SalesHeader.FINDFIRST THEN
REPEAT
SalesHeader.MARK(TRUE)
UNTIL SalesHeader.NEXT=0;
UNTIL usersetup.NEXT=0;
SalesHeader.MARKEDONLY(TRUE);
COPYFILTERS(SalesHeader);