RENAME statement comes up with strange key field name

groover

Has anybody seen a message like this:

The field below must be included in the table's primary key:
Field: Description
Table: Item

The error pops up when I try to rename an Item, even if the item is completely new and has no related entries (ledger entries, sales lines, translations, vendors, ...) at all.

The exact code is:

recItem2.RENAME("Search Description");

It is part of a small customization to replace the Item No. by a unique Search Description. But why would it come up with the "Description" field???

Marije_Brummel

It should be something like

Item.GET('1000');
Item.RENAME(Item."Search Description");

This works fine.

Beware of the fact that "Search Description" is 30 characters long, also it is modified then the description is modified.

groover

Well, in fact I DO a:

recItem2.GET

before doing the RENAME. When debugging it breaks on the line with the RENAME statement, which makes me think that it has something to do with the RENAME function itself and the way it uses keys. What I simply don't understand is why it wants the "Description" field to appear in the Primary Key... In version 3.01 it works, in version 4.00 it doesn't work anymore...

groover

And more:
1. The OnValidate trigger of the Description field has been modified so that the Search Description is no longer changed when the description is changed.
2. The error occurs even with a clean Item record (with NO related entries at all).
3. If I open the 301 database in 400, it still works.
4. If I dump my 4.00 form into the 301 database, it still works.
5. If I use this code in a completely new company, it works too...
==> Is this data related then?

Marije_Brummel

Is there some strange tablerelation in the item description or search description field?

groover

No. The TableRelation property contains the default value <Undefined> for both fields...

Marije_Brummel

And the "No." field? Any strange tablerelations?

groover

No

Marije_Brummel

groover wrote:

The field below must be included in the table's primary key:
Field: Description
Table: Item

This error message is shown when you make a tablerelation with a non-primairy key field of a table.

Somewhere in your database, there has to be a field with a tablerelation Item.Description

The Rename function triggers all related fields to be changed.

Export all tables to a textfile and search this.

groover

OK. Found it. It was indeed a very weird piece of customization. It was a normal field, with Editable=No, and with a Conditional TableRelation referring to the Item.Description field. Strange, because it was never used. I reckon the badly designed field was initially meant to be a FlowField of the "Lookup" type. I have cleaned up the mess.

Many thanks for the advice, Mark!!!

DenSter

Guys you are looking in the wrong place.....

The ONLY time that you would use RENAME is when you need to change the primary key of a record. For example:

Item.GET('10000');
Item."No." := '20000';
Item.RENAME;

This will work because the "No." field is the primary key of the Item table. You can also do:

SalesLine.GET(SalesLine."Document Type"::Order,'1234',10000);
SalesLine."Line No." := 20000;
SalesLine.RENAME;

This will work, because the "Line No." field is part of the primary key of the Sales Line table.

This, however:

Item.GET('10000');
Item.RENAME(Item."Search Description");

Will not work, because the search description is not part of the primary key. If you need to modify any other field that is not the primary key, you do this:

Item.GET('10000');
Item.VALIDATE("Search Description",'NEWVALUE');
Item.MODIFY(TRUE);

You don't need to use RENAME, because you're not modifying primary key values.

Marije_Brummel

Hi Daniel.

Problem is solved.

You were 3 minutes to late

I had to dig realy deep in my memory, but I knew I saw this message before.

DenSter

Cool, I'm happy the problem is solved. I just felt the need to elaborate a little, because there was mention of exact code:

recItem2.RENAME("Search Description");

and that is just not correct.

Good to know though that in RENAME there can also be issues with table relations, I have not seen that before.

Marije_Brummel

RENAME will update all table related fields, exept when the renamed table is shared over all companies.

Whenever consultants test new versions of my add on it is one of the first things they do, to test the tablerelations.

groover

Hi Daniel,

Your last piece of code actually DOES work. And the intention of the code WAS/IS to change the primary key. The syntax of is correct

Item.RENAME(Item."Search Description");

but you have to be aware, as Mark said, that Search Description can be 30 characters, and that it is normally changed when Description is changed!!! Thanks for the advice, to both of you!

DenSter wrote:

Guys you are looking in the wrong place.....

The ONLY time that you would use RENAME is when you need to change the primary key of a record. For example:

Item.GET('10000');
Item."No." := '20000';
Item.RENAME;

This will work because the "No." field is the primary key of the Item table. You can also do:

SalesLine.GET(SalesLine."Document Type"::Order,'1234',10000);
SalesLine."Line No." := 20000;
SalesLine.RENAME;

This will work, because the "Line No." field is part of the primary key of the Sales Line table.

This, however:

Item.GET('10000');
Item.RENAME(Item."Search Description");

Will not work, because the search description is not part of the primary key. If you need to modify any other field that is not the primary key, you do this:

Item.GET('10000');
Item.VALIDATE("Search Description",'NEWVALUE');
Item.MODIFY(TRUE);

You don't need to use RENAME, because you're not modifying primary key values.

DenSter

Maybe I misunderstood what you want to do.

Item.RENAME(Item."Search Description");

This line of code will change the primary key value of the record in the Item variable with the current value of the "Search Description" field as the new value of the primary key. If that is what you want it to do, then yes it works (of course taking into account the fieldlength issue).

I thought you wanted to change the value of the "Search Description" field. Maybe it was me who was looking in the wrong place afte all. Wouldn't be the first time, and certainly won't be the last 8)