How to do this - Link a table on a report

Leroy

Hello again, I think this comes in the standard, is the 5067 report. The report has only one Dataitem, the Customer table, and a filter for customers that shows the available balance of each and how to print a list of tags. I made a modification so that when you run the report it automatically sent by mail to every customer balance as follows:

IF Customer.GET ( "No.") THEN BEGIN 
IF "E-Mail "<>'' THEN BEGIN 
CodeMail.NewMessage ( "E-Mail ",''," Notice - Customer No. '+" No. " 
'Please note that the amount to declare' 
+ 'Is' + value,'', TRUE); 
END; 
END; 

CodeMail is codeunit Mail

It works perfectly, but it gets the mail field from the Customer table. Now I want that it gets the mail address of the person in the department of accounting firm contact type for this customer. The table that contains the data is the 5067-Contact Job Responsibility but I can`t link it to get the email address of the department. Can someone please help me?.
Many thanks in advance.

DaveT

Hi,

To get the contact link through the Contact Business Relation and the lookup the contact table.

Leroy

Thanks for reply, it works putting it on dataitems. If I don't want to put this table on Dataitems, I mean, If I want create a variable to get this table and look for it on body customer sections, how I've to do it?. I've put the code:

relation.INIT;
IF relation.GET("No.") THEN;

relation is the Contact business relation table.

And it gets it, but don't show the fields from that table on customer body, what is wrong please?.
Thank you very much for your help.

DaveT

Hi,

You will need extra code - the GET function is only for the primary key. Try something like...

relation.RESET;
relation.setrange( "Link to Table", relation."Link to Table"::Customer );
relation.setrange( "no.", customer."no." );

IF relation.find( '-' ) THEN
  contact.get( relation."Contact no." )
else
   contact.init;

Leroy

Thanks for reply. I've try the code but sttops in relation.RESET; and says "required a return value". Also you put de contact table, I need it?.
Thanks.

DaveT

Hi,

relation is actually a C/AL function - use a different variable name e.g. BusRelation

The E-Mail address is store on the contact table not the Contact Business Relation table so you will need the Contact table as well

Leroy

Thanks for reply, it works!! thanks. But there is an error, may be I don't explain myself well, sorry, I need the mail from the "Job Responsibility Code" field filtering by Accounting contact form "Contact Job Responsibility" table, not from Contact table. It's possible?,
Thanks you very mucho for your help.

DaveT

Hi Leroy,

We have more work to do to link this in.

So far we have go the company contact for a customer. The next step is to find all the related contacts and then find the one(s) with the correct "Job Responsibility Code".
so

EMailText := '';
BusRelation.find( '-' ) THEN
  begin
    if contact.get( relation."Contact no." ) then
       begin
          PersonContact.reset;
          PersonContact.setrange( Type, PersonContact.type::Person );
          PersonContact.setrange( "company no.", contact."no." );
          if personcontact.find( '-' ) then  
            repeat
              if ContactJobResponsibility.get( personcontact."no." ) then
                if ContactJobResponsibility."Job Responsibility Code" = 'EMAILCONTACT' then
                   EmailText := personcontact."E-Mail" + ';' + EMailText;
            until personcontact.next = 0;
       end;

This will build up a string text with emailaddress;emailaddress;emailaddress

Leroy

Thanks for reply and for your time. Sorry, is your're so kind, can you explain what tables or kind of datatype varibles are the followin variables that you have explain?.

EMailText
BusRelation
PersonContact
ContactJobResponsibility

My version is in spanish and I can't find some tables.
Thanks again.

DaveT

Hi Leroy,

EMailText - is a text variable to store the E-Mail address (or more than one if required)
BusRelation - is a record variable for table 5054
PersonContact - is a record variable for table 5050 (a second variable to loop type person contact)
ContactJobResponsibility - - is a record variable for table 5067

Hope this clears it up for you.

Leroy

Thanls for help; it's very clear, thank you for everything. I've try and all perfect on save it, but the variable EMailText (I guess it's this that should display the e-mail field) don't show me nothing. The email field from Contact Job Responsibility table is a flowfield, may be is this a problem?.
Thanks for your time.

DaveT

Hi Leroy,

Firstly, Gald to Help

On my version the E-Mail is stored on the contact table and not the Contact Job Responsibility table. If it is a flowfield then you need to do a CALCFIELDS to get the value. If you post your code I'll have a look.

Leroy

Thanks indeed for help. This is the whole code; it's on Customer body.

relacion.RESET;
relacion.SETRANGE( "Link to Table", relacion."Link to Table"::Customer );
relacion.SETRANGE( "No.", Customer."No." );

IF relacion.FIND( '-' ) THEN
 contacto.GET( relacion."Contact No." )
ELSE
 contacto.INIT;

ContactJobResponsibility.CALCFIELDS("Correo-e");

EMailText := '';
IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( relacion."Contact No." ) THEN
       BEGIN
          PersonContact.RESET;
          PersonContact.SETRANGE( Type, PersonContact.Type::Person );
          PersonContact.SETRANGE( "Company No.", contacto."No." );
          IF PersonContact.FIND( '-' ) THEN  
            REPEAT
              IF ContactJobResponsibility.GET( PersonContact."No." ) THEN
                IF ContactJobResponsibility."Job Responsibility Code" = 'DPTO. CONTABILIDAD' THEN
                   EMailText := ContactJobResponsibility."Correo-e" + ';' + EMailText;
            UNTIL PersonContact.NEXT = 0;
       END;
  END;

contacto is Contact table
"Correo-e" is the e-mail field from ContactJobResponsibility table that I want to show
relacion is Contact Business Relation table

Thanks for help.

DaveT

Hi Leroy,

The CALCFIELDS was in the wrong place (and I've done a bit of a tidy )

BusRelation.RESET;
BusRelation.SETRANGE( "Link to Table", BusRelation."Link to Table"::Customer );
BusRelation.SETRANGE( "No.", Customer."No." );

EMailText := '';
IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( relacion."Contact No." ) THEN
       BEGIN
          PersonContact.RESET;
          PersonContact.SETRANGE( Type, PersonContact.Type::Person );
          PersonContact.SETRANGE( "Company No.", contacto."No." );
          IF PersonContact.FIND( '-' ) THEN  
            REPEAT
              IF ContactJobResponsibility.GET( PersonContact."No." ) THEN
                IF ContactJobResponsibility."Job Responsibility Code" = 'DPTO. CONTABILIDAD' THEN
                   begin
                     ContactJobResponsibility.CALCFIELDS("Correo-e");
                     EMailText := ContactJobResponsibility."Correo-e" + ';' + EMailText;
                   end;
            UNTIL PersonContact.NEXT = 0;
       END;
  END;

Leroy

Thanks for your help and time, but there is no way, It doesn't show anything. May be I'm doing somethig wrong. I will continue to testing it if I find something strange.
Thanks for all your dedication.

DaveT

Hi Leroy,

Had another look at the code and found a problem it should read

BusRelation.RESET;
BusRelation.SETRANGE( "Link to Table", BusRelation."Link to Table"::Customer );
BusRelation.SETRANGE( "No.", Customer."No." );

EMailText := '';
IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( relacion."Contact No." ) THEN
       BEGIN
          PersonContact.RESET;
          PersonContact.SETRANGE( Type, PersonContact.Type::Person );
          PersonContact.SETRANGE( "Company No.", contacto."No." );
          IF PersonContact.FIND( '-' ) THEN  
            REPEAT
              IF ContactJobResponsibility.GET( PersonContact."No.", 'DPTO. CONTABILIDAD'  ) THEN
                   begin
                     ContactJobResponsibility.CALCFIELDS("Correo-e");
                     EMailText := ContactJobResponsibility."Correo-e" + ';' + EMailText;
                   end;
            UNTIL PersonContact.NEXT = 0;
       END;
  END;

A contact can have more than one job responsibility.

Leroy

Thanks for your patienece, you must be tired of my :roll:

Well, it continues without work. It's true that a contact can have more than one job responsibility, i'm filtering by DEP. CONTABILIDAD (accounting department in english), also can be more than a contact in the accountig department, but showing only one is enough. I'll test the code again.
Thanks for your help.

DaveT

Hi Leroy,

I spotted another error
IF contacto.GET( relacion."Contact No." ) THEN
should be
IF contacto.GET( BusRelation."Contact No." ) THEN

Fingers crossed

Leroy

Thanks for post it. I've observed that don't get any field from Contact Job Responsibility table, I mean, if I put on section ContactJobResponsibility."Contact No." or either don't show it; it seems like don't go to table ContactJobResponsibility.
Well, thanks really for help.

DaveT

Hi Leroy,

Not sure what you mean. The code is designed to get the information off the Contact Job Responsibility table using a variable ContactJobResponsibility. It then stores this in a Variable EMailText and this is what you need to print. If you put a section for the Contact Job Responsibility table then completely different code is needed.

Leroy

Ok, thank you for reply. I'll test the code to find a solution.
Thank you very much for your help.

Leroy

Dear Dave, forgive bothering you again. I've put as a flowfield the "Job responsibility code" field from "Contact job responsibility" table on Contact table, so I don't need "Contact job responsibility" table on report, and either "Contact Business Relation" table I think. Then I only need to link Contact table with Customer table on report. If not much trouble for you, how I can do that with the code that you have put to me?.
Thank you for help

DaveT

Hi Leroy,

You only need the customer dataitem on the report and the corrected code. You will have to declare variables for the tables used i.e. BusRelation, contacto, PersonContact and ContactJobResponsibility. Put the code in the OnAfterGetRecord trigger of the Customer dataitem and then in the print section add a textbox with SourceExpr of EMailText.

Hope this clears it up for you.

Leroy

Ok, I'll try it. Thanks again for your time and help. =D>