Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Lookup and Sum field t...
Small and medium business | Business Central, N...
Suggested answer

Lookup and Sum field to Time Sheet Detail returns nothing

(0) ShareShare
ReportReport
Posted on by Hagru 155
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
  • Hagru Profile Picture
    Hagru 155 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    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.
  • Alexander Drogin Profile Picture
    Alexander Drogin 164 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    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.
  • Hagru Profile Picture
    Hagru 155 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    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.
  • Alexander Drogin Profile Picture
    Alexander Drogin 164 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    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.
  • Hagru Profile Picture
    Hagru 155 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    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.
  • Suggested answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 6,300 Moderator on at
    Lookup and Sum field to Time Sheet Detail returns nothing
  • Ramiz Profile Picture
    Ramiz 461 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    Hi,

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

    TimeSheetLine.Calcfields(LineDate)
  • xgaronnat Profile Picture
    xgaronnat 35 on at
    Lookup and Sum field to Time Sheet Detail returns nothing
    Hello,
    Did you use a CalcFields in order to get the value ? 
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,759 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,468 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans