Setting filters

ataris

I would like to set a filter on a table in code

I want to take the Phone No. from the customer card and place it in the Contact card.

I would like to know how i can set the keys in the contact table so i can filter on the customer table then assign the phone number. So far i have written


Cont.SETCURRENTKEY("No.", "Company Name");
Cust.Cust.SETFILTER(Cust."No.",

HELP PLEASE

Marije_Brummel

What version are you running? This is synchronised automaticaly in standard NAV.

ataris

I have written this so far, version is 3.7b....it complies but it still doesn't bring anything in.


Cust.RESET;
IF Cust.FIND('-') THEN BEGIN
REPEAT
IF Cont.GET THEN BEGIN
Cont."Phone Number" := Cust."Phone No.";
Cont.MODIFY;
END;
UNTIL Cust.NEXT = 0;
END;

DenSter

I don't know what you want to do, but I can explain what your code does, maybe you can conclude what you need to change.

First, you do a RESET, which removes all filters from the Cust variable. This means you are going to do this for all records in the Cust variable. I am assuming that this is a record variable based on the Customer table.

Cust.RESET;
IF Cust.FIND('-') THEN BEGIN
REPEAT

Next you do a GET on the Cont table, but you are not specifying which Cont to get. When you use the GET method, you must provide values for the key fields. If this is a variable based on the Contact table, then you must provide a value for the "No." field in order for Navision to uniquely identify a Contact.

IF Cont.GET THEN BEGIN

Because GET does not return a record, the following code will not be executed

Cont."Phone Number" := Cust."Phone No.";
Cont.MODIFY;
END;
UNTIL Cust.NEXT = 0;
END;

Now let's say that you want to do this for all costumers' primary contacts. I would do it something like this:

Cust.RESET;
IF Cust.FIND('-') THEN BEGIN
REPEAT
  IF Cont.GET(Cust."Primary Contact No.") THEN BEGIN
    Cont."Phone Number" := Cust."Phone No.";
    Cont.MODIFY;
  END;
UNTIL Cust.NEXT = 0;

If you want to update all contacts associated with the customer, it gets more complicated, because the link goes through the Contact Business Relation table.

ataris

Thanks for all your replies. I have changed the code to the following in customer table on Phone No.(OnValidate) trigger:

Cont.RESET;
Cont.SETRANGE(Cont."No.", "Primary Contact No.");
Cont.FIND('-');
Cont."Phone Number" := "Phone No.";
Cont.MODIFY;

My idea is to create a report which will validate the phone no. field on the customer card which should then filter down to the contact card. The code so far complies but it doesn't work, I seriously want to throw my comuter out the window...

DenSter

What you are doing can result in unexpected results:

This first part is correct. You can do GET with the primary contact number because you are using the primary key value of the contact table, but this should also filter down the table enough to get the right contact record.

ataris wrote:

Cont.RESET;
Cont.SETRANGE(Cont."No.", "Primary Contact No.");

If there is no value in your Primary Contact Number field, then this next line of code will error out. If FIND does not find anything, and you're not evaluating the return value, the system will cause an error.

ataris wrote:

Cont.FIND('-');

These last two lines are correct, but again if there is no value in your primary contact number field, it will not even get this far, and it will error out at the FIND statement.

ataris wrote:

Cont."Phone Number" := "Phone No.";
Cont.MODIFY;

Now if you are set on doing the FIND instead of the GET, you should do it like this:

Cont.RESET;
Cont.SETRANGE(Cont."No.", "Primary Contact No.");
// put the FIND inside an IF statement and it will only execute the code
// inside if there is a record in your filter
IF Cont.FIND('-') THEN BEGIN
  Cont."Phone Number" := "Phone No.";
  Cont.MODIFY; 
END;

The value in the Primary Contact Number field on the Customer record is the primary key field of the contact record, so you can do GET, like this:

IF Cont.GET("Primary Contact No.") THEN BEGIN
    Cont."Phone Number" := "Phone No.";
    Cont.MODIFY;
  END;

Now you would put that code in the OnValidate trigger of your Phone No field. In your report, you would put VALIDATE(""Phone No."); in the OnAfterGetRecord trigger of the customer dataitem, so after each customer record is retrieved it executes this code and you should see the phone numbers come through.

ataris

Mate thank you so much for such a good explanation. People like you are hard to come by.

It worked a treat.

Could i possible get your email address, I'm not saying I'll email every five seconds but if i come stuck or need someone to explain something you the one for the job. My email is ataris10@yahoo.com.
Drop me a line.

Thanks again

DenSter

If I do that I will have to start charging you for the work . I just happened to be the first to pick up your issue and stuck with it. Many times you will find other people chiming in and giving you other suggestions as well, so it would be more beneficial to you to just post your issues here.

At some point you will even start answering other people's questions, believe it or not, and that is how a forum like this lives.

ataris

:P , nice one.

Thanks for your help.

Cheers

Savatage

DenSter wrote:

If I do that I will have to start charging you for the work .

I'm sure he doesn't work cheap either

DenSter

And so little of it do I see myself... :shock: