filter general ledger entries

ggdmp
ggdmp Member Posts: 5
edited 2005-08-31 in NAV/Navision Classic Client
Hi.
I have added two text fields at the bottom of the form General Ledger Entries that display the Debit Amount and the Credit Amount.

I made a function and added the following code that is called from OnAfterGetCurrRecord:

GLEntry.COPY(Rec);
GLEntry.CALCSUMS("Credit Amount","Debit Amount");
Credit := GLEntry."Credit Amount";
Debit := GLEntry."Debit Amount";

It works well when I simply run the form .. however when I try to do a field filter (for example if I try with Posting Date) I get the following message:

The sum of the values in the Credit Amount Field cannot be calculated because the current key does not contain all the fields being filtered.

you must select a field that contains all the fields in the filter. The order of the fields is unimportant.

Filters: Posting Date: 04/08/05
Table: G/L Entry
key Fields: Entry No.

Is there something I'm missing? I do have a secondary key in the G/L Entry table... Posting Date with SumIndexFields Credit Amount and Debit Amount.

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Are you refering to

    http://www.mibuso.com/dl.asp?FileID=508&Type=file

    ???

    If you do a calcsums, all the current filters should be in the current key.

    You can avoid a runtime error by using
    IF GLEntry.CALCSUMS("Credit Amount","Debit Amount") then

    You can also create a repeat until if the calcsums fails
    IF NOT GLEntry.CALCSUMS("Credit Amount","Debit Amount") then BEGIN
  if glentry.find('-') then 
   repeat
      credit += glentry."credit amount";
      etc...
   until glentry.next = 0;
    Marije Brummel | Freelance Business Central Specialist
  • ggdmp
    ggdmp Member Posts: 5
    I tried this... and it does not sum up correctly. Also, if I run the form, the Credit Amount and Debit Amount are both 0. I have to filter something for it to show something.
    And what it adds up is wrong.

    My code now is

    GLEntry.COPY(Rec);

    IF NOT GLEntry.CALCSUMS("Credit Amount","Debit Amount") THEN BEGIN
    IF GLEntry.FIND('-') THEN
    REPEAT
    Credit += GLEntry."Credit Amount";
    Debit += GLEntry."Debit Amount";
    UNTIL GLEntry.NEXT = 0;
    END;
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Do you reset the Debit/Credit ?
    Debit := 0;
Credit := 0;

GLEntry.COPY(Rec); 

IF NOT GLEntry.CALCSUMS("Credit Amount","Debit Amount") THEN BEGIN 
IF GLEntry.FIND('-') THEN 
REPEAT 
Credit += GLEntry."Credit Amount"; 
Debit += GLEntry."Debit Amount"; 
UNTIL GLEntry.NEXT = 0; 
END;
    Marije Brummel | Freelance Business Central Specialist
  • ggdmp
    ggdmp Member Posts: 5
    ok.. this works!!

    However... if there is no filter (when I first open up the form)... the values of Credit Amount and Debit Amount are 0.

    How do I make it show the value?
  • ggdmp
    ggdmp Member Posts: 5
    oh, ok...

    I simply added...


    END ELSE
    IF GLEntry.CALCSUMS("Credit Amount","Debit Amount") THEN BEGIN
    Credit := GLEntry."Credit Amount";
    Debit := GLEntry."Debit Amount";
    END;


    works great!
    Thanks!

Categories