Forum Discussion

ben_pecot_cubic_com's avatar
ben_pecot_cubic_com
Ideator I
5 months ago

Top 1 or Max in Query Builder

 

It is possible to use a "Max" or "Top 1" function to get the latest/largest value in a field.  Whenever I add that function to the where section in Query Builder, it no longer allows me to save.

According to the latest documentation for the Query Definition it is possible, but those screenshots are from an older version of Innovator.

https://aras.com/wp-content/uploads/2025/06/Aras-Innovator-2025-Release-Query-Builder-Guide.pdf

I am attempting to get only the latest CE_ManufacturerPartDetail entry based on the latest Synchronization date.  It is not being used in a TGV so I can't limit the execution to one and use sorting to get the latest.

I can get past all of the errors with the following but it does not allow it to be saved.  The button is greyed out.

[Synchronization Date] = max([Synchronization Date])

This is on Aras Innovator version 22.

Any help would be appreciated.

Query Builder
Query Definitions

2 Replies

  • solana's avatar
    solana
    Creator I
    5 months ago

    I prefer to use SQL for queries like this, is that an option for you?

  • Kishor_Ramishte's avatar
    Kishor_Ramishte
    Ideator III
    14 days ago

    Hi ben_pecot_cubic_com​ ,

    It sounds like you need to fetch the latest CE_ManufacturerPartDetail entry by the most recent Synchronization date.

    Here is a potential solution:

    1. Apply sorting directly in the Query Definition.

    Here, I am applying Sorting for the PE_BomStructure QD on Quantity property

    2. Use the TGV to limit the number of records displayed.

    Set Max Visible Children On Expand as 1 for PE_BomStructure TGV

    Keep in mind that this configuration will initially load just the top record. The user will need to click "Show More" to see the rest.

     

    The result will look like below -

    Let me know if this works for you.