Problem filtering Customer table with Cont. job R. table

Leroy

Dear folks, a few days ago I ask for my problem but now the situation has changed, so I'd like to post it in a new topic.
I've a report that has the Customer table as only Dataitem. I want that the report shows the e-mail contact person type that has as a job responsibility code "Accounting" department. This is the code that I put on customer OnAfterGetRecord:

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

jobrelation.RESET;
jobrelation.SETRANGE("Contact No.",BusRelation."No.");


IF BusRelation.FIND( '-' ) THEN;
contacto.SETRANGE("Company No.",BusRelation."Contact No.");
contacto.SETRANGE(Type, contacto.Type::Person);
jobrelation.SETRANGE("Job Responsibility Code",'Accounting');
IF contacto.FIND( '-' ) THEN REPEAT
UNTIL contacto.NEXT = 0;

BusRelation var is table Contactact Business Relation
contacto var is table Contact
jobrelation var es table Contact job Responsibility

The code runs well, but go to the contact person type from contact table from that customer, not from "Contact job Resposibility" table to get the e-mail from Accounting department. How can I do it?. As a second option, if that e-mail field can be moved to the customer table as a new field it also will be good to me.
Thanks for your valuable help.

DaveT

Hi Leroy,

Can you verify that you want to find the company contact first i.e.

Customer -> Business Relation -> Contact Type "Company"

then find a contact type "Person" associated with the Contact Type "Company" who has a Contact job Responsibility of 'Accounting'. If found then use the contact E-Mail address. Is this is correct then I will post the code for you.

Leroy

Hello again Dave, thanks for reply. Yes, it's just as you've indicated.
Thank you.

DaveT

Hi Leroy,

The code you need on the onaftergetrecord of the customer is:

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

EMailFound := FASLE;

IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( BusRelation."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
                 EmailFound := TRUE;
            UNTIL ( PersonContact.NEXT = 0 ) OR EMailFound;
       END;
  END;

if NOT EmailFound then
   PersonContact.init;

message( PersonContact."E-Mail" ); // For testing only

You will need to print the PersonContact."E-Mail" on the customer body section.

The message statement is just for testing and EMailFound is a variable of type Boolean.

Leroy

Sorry, I've I made a mistake. I noticed that not all contacts from Contact job reponsibilite table are listed in the contact table. I've to go necessarily to the ContactJobResponsibility table and get from there the mail from the person who is in that department. Now, this code filter by contact table and show the email from the contact company not person from that department. Any idea please?, sorry for the inconvenience.
Thanks for help.

DaveT

Hi Leroy,

Sorry I'm a bit confused. If you are going back to the company type contact then you already have got this in the contacto variable.

the sequence you are proposing is:

Customer -> Business Relation -> Company Contact -> Contact Job Responsibility (Accounts)
                                                         ^                            |
                                                         |                            V
                                                          --------------- Company Contact

Is the origonal company contact not correct?

Leroy

Thanks for reply Dave. You're correct, but by this way the contact and the e-mail that appears on report is from Contact table, not from Contact Job Responsibility table, if I put the e-mail field in report from Contact Job Responsibility table it appears empty. In contact table don't appear all contacts person type from that department but the nº of customer it's correct, I don't know why.
Thanks for help.

Leroy

Sorry, snother thing. A contact type person can be in two departments, but in contact table only appear one department because the contact only appear one time, normally gets the first department. Then, if you go to Cont. job R. table you will see this contact two times, a resgister for department. This can be one of the problems.
Thanks.

DaveT

Hi Leroy

A Slight change should do as you want:

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

EMailFound := FASLE;

IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( BusRelation."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
                 EmailFound := TRUE;
            UNTIL ( PersonContact.NEXT = 0 ) OR EMailFound;
       END;
  END;

if NOT EmailFound then
   ContactJobResponsibility.init;

// might need ContactJobResponsibility.calcfields( "E-Mail" ); if a flowfield
message( ContactJobResponsibility."E-Mail" ); // For testing only

this gets all the person contact for a customer and returns the E-Mail from the first person with job responsibility 'DPTO. CONTABILIDAD'

Leroy

Perfect!!!! =D> It works!!!, well, finally I've seen that the Job Responsibility Code from Contact table doesn't have bringing the CONTABILIDAD department, only was filtering by the person contact from that company ](*,) . I've put it well and now the code simplifies, I don't need the Contact job responsibility table then your code runs well with only put contact table and filtering by departement instead of Contact job responsibility table. It looks like this:

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

EmailFound := FALSE;

IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( BusRelation."Contact No." ) THEN
       BEGIN
          PersonContact.RESET;
          PersonContact.SETRANGE( Type, PersonContact.Type::Person );
          PersonContact.SETRANGE( "Company No.", contacto."No." );
          IF PersonContact.FIND( '-' ) THEN
            REPEAT
              PersonContact.SETFILTER("Job Responsibility Code",'CONTABILIDAD');
                 EmailFound := TRUE;
            UNTIL ( PersonContact.NEXT = 0 ) OR EmailFound;
       END;
  END;

IF NOT EmailFound THEN
   PersonContact.INIT;

The only problem is that if there is not a "CONTABILIDAD" department contact person, the report gets the first contact person from that company that finds. It would be nice if there is no one in the CONTABILIDAD department gets the mail from main contact or leave it in blank. Do you know how I can do it if it is not very complicated for you?.
Thank you very much for your help and time.

DaveT

Hi Leroy,

Good work

What I think you need is even simplier:

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

EmailFound := FALSE;

IF BusRelation.FIND( '-' ) THEN
  BEGIN
    IF contacto.GET( BusRelation."Contact No." ) THEN
       BEGIN
          PersonContact.RESET;
          PersonContact.SETRANGE( Type, PersonContact.Type::Person );
          PersonContact.SETRANGE( "Company No.", contacto."No." );
          PersonContact.SETFILTER("Job Responsibility Code",'CONTABILIDAD');
          IF PersonContact.FIND( '-' ) THEN
             EmailFound := TRUE;           
       END;
  END;

IF NOT EmailFound THEN
   PersonContact.INIT;

Glad to Help

Leroy

Hi Dave, It's works perfectly!!! =D> . I would like to thank all your help, patience and your time. You have helped me a lot.
Thanks again for help.