Small and medium business | Business Central, N...

Lookup and Sum field to Time Sheet Detail returns nothing

(0) Share

Report

Posted on by Hagru

311

Hello!

We are using the standard time sheets in Business Central, and we figured it would be nice to have the date for the line available. I understand the date of the time sheet line is stored in the Time Sheel Detail table, and I can see the date for each line in there.

My end goal is to show the date in the invoice sent to customers, but to get there, I thought I could use a lookup field in my Time Sheet Line table to get the date for each line, and then work from there.

Already here is where I am facing issues, and I don't quite understand why. I am quite confident with lookup fields, but this one is giving me a hard time. This is what my field looks like in my Time Sheet Line table extension:

field(50103; LineDate; Date)

{

FieldClass = FlowField;

CalcFormula = Lookup("Time Sheet Detail".Date WHERE("Time Sheet No." = FIELD("Time Sheet No."),

"Time Sheet Line No." = FIELD("Line No.")));

}

I see similar ones are already used in the Time Sheet Line table, for example for "Total Quantity". It is a sum field, but it should work the same way. I even tried to copy the "Total Quantity" field into my table extension and change the name and ID of it to see if it would give me anything in return, but no - nothing.

So my LineDate field returns (Blank), and my copy of the "Total Quantity" field returns 0. The actual "Total Quantity" field returns 6,5, and I can see there are dates connected to the Time Sheet Details line.

How can I make a lookup that gets the date from the Time Sheet Details table?

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (8)

Answers (0)

xgaronnat 40 on at

Like (0)

Report

Hello,

Did you use a CalcFields in order to get the value ?

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/record/record-calcfields-method

Was this reply helpful? Yes No
Ramiz 597 on at

Like (0)

Report

Hi,

In your report are you using Calcfields for the field that you created?

TimeSheetLine.Calcfields(LineDate)

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,612 Super User 2025 Season 2 on at

Like (1)

Report

Go through it: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-calcfields-calcsums-fielderror-fieldname-init-testfield-and-validate-methods

https://yzhums.com/5802/

Was this reply helpful? Yes No
Hagru 311 on at

Like (0)

Report

No, I was using CalcFormula, as shown in the code I included in the post. First of all I wanted to be able to see the value in the Page Inspect, but they returned blank, even when copying another field that got data from the same table. There shouldn't be any calculation needed, I just want to get the Date from the Time Sheet Detail where the line is the same as the line I am looking at. It should be fairly straight forward with a lookup I thought, but apparently that's not the case.

Was this reply helpful? Yes No
Alexander Drogin 262 on at

Like (1)

Report

It is indeed quite straightforward, but your question is missing one important detail, hence some confusion in the answers I believe. It all depends on the way you are getting the value. If it is a record variable in AL code, you need to call CalcFields, as suggested. If the field is displayed in a page, nothing else is required, it will be autocalculated. But the key point is - if it is a page field. Table fields that are not linked to page controls will not be calculated and show blank values.

Was this reply helpful? Yes No
Hagru 311 on at

Like (0)

Report

Well I did specify it was a table extension for the Time Sheet Line table. Table extensions is usually where I add the lookups, as the field I am using to look up the value is in that table, ref the Time Sheet No. and Line No., which I imagined would populate the field using the values on the record.

All I have done is quite literally the code I added, within a table extension, and this has always worked for me before, which is why I was so confused when the fields were blank in my page inspect tab.

I thought the lookup worked in a way where it looked at the record, took the field values specified and checked if those were similar to the values in the other fields specified for the other table. In this case, taking the Time Sheet No. and Line No. from the Time Sheet Line record, looking for those values in the Time Sheet No. and Time Sheet Line No. in the Time Sheet Detail table, and return the Date to the LineDate field I created. It shouldn't require any page actions, as everything it needs is already in the record.

There are also multiple CalcFormula fields like this in the base Time Sheet Line table, and when copying them into the table extension and changing the name and ID of them, they also return blank, hence the confusion.

My first goal was to just get the value into the table, maybe show it in the Time Sheet Line list, and then from there add it to the report. However it sort of stopped when I couldn't extract the value from the Time Sheet Detail table.

Was this reply helpful? Yes No
Alexander Drogin 262 on at

Like (2)

Report

Yes, I got it that you added a field in a table extension. Let me maybe try to be more specific. Just declaring a flowfield with a respective calcformula in a table or a table extension is not enough to receive a value in this field - the value must be calculated, either explicitly in AL code, or implicitly by the BC runtime if the field is displayed in a page.

1. The first case is applicable when you want to use your field in a record variable. You get the record from the database with one of the functions like Get or Find, and the flowfield value is still empty. You need to call CalcFields(MyFlowField) to obtain the value. Alternatively, you can use AutoCalcFields to enable SmartSQL and make the Get/Find function calculate your flowfields. But as far as I understand, this is not your case. You actually want to see your flowfield in the page inspect view. This leads to the second option.

2. When the flowfield is displayed in a page (linked as a data source to a page control), then the runtime will calculate the field value when the page records are retrieved from the database. But it calculates only those fields that are part of the page, not every flowfield in the source table. So this is the part that is missing - is your field displayed in the page you are viewing? If it is not present in the page, then what you see is expected behaviour - field value will not be calculated.

Was this reply helpful? Yes No
Hagru 311 on at

Like (0)

Report

Okay that explains it then, because I am not showing the field in the page. I thought it would still fetch the value even if I didn't display it. But if that is the case then I will definitely take that with me for the next case!

Thank you for taking the time to explain it so well, it makes perfect sense.

Was this reply helpful? Yes No