Looking Up Temporary Tables

Even though I promised a different topic with my first blog, this one is something which I have already finished - just need to publish it.

Have you ever tampered around with the OnLookup Trigger to run a lookup to a temporary table?

This is not a very difficult task and looks something like the following;

This code is short and nice and works for both - the Classic Client as well as the Role Tailored Client.
Anyway this is not really worth blogging about it - if this would already be the complete story.

We all know how a lookup looks in the Classic Client - if not - here we have a nice example:


In the Role Tailored Client this code works the same way. And the system also opens up the lookup form.

But shouldn't the lookup in the Role Tailored Client show as drop down instead of opening an additional form?
What might be the difference? What causes this different behaviour?
It is quite easy. The code in the OnLookup trigger versus TableRelation.
It does not make a difference for the Classic Client, but for the Role Tailored Client it does.
The OnLookup trigger code will raise a lookup form and the table relation runs as dropdown list.
Actually I find the dropdown list quite nice. It looks a kind of better than the lookup form which opens in front of the other forms.

This bothered me for some time and then I remembered the "SourceTableTemporary" property - which I in fact use quite often to build up dynamic forms.
But can this be used to create a lookup with a table relation?

Maybe, maybe not.

What we will try first is to add a TableRelation to the Value field. But we should not allow the system to validate or test the TableRelation directly. This would not work as the lookup table does not contain any values to test against. Remember? It is temporary!

So the Value field properties now look like this:

The LookupForm for the "Lookup Table Buffer" SourceTableTemporary property has been set to "Yes".
All we would need to do now is to fill the table when running the LookUp in the OnOpenForm/OnOpenPage trigger.

So we move the code from the "OnLookup" trigger of the Value field in the table to the OnOpenPage trigger of the lookup form but remove the tempRec prefix from the code.
Oh - by the way - before somebody is asking: Yes you can define parameters on this lookup. Just add a WHERE condition in the TableRelation and test for the filter in the OnOpenForm/OnOpenPage trigger.

So, now we save everything and run the lookup in the RTC. Yeah, it is working. That was the point where I was dancing in the office. Then I tried again. Only if it works more often than once you could consider it being a solution. But this should be no issue.

Yes, that is what I thought.
But ...

... I found a bug in NAV Sad another one. 

Okay what went wrong?

Even though the SourceTableTemporary property has been set to Yes, Navision runs the lookup on the real table instead of a temporary!
This means that the lookup will fail with an error message "record already exists" on the second lookup.

The question now is if that is a bug or a "bug by design" (the second one has been invented by Microsoft for everything they did not consider at design time).

In the end it does not matter. We have a workaround.
We just need to incorporate the same programming techniques which we have been using before the "SourceTableTemporary" property has been implemented by Microsoft.

Therefore we need the tempRec variable back implemented in the globals. And this is the code - for the new NAV guys and the old which already forgot how that worked - years ago!

Now we have a lookup to a temporary table working without any code in the OnLookup trigger of the field. I'm not sure if you will ever need that , but who knows ...


Comment List