10
Good Evening,
I'm trying to aggregate a two option field with the following test FetchXML:
Share
Report
But get the following error:
Aggregate MAX or MIN is not supported for attribute of type bit.
Essentially I need to find if any contact has 'field1' set to 'Yes'. Is it possible to aggregate a two option field?
Wahaj, in your "alternated approach" why do you even need aggregation at all in the fetchXML for this? Is that really needed or just forgot to delete that out?
<fetch distinct='false' mapping='logical' aggregate='true' top='1'>
<entity name="contact">
<attribute name="field1"/>
<filter type="and">
<condition attribute="field1" operator="eq" value="1" />
</filter>
</entity>
</fetch>
To make it easier for other readers, If any of the answers helped, please mark it as verified.
(Select 'Yes' under 'Did this answer your question?')
Hi,
I believe you have to do multiple queries for each field and then implement your logic.
For example:
Otherwise, please look into Clofy's approach.
Hi Tom,
SQL is now supported(and MAX function) to query CDS data(now it is renamed to Dataverse), try the new preview feature and check whether it could work for you:
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query
https://powerobjects.com/dynamics-365/use-sql-to-query-data-from-cds-and-dynamics-365-ce/
Regards,
Clofly
Hi Wahaj,
Thanks for your response, what would be the best way to structure a query that was aggregating across multiple fields rather than just one?
Essentially I'm trying to create a query to determine whether any related contact has a field set to true for a number of fields, in SQL the equivalent query would be:
SELECT id, MAX(field1), MAX(field2), MAX(field3), MAX(field4) FROM contacts GROUP BY id WHERE ...
Hi,
Thank you for your query.
As you can see by the error it is not possible Aggregate on bit (2 optionset) fields.
However you can use DISTINCT operator.
Here is the example FetchXML:
All responses (
Answers (
Please note, distinct="true" added in the FechXML and it should return 2 records (at MAX).
An alternated approach to your scenario is:
For instance here is the example FetchXML:
If there are any results returned from above query, we will get to know if there are any records where field1 is set to true.
Here is example C# code for your reference:
string qry = @"
";
FetchExpression fetchExpression = new FetchExpression(qry);
var results = svc.RetrieveMultiple(fetchExpression);
if (results.Entities.Count==1)
{
// There are records where field1=true
return true;
}
Let me know if you need further clarification.
Best,
Wahaj
(if this helps, please mark it as verified)
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,552
Super User 2024 Season 2
#2
Martin Dráb
228,552
Most Valuable Professional
#3
nmaenpaa
101,148