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.
Really inconvenient misalignment between QD results and IOM standards
(from Aras blog post re: using QD from method....) Parse the Result (of executing a QD from method): When you get the response back from the server, you can parse the results into any format you need. The resulting XML looks a bit like your typical AML data structure. However, it has some important differences to consider: The Item tag uses an "alias" attribute instead of "type". Why? How can this be overridden to comply with the rest of the IOM? The "alias" attribute may or may not match an ItemType name. You can explicitly set the alias in the Query Builder GUI. The Item tag does not include the typical "id", "typeId", or "keyed_name" attributes. Why? How can this be overridden to comply with the rest of the IOM? Item properties contain the id of the referenced item. Related items appear in the "Relationships" tag on the relationship item - not in the "related_item" tag. Why? How can this be overridden to comply with the rest of the IOM?
MAC Policy Issue in ARAS 31 – Need Help with Access Restrictions
Hi all, I’m working on implementing access restrictions for the a Itemtype in ARAS 31. There are around 46 records, and I want to group them into 5 categories. Each category will be assigned to specific identities, allowing controlled access based on the record's classification. To achieve this, I started configuring MAC policies. However, I’ve run into a few issues: The fields I need are disabled in the MAC Policy Condition Editor. User-defined properties are not accessible in the condition editor either. Has anyone faced similar challenges or found a workaround for this? Any help or guidance would be greatly appreciated! Thanks, Salini
How to use a query to get distinct values from a item table
Community, I have created a staging table capability that will be used to generate innovator eBOMs from Oracle manufacturing BOMs. My challenge is working with 1.7 million rows of data in SQL Server. I know I could create a SQL procedure to get the unique (distinct) oracle Assembly IDs but I would like to use a Query Definition. In the data model below, what is circled in RED is the Oracle Data. What is circled in Blue is where I am separating a 1) [multi-Division, multi-Ora Org, with non distinct Assemblies (by Ora Org)] : stg_Oracle_MfgItem_MfgItem INTO 2) [single-Division, multi-Ora Org, Distinct Primary Assemblies] stg_eBOM_PartBOM_Primary AND 3) [single-Division, multi-Ora Org, Other Assemblies (without Primary PartBOM)] stg_eBOM_PartBOM_Other The goal is as follows: 2) stg_eBOM_PartBOM_Primary will be used to generate the innovator [Part BOM] relationships 3) stg_eBOM_PartBOM_Other will be compared to stg_eBOM_PartBOM_Primary to derive Substitutes So I would like to use a Query Definition to gather all the Distinct Oracle Assembly IDs so I can populate the stg_eBOM_PartBOM_Primary table. Thanks in advance for your time and insight. ScottHow do I set a where statement on a Query Definition against the current date the query is run?
I want to only have it pull a specific relationship (Part AML) if a custom date field "Valid To" is greater than the current date. I am able to create the Where that compares it against a specific date, but this query definition is pulled by another process automatically and I don't want to have to update the Query Definition every day with the current date for whoever ends up running it that day. I currently have: [Valid to] > '2021-11-29T00:00:00' I want something like (this doesn't work) [Valid to] > current_date()Query for Items related to the current user's Identity?
Looking to build a TGV for our users that basically looks for certain ItemTypes that they are either a creator, owner, or manager of. Trouble I'm running into is how to set the initial Query condition? I tried setting a Where condition of Current = Name, but that didn't work. Do get results expected when I set a parameter and then execute the query using Name LIKE $first_name... I will be launching from a method, so I presume that could merge it into the startConditionProviderParam = 'startConditionProvider=ItemDefault({"id":"id"})'; segment, but I was hoping there was a way to do this from the Query itself. Has anyone done something similar?
Query definition - Same fields added multiple times, causing issues with graph view
Intermittent issue Sometimes, the properties that I add 'disappear' when the query is saved. I've previously made the mistake of going back and adding the properties again. It is not always immediately apparent in the query builder that the properties have been added multiple times. A graph view based on the query does not properly generate. When the properties are correctly displayed for the query definition (sometimes after re-login, sometimes after restarting Chrome browser), I can see that there are duplicate properties. It would be nice if error proofing were built in, so that this situation would not occur. I'd love to hear about any other ways to steer clear of this issue.
Aras 12.0 effectivity with OR condition
Hello, I'm using effectivity in tree grid views in Aras 12. One use case I had was to have effectivity on different levels. So that the top level of a structure has certain effectivity configurations and lower levels have their own that refer to the top level. But in order to do this I think I would need to have an OR condition in my effectivity criteria. I have been able to programatically set the "EffectivityCriteriaDefinition" parameter of the query definition to filter by 1 option for a variable by setting the parameter like this: <eq> <variable id="item_id_model" /> <named-constant id="item_id_z5" /> </eq> but when I try to use this type of OR value for the parameter the tree grid fails to render: <or> <eq> <variable id="item_id_model" /> <named-constant id="item_id_z5" /> </eq> <eq> <variable id="item_id_unit" /> <constant type="int">10</constant> </eq> </or> Is this a bug in the tree grid/query definition implementation of effectivity or am I approaching this the wrong way? Best Regards Rick
