How do I auto fill a field in a subform;

Bagheera

Hello,

I have a table (Act) with 2 fields.
Code
Description

That is a static table, the data in there never changes.

Then I have a form with a subform and there are 2 fields in there:
Act. Code
Act. Description

I have linked the Act. Code field to the Code field from the first table (Act).
So lookup works and I can select any code I want.

What I want is when I select the code, the description field to be automatically completed with the description that corresponds to that code as it is stored in the first (static) table - the one I named act.

It is the exact same thing like when filling in an invoice and you select an item number and the description is filled automatically the moment you select that number.

I am new in Navision,
thank you in advance.

Bagheera

Ok found it...

Act. Code - OnAfterValidate()
CurrCode := "Act. Code"; //Field Value to Local Var
Activities.RESET; // Reset Recordset of Static Table
Activities.SETFILTER(Code, CurrCode); // Filter Static Table
Activities.NEXT; // Go to 1st row - there is only one after filtering
"Act. Description" := Activities.Description; //Assign value from record to field in table

Just let me know, is that code safe to use?

ALSO, after filling some lines that way, at random points no matter what code I select the description gets stuck to one value... Can u see what I might be doing wrong?

Caffa

Well Bagheera,

Why don't you use the following:

Act. Code - OnAfterValidate()
IF NOT Act.GET("Act. Code") THEN
  CLEAR(Act);

"Act. Description" := Act.Description;

Your code would probably also work, but it's way to complicated for such an easy adjustment......

Bagheera

I used this fragment of code and description does not get "stuck" anymore - it works fine.

IF NOT Act.GET("Act. Code") THEN
  CLEAR(Act);

But without the rest of my code and just the

IF NOT Act.GET("Act. Code") THEN
  CLEAR(Act);

it does not work at all.

What does the IF NOT part do? I am a beginner in navision but I've programmed a lot in Java but still I dont get what this statement does.

DenSter

no that is way to much work you guys....

Create a flowfield into the Description field in the other table, and enter one line of code in the OnValidate trigger of the Code field:

CALCFIELDS(Description);

Don't forget to set the editable property of the description field to No.

By the way, you program IF NOT rec.GET, because otherwise the system will generate an error if it doesn't find the record. In situations in which you don't want this error, you catch the return value of the GET method.

Caffa

yes offcourse a Flowfield does the trick, but.......

...It is the exact same thing like when filling in an invoice and you select an item number and the description is filled automatically the moment you select that number...

.. with a flowfield you'll miss the above standing functionality....

Bagheera

By the way, you program IF NOT rec.GET, because otherwise the system will generate an error if it doesn't find the record. In situations in which you don't want this error, you catch the return value of the GET method.

But why do I need this. There is no way a record wont be found as the table is static... It only has 2 columns, code and description and all I need is to select the code and then the description to be filled automatically.

The weird thing I am noticing is that:

That is my static table
Code Description
A1 Descr. A
A2 Descr. A2
A3 Descr. BLAH


Then in a subform there are 2 colums one connected to Code field from above and the other one that should get the description filled automatically when we pick a code.

It works fine but it gets stuck after enntering some lines.
For example I pick A1 - Descr. A is filled. I pick A2 Descr A2 is filled then I pick A3 BUT Descr A2 is filled again!!! And that occurs randomly not at the same point every time.

After I tried the IF NOT Rec.GET it stopped doing this, and it works fine but I cant realise what this piece of code had to do with it.

Sorry for the hussle I am a noob @ navision.

Caffa

it's just a standard way of handling such situations.....

When you fill in a code, the description will also be filled. When you decide to empty Code again, the description will also be made empty.

Perhaps this won't happen in your scenario, but this construction just makes sure that when you empty the Code, the description will also be gone........

Bagheera

No it wont work and now I am more confused than ever.

IF NOT "Post Codes".GET("Post Code") THEN
  CLEAR("Post Codes");

CurrCode := "Post Code";
"Post Codes".RESET;
"Post Codes".SETFILTER(Code, CurrCode);
"Post Codes".NEXT;
"City Description" := "Post Codes".City;

"Post Codes" is the Record
"Post Code" is the Field I perform the LookUp on
CurrCode is just a local variable
"City Description" is the field that I want to get auto filled

This way, it works fine (although I still dont get the IF NOT fragment of code).

2 things.

1. If I remove the IF NOT fragment it will still work but at random times descriptions will all be filled from a description used in a record above.
example.
1st record got desc AA which is correct
2nd record got desc BB which is correct
3rd record got desc CC which is correct
4th record got desc CC again which is NOT correct
5th record got desc CC again which is NOT correct
all records from now on get CC as description no matter what code I select. This happens at random times. It does not occur if I have the IF NOT fragment of code.

2. Now the code as it is (including IF NOT) does not clear descriptions if I clear the code... Instead it will fill description with the first description of the table (the table that contains all descriptions).

I am getting so frustrated, these things are a joke in other programming languages and here I dont even get it.

Thanks again for the help...

Caffa

it's starting to look as a joke to me......

PLEASE JUST REMOVE THE FOLLOWING....

CurrCode := "Post Code"; 
"Post Codes".RESET; 
"Post Codes".SETFILTER(Code, CurrCode); 
"Post Codes".NEXT; 

This code is totally not necessary!!

One more time about the IF NOT....:
this makes sure that when you enter a Code which exists as a record then variable will NOT be cleared, and the Description will be filled with the record you just retrieved from the database.
BUT: When you enter a code which does not exist, the variable will be cleared, and description will be made empty ('')

One more thing: I assume that the PK of the record you're trying to GET only contains the field "Code".

Bagheera

THANK YOU FOR BEING CLEAR ABOUT THE IF NOT. IT NOW MAKES SENSE.

You are correct the code you mentioned is not necessary IF the PK is the code field. What happens If I need 2 do the same thing and the PK is not just the code field?

Should I do it the way I was doing it up to now or there is a better solution?

Caffa

If the PK contans more then one field, and these values are all available, just use exactly the same code as I mentioned before, only the GET will look like: Rec.GET(Value1,Value2[,Value3....]).

When not all the values of the PK are available you ofcourse can't GET the record. In that case use something like:

MyRec.RESET;
MyRec.SETCURRENTKEY(Field1,Field2,....);
MyRec.SETRANGE(Field1,Value1);
MyRec.SETRANGE(Field2,Value2);
IF NOT MyRec.FIND('-') THEN
  CLEAR(MyRec);

Description := MyRec.Description;

..or something like this.....

Caffa

DenSter

Caffa wrote:

yes offcourse a Flowfield does the trick, but.......

...It is the exact same thing like when filling in an invoice and you select an item number and the description is filled automatically the moment you select that number...

.. with a flowfield you'll miss the above standing functionality....

Which is why I suggested you put CALCFIELDS(Description); into the Code OnValidate.

DenSter

With all due respect, I believe you should consider a class in programming principles. It seems to me that you just don't understand the basics of programming.

Try something like this:

IF "Post Codes".GET("Post Code") THEN 
  Description := "Post Codes".Description
ELSE
  Description := '';

Which means that your code will set the description field to the description of the "Post Codes" record, but only if it finds one. If it does NOT find one, then it sets it to blank. If you do not understand why you have to look at the return value of the GET method, then you definately need some instruction about programming basics.

DenSter

Act. Code - OnAfterValidate()
CurrCode := "Act. Code"; //Field Value to Local Var

This is not necessary, you can just use the value of "Act. Code" without storing the value in a separate variable

Activities.RESET; // Reset Recordset of Static Table
Activities.SETFILTER(Code, CurrCode); // Filter Static Table
Activities.NEXT; // Go to 1st row - there is only one after filtering

This will not work the way you'd want without doing a FIND first. If you want to get the first row, you would do Activities.FIND('-'); BUT, in this case you already know the key value of the Activities table, so you should not do a SETFILTER and a FIND, but a GET.

"Act. Description" := Activities.Description; //Assign value from record to field in table

This part is correct.

I would personally do this with a flowfield, but if I had to write code, this is how I would do it:

Act. Code - OnAfterValidate()
IF Activities.GET("Act. Code") THEN
  "Act. Description" := Activities.Description
ELSE
  "Act. Description" := '';

Bagheera

Dear all, thank you for replying...

DenSter its not the first time I am writing code in my life, actually I am a Java developer however its been 10 days since I started working with Navision. I just passed the programmer's exam with MS.

The thing is the IF NOT fragment of code was suggested to resolve a completely different issue from the one I was facing. Plus its syntax is kind of oversimplified over classic programming languages. For example:

IF NOT "Post Codes".GET("Post Code") THEN

is a bit confusing because first there is no API (or I have not found one) for C/AL in order to know what the GET function returns. Now I realise it is boolean this is why there is no comparison either (what I mean is this):

IF NOT "Post Codes".GET("Post Code") == TRUE THEN

that (if it exists in C/AL) would be much clearer...

Anyway, I am not offended or anything, I know what I know, its just the way one can code in Navision is a bit anorthodox if you are used to environments such as Java or .NET.

So yes I am a complete newbie in Navision but not a newbie overall.

Once again thank you all for your replies. Hour by hour this crappy environments starts making sense.

Savatage

Bagheera wrote:

Hour by hour this crappy environments starts making sense.

:-k

DenSter

I don't think you should call this a crappy environment after 10 days because you don't know how to use it. You could also look at it from another perspective, and say Java is a bit unorthodox if you're used to Navision.

By the way, you can do:
IF "Post Codes".GET("Post Code") = FALSE THEN

which will get you the same results as
IF NOT "Post Codes".GET("Post Code") THEN

You can also simply do
"Post Codes".GET("Post Code");

But if the record does not exist, your code will cause an error, which is why evaluating the return value makes sense in most cases.

Anyway, I don't want to get into another discussion about the merits of Navision development. I'm glad you were able to solve your problem, please come back if you have more questions.

Bagheera

I now understand the whole lot on this matter.

I realise that PK must be only the field I am performing the lookup. Can I change that key during the lookup like this:

"Post Codes".SETCURRENTKEY(Code);

IF "Post Codes".GET("Post Code") THEN
  "City Description" := "Post Codes".City
ELSE
  CLEAR("City Description");

Will the SETCURRENTKEY work? It seem to ignore me at the moment.

Luc_VanDyck

GET always uses the primary key to find the record, so it's no use to change the key before performing a GET-instruction.

From the on-line help:

GET
Use these functions to find a record based on values stored in primary key fields.

[...]

Comments
This function always uses the primary key for the table and ignores any filters. The system does not change the current key and filters after you call this function.

Bagheera

Pitty. Thanx for clarifying this. However is there a way to get this when the PK is not what you want?

I managed to do it by filtering the record and then doing RECORD.NEXT... Is this way the most appropriate one.?

DenSter

This information is all in F1, but I felt like writing it down .

SETCURRENTKEY is meant to change the sort order of the records in the recordset, it cannot be used to dynamically change the primary key of the table. If you don't specify the key using SETCURRENTKEY, Navision will assume that you want to sort by the primary key.

If the primary key of your table is Code, then you don't have to do SETCURRENTKEY(Code), that is the default sort order.

Even if you do SETCURRENTKEY(Description), you will still have to use the primary key to use the GET method, since that will only work with the primary key. In case you did not know, the primary key of a table is used to UNIQUELY identify a record. Using any non-primary key will not uniquely identify a record, and will therefore not guarantee the results.

About the NEXT method:
This method is used to go to another record, using the step as parameter (the default is 1, and means the next record in the sort order). So, if you want to go to the 5th record DOWN the list, you do NEXT(5). if you want to go to the 3rd UP the list, you do NEXT(-3).

In order to control the NEXT method, you need to know where you are to start out with, you have to 'fill the record set' and go to a starting record. After you set your filters, you then do FIND. The most common FIND parameter is either '-' or '+', which goes to the top of the list or the bottom of the list. There are other parameters you can use, refer to F1 help for more information.

Bagheera

Thank you all.