Report Help

rj5570

I am trying to add Line Description from the Purch. Inv. Line Table to the Trail Balance Detail/Summary report.

first i thought i would set up a variable in the C/AL global as a record and just reffrence it in the detail section like this MyVar.Description. i set MyVar as record and pointed it to the Purch. Inv. Line table, but didnt get any data for the description.

SOOOOOO i thought i could just add the Purch. Inv. Line table to the report, link it by Document No. to the G/L Entry Table, and reference it that way. still no data.

am i missing something?

](*,)

gvolkov

Try

IF InvLine.GET(parameter) THEN
yourVariable := InvLine."Field You Need";

rj5570

thanks, i will try that

rj5570

ok, from what you posted i did this:


IF "Purch. Inv. Line".GET(Description) THEN
InvDesc :="Purch. Inv. Line".Description;

i think i am closer!!!! but i am getting an error when i run the report, this is the error:

"Overflow under type conversion of Text to Code."

then it gives the value of the actual data i was trying to get.

the fact that the value that it returned in the error is the data i want, i know i am close, but what does it mean by overflow under type conversion.

MyVar is a Text variable, the field that i am reffrencing above "Description" is a text field. so why does it think i am using a code and text field, when all are text fields..the variable and the actual record field... also i have my vairable (InvDesc) text set to 50, as the description field is set to 50

](*,) not as much from my first post LOL

thanks!!!!!!!!!!!

rj5570

ok...more info

i added the line:

InvDesc := '';

i get the same error BUT

if change the space to 51 spaces between the ' ', i get

Overflow under type conversion of Text to Text.

with NO value in the error.

i have not changed ANY field lenghts, i have my text variable (InvDesc) set to 50, the Description field in Purch. Inv. Line is 50 characters as well

cornfused!!!!!!!

David_Cox

rj5570 wrote:

I am trying to add Line Description from the Purch. Inv. Line Table to the Trail Balance Detail/Summary report.

first i thought i would set up a variable in the C/AL global as a record and just reffrence it in the detail section like this MyVar.Description. i set MyVar as record and pointed it to the Purch. Inv. Line table, but didnt get any data for the description.

SOOOOOO i thought i could just add the Purch. Inv. Line table to the report, link it by Document No. to the G/L Entry Table, and reference it that way. still no data.

am i missing something?

](*,)

The primary Key on the Invoice line is "Document No." and "Line No." you need both to use the GET statement, PurchInvLine.GET('PI-10000',10000);

So you cannot use the get unless you are sure the first line is always 10000 and has the description you want, so you have to use filters to filter down to the line you want.

Firstly you can only show the line detail on the detailed view.
If you are using a variables try this untested code.
Variables:
LastDocNo Code 20
LineDesc Text 50
PurchInvLine Record Purchase Invoice Line
PurchCRMemoLine Record Purchase CR. Memo Line

G/L Entry

OnAfterGetRecord()
IF "Document No." <> LastDocNo THEN BEGIN
   Case "Document Type" of
      "Document Type"::Invoice:
           BEGIN
                PurchInvLine.RESET;
                PurchInvLine.SETRANGE("Document No.","Document No.");
                PurchInvLine.SETRANGE("No.","Account No.");
                IF NOT PurchInvLine.FINDFIRST THEN
                    PurchInvLine.INIT;
                LineDesc := PurchInvLine.Description; 
           END;  
     "Document Type"::"Credit Memo":
           BEGIN
                PurchCrMemoLine.RESET;
                PurchCrMemoLine.SETRANGE("Document No.","Document No.");
                PurchCrMemoLine.SETRANGE("No.","Account No.");
                IF NOT PurchCrMemoLine.FINDFIRST THEN
                    PurchCrMemoLine.INIT; 
                LindDesc := PurchCrMemoLine.Description;           
           END;  
      ELSE
           CLEAR(LineDesc);
   END;
   LastDocNo := "Document No.";
END;

On the Detail Section add a Textbox with source = LineDesc;

If there is no purchase document with the filters then the code clears the purchase description, by using the INIT, then assigning this to LineDesc.

If you just want to find the first line of the Purchase Document regardless of Account No.
Delete:
PurchInvLine.SETRANGE("No.","Account No.");
IF NOT PurchInvLine.FINDFIRST THEN
PurchInvLine.INIT;

Add:
PurchInvLine.SETRANGE(Type,PurchInvLine.Type::"G/L Account");
PurchInvLine.SETFILTER("No.",'<>''''');
IF NOT PurchInvLine.FINDFIRST THEN
PurchInvLine.INIT;

This will find the first line type G/L Account

rj5570

ok, i think that makes sense....

i am going to try that.....one question thou...should the code go on the "on after get record" section of the Purch. Inv. Line code section?

David_Cox

rj5570 wrote:

ok, i think that makes sense....

i am going to try that.....one question thou...should the code go on the "on after get record" section of the Purch. Inv. Line code section?

No Remove the Purch. Inv. Line from the report.

This code is on the reports G/L Entry (Line Detailed Data Item) On after get record, and the LineDesc is in the section for the DataItem.

David

rj5570

ok thats what i was getting ready to ask.... on the same page now

edited this cause i didnt add what you said and didnt remove the find first line

rj5570

ok when i removed:

PurchInvLine.SETRANGE("No.","Account No.");
IF NOT PurchInvLine.FINDFIRST THEN
PurchInvLine.INIT;

and inserted:

PurchInvLine.SETRANGE(Type,PurchInvLine.Type::"G/L Account");
PurchInvLine.SETFILTER("No.",'<>''''');
IF NOT PurchInvLine.FINDFIRST THEN
PurchInvLine.INIT;


i am getting an unknown variable on the FINDFIRST

here is the code i inserted:

IF "Document No." <> LastDocNo THEN BEGIN
Case "Document Type" of
"Document Type"::Invoice:
BEGIN
PurchInvLine.RESET;
PurchInvLine.SETRANGE("Document No.","Document No.");
PurchInvLine.SETRANGE(Type,PurchInvLine.Type::"G/L Account");
PurchInvLine.SETFILTER("No.",'<>''''');
IF NOT PurchInvLine.FINDFIRST THEN
PurchInvLine.INIT;
LineDesc := PurchInvLine.Description;
END;
"Document Type"::"Credit Memo":
BEGIN
PurchCrMemoLine.RESET;
PurchCrMemoLine.SETRANGE("Document No.","Document No.");
PurchCrMemoLine.SETRANGE("No.","Account No.");
IF NOT PurchCrMemoLine.FINDFIRST THEN
PurchCrMemoLine.INIT;
LindDesc := PurchCrMemoLine.Description;
END;
ELSE
CLEAR(LineDesc);
END;
END;
LastDocNo := "Document No.";
END;

rj5570

david,

ok i just read that the findfirst was not avialable for versions under 4.0, what can i replace that with since i am on 3.6?

David_Cox

rj5570 wrote:

david,

ok i just read that the findfirst was not avialable for versions under 4.0, what can i replace that with since i am on 3.6?

//Find First
PurchInvLine.FIND('-');

//Find Last
PurchInvLine.FIND('+');

One to Many Ends
ELSE
CLEAR(LineDesc);
END;
LastDocNo := "Document No.";
END;

8-[

rj5570

THANK YOU THANK YOU!!!

OK that worked for the PurchInvLine, i have to fix the credit memo line cause it didnt like the Account No. its not in the table of PurchCrMemoLine and if i replaced it with the

SETRANGE(Type,PurchCrMemoLineType::"G/L Account");

it didnt likt it, so i just deleted the line so i can test to see if i can get the invoice information.

all i need to do is figure out the credit memo line change

thanks for your help and also teaching me something

sorry i didnt know about the code for findfirst LOL still learning

rj

David_Cox

rj5570 wrote:

THANK YOU THANK YOU!!!

OK that worked for the PurchInvLine, i have to fix the credit memo line cause it didnt like the Account No. its not in the table of PurchCrMemoLine and if i replaced it with the

SETRANGE(Type,PurchCrMemoLineType::"G/L Account");

it didnt likt it, so i just deleted the line so i can test to see if i can get the invoice information.

all i need to do is figure out the credit memo line change

thanks for your help and also teaching me something

sorry i didnt know about the code for findfirst LOL still learning

rj

Thanks for the feedback =D>

Hint: Use the same code and change PurchInvLine PurchCrMemoLine

As I said it was untested code, written from memory.
It should have read "G/L Account No.", like this:
PurchCrMemoLine.SETRANGE("No.","G/L Account No.");

What this is doing if filtering on the "Purc. Cr. Memo Line" and trying to Match the "No." field to the "G/L Account No." in the "G/L Entry"

When you are learning CAL code it can be confusing, as the Header "No." field is the "Document No.", and the "No." field on the Lines is the G/L Account No.,Item No.,Resource No. etc:

One day MS might normalise the field names to stop this, making the Header "No." Field "Document No." and in the Line "No." to "Source No." or "Lookup No." maybe!

rj5570

right on, and THANKS AGAIN!!!!

do have one more quick question, how can you import an exsisting report into the 50,000 report number range,

saying if i modify a report and i want to have both, the original and the modified report. I know i could go thru and make a new report, copy the code and all the fields etc...but that seems sooooooooooooooooo idiotic, when one should be able to rename a report and the number or import into a different number and name.

Savatage

rj5570 wrote:

saying if i modify a report and i want to have both, the original and the modified report. I know i could go thru and make a new report, copy the code and all the fields etc...but that seems sooooooooooooooooo idiotic, when one should be able to rename a report and the number or import into a different number and name.

Open the orig Report
save as
& give it a new number(50000+ range) & new name

rj5570

wow, ok now i feel like such an idiot didnt realize it was that easy !LOL

:oops:

thanks for your help!!!!!!!!!!!!

DenSter

Don't feel bad, it's something you didn't know, it doesn't matter if it is 'easy'. The good thing is now you know