Dynamics NAV How-To Program Lookups
Often in NAV development, we have times on pages when we need to do a lookup into a table, find a record, and return it to our original page. There are some super-easy ways to do this that come with constraints, and some less-easy ways to do this that are significantly more robust. I’ll try to talk about them, since I don’t see any good tutorials out there on “el Google”. (You may believe that “el Google” is Spanish for “the Google”, but that’s incorrect. It’s actually Spanish for “the chupacabra”; I use some unusual methods for web searches.)
Setting up a Lookup into a Table: Easy Way
The first super-easy way to set up a lookup into a table comes when you have a table field that relates to another record. If you set the TableRelation property on a table field, and the related table has a LookupPageID property assigned, and you then add the field to a page, NAV will automatically give you a drop-down box showing the records from the table. (To customize the drop-down box, see this blog entry, written by ArcherPoint’s most handsome developer.) The drop-down box looks like this:
(That’s a picture from NAV 2013 that I already used in another blog entry, because I’m lazy.)
If you have a page field that’s NOT actually related to a table field, there’s an easy to do a lookup on that, too. All you have to do is set the TableRelation property of the field to the table you need a lookup for, and NAV will automatically give you the drop-down box lookup, just like if you’d set TableRelation on a table field.
(Not only does setting the TableRelation property work on the newfangled versions of NAV with pages, but it will even work with forms in the NAV legacy client; at least, I believe it does as far back as I recall.)
Setting a Lookup into a Table: A Bit More Complex, Using a Dynamic Filter
All of that is great when you have a simple, straightforward relationship. But sometimes things are more complex, like when you want to dynamically filter your lookup somehow. That’s when you have to write some actual code.
Let’s say you had customization request for the Sales Order page. If the Sell-to Customer No. begins with “0”, you need to do a lookup into the Item table on items beginning with “1.” If the Sell-to Customer No. begins with any other character, you need to do a lookup into the Resource table where the Resource begins with “M.” Whatever the chosen record is, you’re going to put the primary key value into a variable called LookupExperiment.
To pull this off, we start by creating a global code variable called “LookupExperiment” and we add it to the Sales Order page. It looks like this:
And to make our lookup happen, we add some local variables and code to the OnLookup trigger for the LookupExperiment field. The local variables are as follows:
| Name | Data Type | Subtype |
|---|---|---|
| ItemList | Page | Item List |
| ResourceList | Page | Resource List |
| Item | Record | Item |
| Resource | Record | Resource |
Lookup Experiment – OnLookup(VAR Text : Text) : Boolean
Next, we add the following code to the OnLookup trigger:
IF COPYSTR(“Sell-To Customer No.”,1,1) = ‘0’ THEN BEGIN
Item.RESET;
Item.SETFILTER(“No.”,‘1*‘);
CLEAR(ItemList);
ItemList.SETRECORD(Item);
ItemList.SETTABLEVIEW(Item);
ItemList.LOOKUPMODE(TRUE);
IF ItemList.RUNMODAL = ACTION::LookupOK THEN BEGIN
ItemList.GETRECORD(Item);
LookupExperiment := Item.”No.”;
END ELSE BEGIN
LookupExperiment := ‘ITEM NOT FOUND‘;
END;
END ELSE BEGIN
Resource.RESET;
Resource.SETFILTER(“No.”,’M*’);
CLEAR(ResourceList);
ResourceList.SETRECORD(Resource);
ResourceList.SETTABLEVIEW(Resource);
ResourceList.LOOKUPMODE(TRUE);
IF ResourceList.RUNMODAL = ACTION::LookupOK THEN BEGIN
ResourceList.GETRECORD(Resource);
LookupExperiment := Resource.”No.”;
END ELSE BEGIN
LookupExperiment := ‘RESOURCE NOT FOUND‘;
END;
END;
We use COPYSTR to evaluate the value in Sell-to Customer No. Based on that, we set our filter on an Item or Resource record according to our needs. After that, we send the record to the appropriate list page using SETRECORD, and we make sure the list page uses the same view as the record variable by using SETTABLEVIEW. (If you leave out the SETTABLEVIEW, no filters will be shown on the list page.) We make sure the list page is primed for a lookup by calling LOOKUPMODE and passing a value of TRUE. Doing the IF [list page].RUNMODAL = ACTION::LookupOK tells NAV to run the page and look at the results. If the user hits OK, then the returned value from RUNMODAL is equal to Action::LookupOK. (Action is a system-level Option field that we use to evaluate the result from running the page in LOOKUPMODE.)
Once the user has chosen a record, we use GETRECORD to set our record variable to the chosen record from the list page. We can then set our LookupExperiment variable to the primary key value from the record. And, if the user hits the Cancel button on the list page, we set LookupExperiment to give an error message.
That’s really all there is to it. Note that if you’re using the NAV legacy client to try to work on NAV 2009 or previous versions, you basically do the same thing, just with forms instead of pages. Good luck coding!
For more information on programming lookups in tables NAV, and other NAV-related programming topics, please visit our resource center today.
SPECIAL STREET FIGHTER UPDATE: It took me a while to get the hang of it, but I learned that I need to block more and do fewer dragon punches on wakeup to achieve victory in Street Fighter V. Once I got those two things to click and started to get the rhythm of a setup for my critical art, I went on a 14-win streak and pulled myself from bronze rank up to silver. Perseverance is the key. (Now if I could only make the time to play even more . . . maybe someday I’ll achieve platinum rank.)
For more step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV, see our collection of How-To blogs.