Problem getting data form a table

Red-Eagle

I have strange problem, when make connection with table Customer:

IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN;
Faxnummer := Klantrec."Fax No.";

Then i get the fax number from that table, but when i make a connection with ship-to address table

IF Shiprec.GET("Issued Reminder Header"."Customer No.") THEN;
Faxnummer := Shiprec."Fax No.";

then faxnummer will stay empty.

What can be the problem?

einsTeIn.NET

Table Ship-To Address has got a primary key that consists of two fields. GET uses the pk, thus you have to deliver all parts of it as parameter. You deliver only the first part.

And afaik reminder in NAV standard doesn't work for Ship-To Address.

DenSter

The ship-to address table has a different primary key than the Customer table. If you want to use GET, you will need to specify all primary key fields ("Customer No." and "Code" in the case of the Ship-to Address table). If you only know one field of a compound primary key, you will need to set a filter and use FINDFIRST to retrieve the record from the database.

Savatage

the problem is that the Ship-to Customer table's key is "Customer No. & Code"

With get you need to fill in the key's with a value and you're only using customer#

Savatage

3 reply's all within 10 seconds

SNielsen

And a final comment, the way your code is structured your IF-THEN seems to be structured incorrectly:

IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN
  Faxnummer := Klantrec."Fax No.";

in your example you have a ";" after THEN, which terminates the IF-THEN statement.

IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN;
Faxnummer := Klantrec."Fax No.";

DenSter

SNielsen wrote:

And a final comment, the way your code is structured your IF-THEN seems to be structured incorrectly:

Indeed. I would take it one step further:

IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN BEGIN
  Faxnummer := Klantrec."Fax No.";
END ELSE BEGIN
  Faxnummer := 'not found';
END;

I always add BEGIN and END, just in case the fax number isn't all you need to do.

Red-Eagle

DenSter wrote:

The ship-to address table has a different primary key than the Customer table. If you want to use GET, you will need to specify all primary key fields ("Customer No." and "Code" in the case of the Ship-to Address table). If you only know one field of a compound primary key, you will need to set a filter and use FINDFIRST to retrieve the record from the database.

I know only one field. So how do i use this Findfirst then in this situation?

Savatage

If you only know one field then why get the fax number from the Ship-to Address table?

What if a customer has 10 ship-to's and each has a different fax #.
Which one would you like to get?
Usually when a customer has multiple ship-to's then the information on the main customer card is usually the "main office".

Is the fax # on the main customer card not the one you're looking for?

Does this "Issued Reminder Header" not have a ship-to code?
Perhaps adding a lookup to the ship-to address table can help.

Red-Eagle

The problem is that i need the fax number from the ship-to table when it is a billing address. I have a lot of customers where the customer is a different address as the address which the invoice have to go.

I constructed some code which had to do that, but that isnt working because of the two keys in one table. As understand from your answers. Thnx by the way for that.

This is the code i had:


CLEAR(Shiprec);
IF Shiprec.GET("Issued Reminder Header"."Customer No.") THEN;
IF Shiprec."Billing Address" = TRUE THEN BEGIN
Faxnummer := Shiprec."Fax No.";
END ELSE BEGIN
CLEAR(Klantrec);
IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN
Faxnummer := Klantrec."Fax No.";
END;

Because this wasnt working i made it simpler to see which part of the code worked and where i could found the problem.

einsTeIn.NET

As I said before...

einsTeIn.NET wrote:

And afaik reminder in NAV standard doesn't work for Ship-To Address.

How do you set the address of one Ship-To Address to the reminder? How do you split your Ledger Entries to the different Ship-To Addresses? It's not just about getting the correct Fax No., but also about your data is entered in a wrong way.

Red-Eagle

Okay then it will be a different approach.

If the customer no. exist in the table ship-to address and it is a billing address then it has to look if the issued reminder header.address is the same as ship-to address. When it is it has to take this fax number when it is not it may take the fax number of the customer table.

In code it has to look something like this (the green part is working), but the red one isnt:

CLEAR(Shiprec);
IF Shiprec.GET("Issued Reminder Header"."Customer No.") AND //customer no. needs to be the same and exits
(Shiprec.Address = "Issued Reminder Header".Address) AND //the extra link to get the right address
Shiprec."Billing Address" = TRUE THEN BEGIN //third link to look only at billing address
Faxnummer := Shiprec."Fax No.";
END ELSE BEGIN
CLEAR(Klantrec);
IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN
Faxnummer := Klantrec."Fax No.";
END;

Is this more in the right way?

ufuk

Try this and you should read again the things told above about keys:

Shiprec.SETRANGE("Customer No.","Issued Reminder Header"."Customer No."); 
Shiprec.SETFILTER(Address,"Issued Reminder Header".Address); 
Shiprec.SETRANGE("Billing Address",TRUE);
IF Shiprec.FINDSET THEN // this record is not unique yet
  Faxnummer := Shiprec."Fax No."
ELSE 
   IF Klantrec.GET("Issued Reminder Header"."Customer No.") THEN
     Faxnummer := Klantrec."Fax No.";

Red-Eagle

Thnx for all the effort. I now understand that it wont be easy. I thinked about. The only way it could do it is connect issued reminder documentnumber at the Sales Invoice Header. There i have the code which is used in the ship-to address table. That wont be easy but maybe i will look at it. It is the only way to get both keys at once

DenSter

What you really need is some training in C/AL development, because it does not look like you know what you are doing. There's nothing wrong with that, but you should really get some help on the fundamental NAV development skills.

You need to get some help from a senior developer, get the training material, and study for the certification exams, which is a good start. I'd also recommend David Studebaker's books on development and Mark Brummel's book on application design.