Storing "Vendor Quantity" Data

Business Central/NAV Technical Forum
1

Hey guys,

So here is a dilemma we’re having. In our NAV setup, we have a field called “Quantity of Vendor Part Number”, which is essentially a lookup box and has no real data stored. What this does is such. Say we have part number ABC, and there are none on hand. But for “Vendor Part Number”, we have “XYZ” stored.

XYZ would then also be a part in our items list, and say that has a quantity on hand of 10. Therefore, if you pull up the item card for ABC, you’ll see 0 on hand, but 10 of vendor part number. This lets us know that our vendor has 10 of them.

Now, we set up a data migration using Scribe Insight a few months back. This requires actual fields of data to pull from, not just lookups. What I did for our initial load of parts from NAV into CRM was replicate that lookup in Scribe itself, so that when it put those parts in, it would fill in “Quantity of Vendor P/N” in CRM (which IS an actual data field that stores a decimal number).

The initial sync worked fine, but we came across a problem after the fact. When a sales order line is shipped in NAV (our Scribe system adds the orders to NAV which originate in CRM), our Scribe file goes and forces the item to re-sync itself, since the quantity will obviously be less after shipping the parts. This works for the “quantity on hand”, but NOT for quantity of vendor part number (well sort-of, let me explain)

Say we sell one of part XYZ. Its quantity will go down from 10 to 9, and CRM will show that. However, if we look up ABC in NAV, we’ll now see that it has 9 for quantity of vendor part number, since it’s pulling that data directly from the XYZ record. In CRM, however, ABC still has 10 in that box, since there would be no reason to update ABC if a customer purchased XYZ.

Now, what I did was create an additional NAV field for Vendor Quantity, so that this data can be stored normally and not a lookup. Why would this help? Well, recall how integration works - if something in NAV is updated (such as a part’s quantity…), it will trigger Scribe to go update it. So my thinking was that if we stored these values in an actual field instead of just using a form on the item table, this issue would fix itself (since Scribe would force a re-sync of XYZ, which would also force ABC to sync since ABC had a field changed)

So here’s what I need help with. How can we set it so that the data field in an Item record updates whenever the “parent record” is linked? When I look through this in my head, it’s a very complicated scenario because you can have 3 parts all with the same vendor part (we sell one part under several SKUs, in other words), so in that case if we sell the MAIN part, you’d have all 3 changing values (for example)

Here is what the NAV code is for that lookup field:

It’s only under “page” for Role-Tailored Client, you can’t see it in Classic…
http://i.imgur.com/rwBFESb.png

I actually don’t know how to view what that function does, I’m not too trained in NAV coding, sadly.

2

OK, I did some more digging and found where that function is.

It’s in the Item Variants table, as its own code (stored as xREC, if that matters). Here’s what it has:

vendorItemQty_fu() : Decimal
IF “Vendor Item No.” = ‘’ THEN EXIT(0);

ile.RESET;
ile.SETCURRENTKEY(“Item No.”);
ile.SETRANGE(“Item No.”,COPYSTR(“Vendor Item No.”,1,MAXSTRLEN(ile.“Item No.”)));
ile.CALCSUMS(Quantity);
EXIT(ile.Quantity);

childItemQty_fu() : Decimal
IF “Child Code” = ‘’ THEN EXIT(0);

ile.RESET;
ile.SETCURRENTKEY(“Item No.”);
ile.SETRANGE(“Item No.”,COPYSTR(“Child Code”,1,MAXSTRLEN(ile.“Item No.”)));
ile.CALCSUMS(Quantity);
EXIT(ile.Quantity);

For my new “Quantity of Vendor Part Number” field, I can’t just type “vendorItemQty_fu()” in the OnValidate and OnLookup, right? The problem is basically that I need it to change the “Quantity of Vendor Part Number” field on the OTHER part.

In other words, if the quantity of part ABC changes from 10 to 9, something on the page for part ABC needs to change a field on part XYZ. Does that make sense? If it was just a lookup I could use vendorItemQty_fu(), to at least store the values (but how would I force it to update all the records?), but I need it to store data in another record and I’m not sure how to do it.

It sounds easy in my head but I really don’t know what I’m doing. Anyone’s help would be appreciated.