Comparing records

JBhav

Hi,
I am using a report to display some bank transactions in a text file. The problem is that i have to compare the bank account number. If they are same then i have to sum up the amount of the transactions and then output it on the text file.

I have put the codes in the OnAfterGetRecord trigger but it is not working. ](*,)

Can anyone help me please as it's urgent.

Thanks in advance,
Bhavna

mohana_cse06

Can you show us the code?

JBhav

Here is the code:

BankAccountNo := "Interface table"."Outlet A/c branch code" + "Interface table"."Outlet bank account no";

IF BankAccountNo = PreviousBankAccNo THEN
totalamount += "Interface table"."Invoice Open Amount";

PreviousBankAccNo := "Interface table"."Outlet A/c branch code" + "Interface table"."Outlet bank account no";

Regards,
Bhavna

rhpnt

Did you set the correct key on the "Interface table" first?

e.g.: SETCURRENTKEY("Outlet A/c branch code","Outlet bank account no",...)

JBhav

Hi,

The keys were missing. But when i added them, instead of suming up the amounts of the records having the same bank account number, it is giving me a grand total.

Regards,
Bhavna

Belias

I think that one problem is that the first amount is never summed up because in the first loop you'll have Bankaccountno = "something" and previousbankaccountno = blank.
moreover, where do you reset the totalamount?
Btw, do you have rdlc or classic layout? (but i think "display in a text file" means export data in a text file, thus a processingonly report(?)) And Can't you use grouping for this purpose?

JBhav

Hello Belias,

I initialised the total in the OnPreDataItem trigger. You are right, the PreviousBankAccNo is blank at first.
I am using the classic version. As for the text file, i created a function where all the transactions will be written to the text file after all the calculations.

Maybe i am pointing to the wrong trigger. :?

The client's requirements are as follows:-
1) Import all transactions in a table which i named Interface Table.
2) Create a report to output all the transactions in a text file. There are 3 banks. I need to get each one first.
Each customer has an outlet number and a bank account.

What i need is to get the specific bank which works fine and then check the account number. For all transactions having the same bank account number, i need to sum the amount so that i can display it on the same line. The grouping works fine while processing the report but i am not being able to display it in the text file.

Regards,
Bhavna

kriki

[Topic moved from 'NAV Three Tier' forum to 'NAV/Navision Classic Client' forum]

Belias

i like temporary tables for this kind of things (well, i like temporary tables for everything, but it doesn't matter ).
here's how would i do it. Do you have a table that has "Outlet A/c branch code,Outlet bank account no" as primary key? (i don't think so).
If not, create one, and add one more field called "amount". This table can be out of the range of your licensed tables, because it will be used only as temporary.
When you read records for the amounts, write something like this:

CLEAR(mytemptable);
mytemptable.Outlet A/c branch code := outlbranchcode;
mytemptable.Outlet bank account no := outlbankacc;
mytemptable.amount += myamount;
if not mytemptable.insert then
  mytemptable.modify;

at the end of your interface table dataitem, loop the temptable and get the summed values, grouped with the PK fields of the temporary table.

Belias

sorry, wrong code suggestion (i was a in hurry for the lunch time :whistle: )

CLEAR(mytemptable);
if mytemptable.get(outlbranchcode,outlbankacc) then
  mytemptable.amount += myamount
  mytemptable.modify;
else begin
  mytemptable.Outlet A/c branch code := outlbranchcode;
  mytemptable.Outlet bank account no := outlbankacc;
  mytemptable.amount := myamount;
  mytemptable.insert;
end;

JBhav

Belias wrote:

sorry, wrong code suggestion (i was a in hurry for the lunch time :whistle: )

CLEAR(mytemptable);
if mytemptable.get(outlbranchcode,outlbankacc) then
  mytemptable.amount += myamount
  mytemptable.modify;
else begin
  mytemptable.Outlet A/c branch code := outlbranchcode;
  mytemptable.Outlet bank account no := outlbankacc;
  mytemptable.amount := myamount;
  mytemptable.insert;
end;

Hello,

I have tried with the temporary table but still no results
I finally created a flowfield named Total Amount in the interface table itself and it sums up all the transactions having the same bank account number.
The problem that i am facing now is that when i am trying to display it, it's always zero.

Regards,
Bhavna

Belias

I double checked my code and it's correct :-k
Well, what did you do? in what triggers? did you get the meaning of my code or you blindly copy/pasted it?
BTW, that flowfield is weird: you'll get a lot of useless duplicated values, moreover you slow down the writing of that table (and i suppose it have to be a fast-writing one). But if you feel confident with the flowfield solution only...well, check if you use calcfields instruction before writing the total. If you already put the calcfields in the code, in what trigger did you put it?

JBhav

Belias wrote:

I double checked my code and it's correct :-k
Well, what did you do? in what triggers? did you get the meaning of my code or you blindly copy/pasted it?
BTW, that flowfield is weird: you'll get a lot of useless duplicated values, moreover you slow down the writing of that table (and i suppose it have to be a fast-writing one). But if you feel confident with the flowfield solution only...well, check if you use calcfields instruction before writing the total. If you already put the calcfields in the code, in what trigger did you put it?

Hi,

I tried it again. I have put the codes in the OnAfterGetRecord trigger for the Interface Table.
the codes are as follows:


CLEAR("Interface Table temp");

IF "Interface Table temp".GET("Interface table"."Outlet A/c branch code","Interface table"."Outlet bank account no") THEN
BEGIN
"Interface Table temp".Amount += "Interface table"."Invoice Open Amount";
"Interface Table temp".MODIFY;
END
ELSE
BEGIN
"Interface Table temp"."Outlet no" := "Interface table"."Outlet no";
"Interface Table temp"."Outlet A/c branch code" := "Interface table"."Outlet A/c branch code";
"Interface Table temp"."Outlet bank account no" := "Interface table"."Outlet bank account no";
"Interface Table temp".Amount := "Interface table"."Invoice Open Amount";
"Interface Table temp".INSERT;
END;

Am i missing something?

Regards,
Bhavna

Belias

what is the error or the thing that does not work?
"Interface Table temp" <-- what's its PK?
"Interface Table temp"."Outlet no" := "Interface table"."Outlet no"; <-- useless (?)
and what do you do with this temporary table? I hope you don't expect that it magically writes the textfile :shock:
What code is next?

JBhav

The primary keys are : - Outlet A/c branch code,Outlet bank account no

the codes that follows are:-


IF "Outlet bank code" = 'MCB' THEN
BEGIN

MCBTransfer
END;

IF "Outlet bank code" = 'SBM' THEN
BEGIN
SBMTransfer
END;

IF ("Outlet bank code" = 'BBPLC')

THEN
BEGIN
BARTransfer
END;

The MCBTransfer, SBMTransfer and the BARTransfer are the functions that write to the text file. The temporary table is always blank. If i am not mistaken i think it should contain the transactions of a particular bank. :-k

In addition, while running the report i get an error on the: "Interface Table temp".INSERT.

It says that the Interface table temp already exists.


Regards,
Bhavna

Belias

](*,) ](*,)
how can i know what does these funcitons do? :?
SBMTransfer
BARTransfer....

you should ask your senior developer to review your code with you...i gave you an idea, but i cannot remotely check your code in a forum, sorry...

JBhav

Hello,

The problem has been solved. All i had to do was to put the bank transfer functions which write to the text files in the group footer section.

Thanks for your suggestions.

Regards,
Bhavna

Belias

so you DO have a layout...you should have said it before, we would have solve your problem far more faster (that section trigger helps in these kind of group processing)
nevermind, you solved your problem...

JBhav

Hi,

Sorry it just skipped my mind. :oops: But thanks still

Regards,
Bhavna