Sum field through Select query in CDS/List records

(0) Share

Report

Posted on by MarceloBarroso

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??

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.

Categories:

Using connectors

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

v-siky-msft on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
MarceloBarroso 39 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Helpful 382 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
MarceloBarroso 39 on at

Like (0)

Report
Copy link

Link copied!

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

Best regards.

Was this reply helpful? Yes No