How to filter the records?

NAVPecker

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.

kriki

Can you explain a little better. It is not clear what you exactly need.

NAVPecker

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

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

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;