Lookup Field Not Doing What I Want

Business Central/NAV Developers Forum
1

Hi

I am trying to get the Last Service Date from the Service Item Table to show on the Service Header Table.

There are no common fields to match to go straight between these tables, so as an intermediary step i put the Last Service Date on the Service Line Table (where Service Item No is common), and then looked up from Service Header Table to Service Line Table.

The field appears in both tables, but the date doesn’t show in the field, you have to drill down to get it.

I thought it might be from doing the double flow field, or from a one-to-many relationship but it worked perfectly with a different field I tested on.

Thanks!!!

2

are you trying to apply filter on the list table to get the Entries Which belongs to last service date ?

3

Yes I am. I have tried both with and without filters and the same thing happens (but less results show on drilling down when filters are applied).

4

hi

Zarah Jay-Smith

Reason for less result is you don’t have that field applied on entries…if you don’t have value in that field in table it will not display while applying filter.
if possible try to share screenshot what you wants to perform.

### thanks

5

sorry i’m not really following what you’re saying is the reason… what would you like to see a screenshot of?

6

Curious, which fields did it work with? I thought such kind of “nested” flowfields should not be calculated. I don’t have NAV 2009 at hand to test, but if you do the same in 2013, it fails when opening the page:

7

it worked with Service Item No

8

But Service Item No. is a field in Service Line, so it’s only one lookup from service header into service line. It works fine. What you want to do is a bit more complicated. When you make a flowfield a part of a formula for another flowfield, you must ensure correct order in which these two fields are calculated. Fields in subpage are populated after the header, so when the page is opened, “Last Service Date” in the header page retrieves blank value from the line, and only then flowfield in the line is calculated.

To make it work you’ll have to add both flowfields in Sales Header table.

  1. Service Item No. that will lookup value from service line.

Lookup(“Service Line”.“Service Item No.” WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.)))

  1. Last Service Date - this will be a lookup into Service Item table with “Service Item No.” as a filter:

Lookup(“Service Item”.“Last Service Date” WHERE (No.=FIELD(Service Item No.)))

But honestly, I would prefer writing several lines of C/AL code instead of such magic tricks. Lookup won’t give you any performance gain, but it complicates things and requires an extra field in Service Header table.

9

thank you! thank you! thank you!

i’m not so good with code… what would you recommend?

10

Hmm, this lookup seems to work perfectly well in 2013 R2, but not in 2009.

Anyway, here is the code you need. Create a function GetLastServiceDate and call it from OnAfterGetCurrRecord trigger in the Service Header form. You can declare a global variable, set it as a source for a textbox control and assign the return value to the variable:

Form - OnAfterGetCurrRecord()
LastServiceDate := GetLastServiceDate;

GetLastServiceDate() : Date
ServiceLine.SETRANGE(“Document Type”,“Document Type”);
ServiceLine.SETRANGE(“Document No.”,“No.”);
IF ServiceLine.FINDFIRST THEN
IF ServiceItem.GET(ServiceLine.“Service Item No.”) THEN
EXIT(ServiceItem.“Last Service Date”);

EXIT(0D);

ServiceLine and ServiceItem are local variables in GetLastServiceDate.

If you use RTC and need to see this field in the page, then you’ll have to place the function call into OnAfterGetRecord, since 2009 pages do not support OnAfterGetCurrRecord trigger.

Another thing to notice - this code will do what a lookup would, i.e. find the first line in the service order and return last service date for this item. While service order can have several lines with different items and different service dates.

11

in the service line there is only one service item, so relation 1:1. to the header there is a 1:n relation. so what you want won’t work. for e.g. a service order you could do the following:

you can use the new LastServiceDate field in the service line or service item line (flowfield, lookup).

then add following code to trigger onaftergetrecord in e.g. page service order:
ServiceItemLine.setrange(“Document Type”,“Document Type”);
ServiceItemLine.setrange(“Document No.”,“No.”);
ServiceItemLine.setfilter(LastServiceDate,’<>%1’,0D);
if ServiceItemLine.findfirst then begin
ServiceItemLine.calcfields(LastServiceDate);
LastServiceDate := ServiceItemLine.LastServiceDate;
end else
LastServiceDate := 0D;