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 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 ...
this is the first time that I try to blog - ever in my life time. So please be tolerant if you find some weird works or unclear phrases.Some of you might know me - I'm member at DUG since the year 2000 and doing NAV since 1999.
Now let's start the story.
Many times NAV developers face the request from the customer to show the total amount of all lines in document forms.And there are a lot of suggestions running around including using sendkeys functions to update the page when showing the document amount in the "General" FastTab.To show the total line amount there is one of the options - even though not the best one - at least in my opinion - and that is what I'm sharing here.
How to do it otherwise?
There are two additional approaches to this issue.Approach 1: Extend the SubPage (ListPart)
Just above the repeater we will ad a fixed layout group with four field controls, the first two will not have a source expression. The other ones will have the caption and the total amount as source expression.
Of course we do also need to define the TotalLineAmount variable as decimal in the globals.Now as we do have our stage, we can start the play.
One more consideration: When just calculating a normal flowfield we do have the problem - and that is the biggest issue for displaying the correct total - the total is updated only when the current line has been written to the database.So for overcoming this issue we use a small trick:We create a new function in the page called CalcTotalLineAmount - or even better we could do that in the table so we can call it from everywhere.This function will return a decimal value representing the total of the document:
As you can see we are excluding the current line number from the filter so after calling the CALCSUMS we are getting the total amount of all "other" lines. Then we add the line amount of the current line manually to the result.This way we make sure to receive the correct line amount for the current record and not from the database.
All we do need to do now is to call this function, most probaly at different places.One nice place to call this is the "OnAfterGetRecord" trigger.Other nice places would be the OnValidate triggers in the form which will have impact on the amount of the lines.Usually these are fields like quantity, unit price, discount - and of course the amount field itself;We just place the following code into the trigger:
This should be it
But we have been talking about a second approach - so here we go:
Approach 2: Utilize the fact box.
The fact box already shows some information for the current line, so it would just be a small extension to also show the document amount.
All we need is one more control in the page 9087 for the sales document as an example.We where clever in the previous approach when we created the CalcTotalLineAmount function in the table rather than in the form. This way we can use the function directly as the source expression for the new field in the fact box:
That was actually it, just run the sales order form - or any other form using this Sales Line Factbox - and see the result:
But of course there is no rose without a thorn. This one only updates the total amount when you leve the current line and go to the next or previous one.The next time I will blog about the opportunities to perform real "Inter-Page-Communication" on the Role-Tailored Client which allows the direct access to the master page from the sub page.