Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / AOT table view range f...
Finance | Project Operations, Human Resources, ...
Answered

AOT table view range for date not working

(1) ShareShare
ReportReport
Posted on by CU29041349-0 353
I created a view to get me from HcmPosition to DirPersonName. So the chain looks like: HcmPosition > HcmPositionWorkerAssignment > HcmWorker > DirPersonName. I have ranges defined for HcmPositionWorkerAssignment and DirPersonName to handle the ValidFrom and ValidTo fields. I've entered the range value to 'DirPersonName.ValidFrom <= currentDate(), and similarly for ValidTo. When I execute the view in SSMS, it does appear the rows returned are unique and honors the valid date ranges. 
 
I use this view on a form control as the lookup. When I view the lookup on the control, duplicates are appearing.  
  • CU29041349-0 Profile Picture
    CU29041349-0 353 on at
    AOT table view range for date not working
    Thank you very much everyone, and apologies for the delayed response. 
    Yes, I went the route suggested by Martin of removing all the ranges from the view and filtering in my query. 

    Please note I constantly have issues marking answers. If you find the answer indicated not marked, please mark on my behalf is you're able to. Thanks!
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    AOT table view range for date not working
    Note that Layan answer is over-complicated. There is no need to compose the range value as a SQL string, you can simply use something like this: 
    qbds.addRange(fieldNum(YourView, ValidFrom)).value(queryRange(null, currentDate));
    Also, if the view is designed as date-effective, you need another approach. You can utilize validTimeStateDateRange() of the Query class.
  • Waed Ayyad Profile Picture
    Waed Ayyad 6,813 Super User 2024 Season 2 on at
    AOT table view range for date not working
    Hi,

    Is your issue resolved?  If yes, mark the answers that helped you as verified.

    Thanks
    Waed Ayyad
  • Layan Jwei Profile Picture
    Layan Jwei 7,440 Super User 2024 Season 2 on at
    AOT table view range for date not working
    Hi,


    I agree with Martin, because The "View" is built like T-SQL code, so you can't call x++ methods in it.

    you will need to add this range by code

    Maybe you could do sth like this (you can change this code to match what you need): 
    SysTableLookup        sysTableLookup        = SysTableLookup::newParameters(tablenum(View), this);
Query                 query                 = new Query();
QueryBuildDataSource  queryBuildDataSource  = query.addDataSource(tableNum(View));

queryBuildDataSource.name('View'); 
queryBuildDataSource.addRange(fieldNum(View, ValidFrom)).value
            (  
                strFmt(
                         '(%1.%2 < %3)',
                          queryBuildDataSource.name(),
                          fieldStr(Table, ValidFrom),
                          DateTimeUtil::getSystemDateTime()
                      )
            );

sysTableLookup.addLookupfield(fieldnum(View, Field));
//add any field needed
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();
     
    Thanks,
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    AOT table view range for date not working
    Using (currentDate()) in the view range it wrong. This is an X++ function that can't be called from the view definition in SQL. The function gets executed just once, when the view is synchronized with database, therefore the SQL view will contain the time of synchronization, not the current date. Look at your view definition in SSMS; you'll see the hard-coded value there.
     
    Here is the solution: don't put the range to the view; apply it when you're fetching data from the view by a query.
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 6,813 Super User 2024 Season 2 on at
    AOT table view range for date not working
    Hi,
     
    Can you share your view ranges? How you added the date ranges, did you define the ranges to get the active records only? Try to create a query and add the ranges to it then add the new Query to your view.
     
    Thanks,
    Waed Ayyad

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 Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,605 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans