cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarceloBarroso
Helper I
Helper I

Sum field through Select query in CDS/List records

Hi all.

 

Thanks in advance for your support.

 

I'm not sure whether this would be possible, but certainly it would help a lot. I want to sum the values in a table field (CDS), similar to the SQL instruction "Select SUM(field_name) as total_amount from <table_name>" (returning the total amount in the <total_amount> field).

 

I tried something like this using the LIST RECORDS action, but it didn't work (see image). Does anyone know a way to do that??

image.png

 

This is the error received:

Term 'sum(clth_gross_billable_value) as valor_tributavel_total, sum(clth_invoice_item_value) as valor_faturavel_total' is not valid in a $select or $expand expression.

 

 

 

(PS: The solution of (1) initializing a float variable and (2) adding the values to the variable within a for_each action is what I am doing right now, and it works...    if it was possible to do what I wanted, it would be much better tough.)

 

Thank you very much.

Best regards.

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @MarceloBarroso ,

Select is a query option from OData service, unlike the Select instruction in SQL Server. it allows the clients to requests a limited set of properties for each entity, see System Query Option $select .

Hence, your requirement can't be achieved. The workaround for now is, as you said, initializing a variable and adding the values to the variable within a Apply_to_each action to sum a column. There is no better method for that in flow at present.

Hope this helps.

Sik

View solution in original post

4 REPLIES 4
v-siky-msft
Community Support
Community Support

Hi @MarceloBarroso ,

Select is a query option from OData service, unlike the Select instruction in SQL Server. it allows the clients to requests a limited set of properties for each entity, see System Query Option $select .

Hence, your requirement can't be achieved. The workaround for now is, as you said, initializing a variable and adding the values to the variable within a Apply_to_each action to sum a column. There is no better method for that in flow at present.

Hope this helps.

Sik

View solution in original post

Hi @v-siky-msft 

 

Thank you very much that. I thought it wouldn't be possible, but decided to ask anyway. At least we know Flow has a large road to improve yet, we may expect a lot for the years ahead.

 

Thank you for the reference on oData query, it will be much helpful.

 

 

Best wishes.

Helpful
Resolver III
Resolver III

Hey @MarceloBarroso, you could use FetchXml with aggregate values.

Something like this:  Using FetchXML with List records in CDS current environment 

Using this approach I saw crazy performance improvement compared to nested loops.

 

Thank you, @Helpful . I'll try FetchXML to do this...   it seems it's going to help me in lots of things.

 

Best regards.

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (38,375)