How to filter the records?

NAVPecker
NAVPecker Member Posts: 3
edited 2012-08-06 in NAV/Navision Classic Client
Hi NAV guys,

I need to filter a set of values from 2 tables. How to use SETRANGE or SETFILTER in below mentioned scenario?

For eg.:

"Customer Posting Group" (table), "Receivables Account" (field) contains 4 records: 12101; 12102, 12103; 12104.
"G/L Entry" (table), "G/L Account No." (field) contains many records. Say totally 20 records. And it contains 
3 records in 12101; 
3 records in 12102; 
4 records in 12103; 
0 record in 12104.

How can I filter the above mentioned 10 records alone? I am doing this in dataport. Can anyone help me out in this regard?

Regards,
NAV Pecker.

Comments

  • kriki
    kriki Member, Moderator Posts: 9,124
    Can you explain a little better. It is not clear what you exactly need.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • NAVPecker
    NAVPecker Member Posts: 3
    Thanks kriki for you reply.

    Explaining you in little more details. There are two tables.

    1. "Customer Posting Group" - This table has a field named "Receivables Account" and this table contains 4 records.
    A. 12101
B. 12102
C. 12103
D. 12104

    2. "G/L Entry" - This table has a field named "G/L Account No." and this table contains 10 records.
    a. 12101
b. 12101
c. 12101
d. 12101
e. 12102 
f. 12102
g. 12102
h. 12103
i. 12103
j. 12104

    I need to add the Amount field in G/L Entry table by grouping/filtering 12101 seperately (4 records), 12102 (3 records seperately), 12103 (2 records) seperately and 12104 (1 record seperately) and store in a variable varAmount. Also, I should leave the other records which cannot be filtered. This I am doing in dataport and export it. How to proceed with this? Hope I am clear.

    Regards,
    NAVPecker.
  • Purvesh_Maisuria
    Purvesh_Maisuria Member Posts: 71
    Hello NAVPecker,

    Code :

    Customer_Posting_Group.RESET;
    Customer_Posting_Group.SETFILTER(Customer_Posting_Group."Receivables Account",'12101|12102|12103|12104');
    IF Customer_Posting_Group.FINDFIRST THEN
    REPEAT
    GL_Entry.RESET;
    GL_Entry.SETRANGE(GL_Entry."G/L Account No.",Customer_Posting_Group."Receivables Account");
    IF GL_Entry.FINDFIRST THEN
    REPEAT
    // 1st Time for 12101-4 Times , 2nd Time for 12102-3 Times, 3rd Time for 12103-2 Times, & So On...
    UNTIL GL_Entry.NEXT=0;
    UNTIL Customer_Posting_Group.NEXT=0;

    Note : In Table Customer_Posting_Group "Receivables Account" if not a primary key field. "Receivables Account" Value 12101, may be repeat. If "Receivables Account" 12101 2 times in Table Customer_Posting_Group then upper loop is repeat 2 times for 12101 & inner loop is repeat 1st 12101 - 4 Times, 2nd 12101 - 4 Times.

    Hope this helps you.

    Thanks & Regards,
    Purvesh Maisuria.
  • kriki
    kriki Member, Moderator Posts: 9,124
    Improving on Purvesh Maisuria's code:
    Customer_Posting_Group.RESET;
Customer_Posting_Group.SETFILTER("Receivables Account",'12101|12102|12103|12104');
IF Customer_Posting_Group.FINDSET THEN // if you loop the record, NEVER use FINDFIRST. Use FINDSET or FIND('-') or FIND('+')
  REPEAT
    GL_Entry.RESET;
    GL_Entry.SETRANGE("G/L Account No.",Customer_Posting_Group."Receivables Account");
    GL_Entry.CALCSUMS(Amount); // CALCSUMS is faster than looping the records

    MESSAGE('Account "%1" = %2"',Customer_Posting_Group."Receivables Account",GL_Entry.(Amount);
UNTIL Customer_Posting_Group.NEXT=0;
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories