search

microsoft / Power-Fx

Power Fx low-code programming language
MIT License
3.19k stars 321 forks source link

Accessing child collections from record scope #2535

Open MarkMpn opened 2 months ago

MarkMpn commented 2 months ago

I'm encountering an error when I try to reference a child collection within a table formula. I can reproduce it in pac cli, canvas apps and my own application consuming this library, so it's quite possibly my misunderstanding of the language but any advice would be appreciated.

I can successfully get the number of activities associated with a single contact using:

CountRows(First(Contacts).Activities)

However, if I try to replicate that within a collection of contacts, say all those with the first name "Mark":

Sum(Filter(Contacts, 'First Name' = "Mark"), CountRows(ThisRecord.Activities))

I get the errors:

Error 65-76: The specified column is not accessible in this context.
Error 45-77: The function 'CountRows' has some invalid arguments.

I understand that this is potentially an inefficient formula as it would require the CountRows to be executed for each individual contact, but as far as I can see the ThisRecord.Activities should be in scope at the point it's being used?

anderson-joyle commented 1 month ago

This is the expected behavior and has been restricted due to performance concerns. Can you share more about your scenario and what you are trying to achieve?

MarkMpn commented 1 week ago

I have a manually selected collection of records and I want to be able to work with the children of those records. In my case I have a collection SelectedRecords that is provided by the host app, and I want to be able to sort or filter those records in various ways including references to their child records, eg find the contact within that collection with the most activities.

Although this could lead to performance problems, in the case of a small collection this shouldn’t cause issues. I feel a warning would be better than an error here to allow this to work consistently, with the responsibility on the developer to heed the warning and rewrite the formula in some other way if possible but without blocking the functionality and accepting the possible performance penalty if that is what the developer requires.