Using simple lookup and validate

tompynation

When i add a new Field to the Item table, namely "Custom No."
Every item will have a unique "Custom No.".

On the sales order for example the user will fill in the "Custom No" instead of the "No."
In the OnValidate of the "Custom No." inside the Sales Line table i wrote following code:


Custom No. - OnValidate()
IF "Custom No." <> '' THEN BEGIN
lv_Item.SETCURRENTKEY("Custom No.");
lv_Item.SETFILTER("Custom No.","Custom No.");
IF lv_Item.FINDFIRST THEN
VALIDATE("No.",lv_Item."No.");
END;

The "Custom No." does naturally have a table relation to the Item table:
Table Relation: Item."Custom No."

Now when i open the Sales Form and try to insert a "Custom No." i get the error that:
The field below must be included inside the table's primary key.
Field: Custom No.
Table: Item

Why is this error occuring?

Savatage

why didn't you use the item cross reference table & fields?? that functionality already exists.

To answer your question
lv_Item.SETCURRENTKEY("Sidec No.");

your telling it to use the key Sidec No - but the key doesn't exist. Or does it?
If not you'll have to add it to the item table.

tompynation

sure the key exists... i created it my self

Savatage

why not just change the AltSearchField Property of the Item."No." to "Sidec No." And Viola!

You'll be able to enter EITHER the Item No Or the Sidec no in the normal Item.No field and you will get the proper results?

It appears you get the message because the key you created is (i'm assuming) a sidec no key by itself. The error message is asking for it to be PART OF the primary key.

Do you have a GET somewhere too?

tompynation

well, i changed it to a programmatically Lookup which +- :roll:

Custom No. - OnValidate()
IF "Custom No." <> '' THEN BEGIN
lv_Item.SETCURRENTKEY("Custom No.");
lv_Item.SETRANGE("Custom No.","Custom No.");
IF lv_Item.FINDFIRST THEN BEGIN
"No." := lv_Item."No.";
VALIDATE("No.");
END;
END;

Custom No. - OnLookup()
IF FORM.RUNMODAL(31,lv_Item) = ACTION::LookupOK THEN BEGIN
"Custom No." := lv_Item."Custom No.";
VALIDATE("Custom No.");
END;

The only problem with this is that the "Custom No." does not get inserted into the salesline, but it does fill in the "No." through the validate code.

Now there is still a difference between inserting a sales line using the standard functionallity and inserting it through my custom method:

Item 1000 Fiets BLAUW STUKS 25/01/01 25/01/01 25/01/01
Item 1000 Fiets BLAUW STUKS 4.000,00 25/01/01 25/01/01 25/01/01

The first one was inserted using the "Custom No."
Second through normal NAV way... use you can see "Unit Price excl VAT" does not get filled in with my way?

I dont see why cause i validated the "No." field which would be same functionallity is just filling it in through normal nav way?

Savatage

I've done a similar code in the item journal - to allow me to use Item."No. 2" in the item journal.

item.SETCURRENTKEY(item."No. 2");
item.SETFILTER(item."No. 2","Item No. 2");
IF item.FIND('-')
THEN VALIDATE("Item No.",item."No.")
ELSE MESSAGE('Item Does Not Exist!');

Works for me.

Just curious if you're totally against both AltSearchField and/or using item cross reference?
Sounds to me like you'd like to be able to enter either and get the proper result. :-k
If you simply enter the Sidec no into the cross-reference table for that item, you'd be done.
The functionality of entering either an item no or a cross-reference no is already part of sales lines. the Altsearchfield would do it too if you're not using the default search value which is probably the search name.

garak

:-k why not using item cross reference for this?
When i understood you correct, you will a special "custom no". What is this? Is this "custom no" a customer specific no? I think not. It is like field "No. 2" if yes why not using Field "No. 2" :?:

so back to "Item Cross Reference" (Item Card -> Button item -> Cross References). Here you can store special numbers for the item (the vendor item Nos, Customer item no Nos, Barcodes, etc. ).
So make the field "Cross Ref. No" on subform visible and now you can enter here your "custom no". so you need no modifications on code. its all standard.

Regards

tompynation

Cause this new Item No. will be off Length 35... or can i addapt this easy in the Item Cross Reference Table?

tompynation

But what is the difference between:

1. Entering the Item No through normal NAV way in sales line
2. Entering the Item No through my "Custom No."

Custom No. - OnValidate()
IF "Custom No." <> '' THEN BEGIN
lv_Item.SETCURRENTKEY("Custom No.");
lv_Item.SETRANGE("Custom No.","Custom No.");
IF lv_Item.FINDFIRST THEN BEGIN
VALIDATE("No.",lv_Item."No.");
END;
END;

Cause when i insert with the "Custom No." the correct "Item No." gets inserted into the "Sales Line" but the "Unit Price excl. VAT" does not get filled up.

Savatage

things i would check..
1) since you code is on the sales line table - check the form and see if there is any code on the "no." field?
2) once the "custom no" has been entered does it pop to the item fno. field & if so are you then tabing out of it?
3) what happens when you enter a quantity, does the price appear?
4) on release of the order do you see any differences?

tompynation

Savatage wrote:

things i would check..
1) since you code is on the sales line table - check the form and see if there is any code on the "no." field?
2) once the "custom no" has been entered does it pop to the item fno. field & if so are you then tabing out of it?
3) what happens when you enter a quantity, does the price appear?
4) on release of the order do you see any differences?

1: On the form there is no code for No. field...
2: It does not focus the "No." field but it does get filled up
3: No
4: No

Well, it is not this important anymore cause i'm not going to use this anymore...
Just found it pretty strange cause when i follow with debugger the exact same things are executed

Why we are not using the Cross References: Simple, because we are but that cant be with my Custom No.

We introduced this custom no cause the client wants his item code to be 35... But for the rest the functionallity should all remain the same.

So there was the option to:
1: Change the lenght of the Item No field ==> which is verry bad idea
2: Work with a custom no and add this custom no every were the item no is displayed and fill up the item no through the
custom no

Savatage

or 3) simplty change the AltSearchField property of the item."No." field to your new "custom field"

David_Singleton

tompynation wrote:

...

Now when i open the Sales Form and try to insert a "Sidec No." i get the error that:
The field below must be included inside the table's primary key.
Field: Sidec No.
Table: Item

Why is this error occuring?

This is a bug that was introduced into Navision Financials version 1.1 it was introduced as a part of the upgrade tool kit from DOS to windows, I reported it first in 1996, so lets hope they have fixed it now by NAV 2009.

Anyway at a rough guess, I would imagine that in the sales line table (37) you have a relation from field "Sidec No." to Item, probably somethign like Item."Sidec No." so in effect the field links to a field that is a part of a secondary key int he Item table. Navision wants to look up on the Primary key, so it is asking you to make the field Sidec No. a part of the Primary key Warning DO NOT DO THIS. the only way to fix this error is to create a new table called Sidec, and map it to the field. But simply removing the relationship will fix that error.

Anyway It has been my contention for some time now, that its very unlikely for someone to want to upgrade from Navision DOS version to NAV 5.00, and if so they could upgrade to Navision 1.1 first, so really can we fix this finally.

David_Singleton

By the way, on a test Database, try to rename an Item, see if it gives you an error message.