Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
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,089
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?
 
 
  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    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.
  • Verified answer
    b_radlyjames Profile Picture
    b_radlyjames 3,089 on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    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())'.
     
  • b_radlyjames Profile Picture
    b_radlyjames 3,089 on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    Girish,
     
    Thank you!  I will look at that.
     
    Brad
  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    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,089 on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    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
  • Suggested answer
    GirishS Profile Picture
    GirishS 27,825 Moderator on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    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.
  • Mohit Rampal Profile Picture
    Mohit Rampal 12,558 Moderator on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    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
    Mohit Rampal Profile Picture
    Mohit Rampal 12,558 Moderator on at
    How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)
    HI, You can write range in init method of query. Check 'ReturnTableListPage_Expired' AOT range.

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

News and Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,206 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,968 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans