web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to create an AOT q...
Finance | Project Operations, Human Resources, ...
Answered

How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)

(1) ShareShare
ReportReport
Posted on by b_radlyjames 3,157
I have been working on creating an AOT query (For a Tile) to show a count of 'Expired' party relationships. (DirPartyRelationship)
All of my attempts, however, seem to have been thwarted by the ValidTimeState setting of the DIrPartyRelationship table which forces some ranges on the 'ValidTo' and 'ValidFrom' fields.
 
Is there a way to using an AOT query to get only Expired relationships?
 
 
I have the same question (0)
  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at
    HI, You can write range in init method of query. Check 'ReturnTableListPage_Expired' AOT range.
  • Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at
    Also, you can try adding range on ValidTo field is less than DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()).
     
    I think no need to validate ValidFrom as ValidFrom cannot be greater than ValidTo, so if ValidTo is less than today then its a expired record.
  • Suggested answer
    GirishS Profile Picture
    GirishS 27,833 Moderator on at
    Hi radlyjames,
     
    You can add a "ValidTo" field as range and in the value, property set the below one.
    (lessThanUtcNow()).
    It will work.
     
    Thanks,
    Girish S.
  • b_radlyjames Profile Picture
    b_radlyjames 3,157 on at
    GirishS,
    Sadly, you can simply add a range on 'ValidTo'.  The query automatically places a range on 'ValidTo' and 'ValidFrom' (Due to the ValidTimeState on the table) that can't be modified or removed in any way (That I could find).
    Therefore, it doesn't matter what range you attempt, it will always fail as it can't work right with the validTimeState range.
     
    Mohit,
    I will check the 'ReturnTableListPage_Expired'. Thanks!

    UPDATE:
    To give you an idea, if you get the SQL statement from the Query object, you will see the following:
    (T3.VALIDTO<?)) AND ((T3.VALIDFROM<=?) AND (T3.VALIDTO>=?)))
    The first one there is from my range on the AOT Query.  The second bit is from the valid time state setting.  It's forcing in that range.  So if you attempt any other ranges it will simply break the query.
     
    Brad
  • GirishS Profile Picture
    GirishS 27,833 Moderator on at
    You can refer to the HcmWorkerListPage_PastEmployees query where the below said is implemented.
    This query will return all the workers who's valid to field of HcmEmployment table is lesser then today, which is expired record.
    Query will have PastEmployment datasource which will have range added to ValidTo which will return expired records and then it will have ActiveFutureEmployment datasoruce which will have range added to ValidTo which will return active records.
    Both will be joined with not exists join.
     
    Thanks,
    Girish S.
  • b_radlyjames Profile Picture
    b_radlyjames 3,157 on at
    Girish,
     
    Thank you!  I will look at that.
     
    Brad
  • Verified answer
    b_radlyjames Profile Picture
    b_radlyjames 3,157 on at
    After some trial and error I found how to do this:
     
    1. You CANNOT use composite queries.  Not sure why but it doesn't work.
    2. In the 'Init' method of the 'Simple Query' put this: this.query().validTimeStateDateTimeRange(DateTimeUtil::minValue(), DateTimeUtil::maxValue());
    3. This will free up the 'Valid From' and 'Valid To' ranges for you to use the query date range functions such as '(lessThanUtcNow())'.
     
  • GirishS Profile Picture
    GirishS 27,833 Moderator on at
    Yes, you can refer to the query I send previously. They are setting the min and max date range to validTimeStateRange on the init method of the query.
     
    Thanks,
    Girish S.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 517 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 440

#3
Adis Profile Picture

Adis 266 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans