Help writing a complex filter for a gallery

[Flyingfishnm]

Hi

I've been trying to write a filter query to achieve a result the following MySQL query produces:

Select a.docname, b.status, b.due_date
from parentTBL as a,
childTBL as b
where
a.id = b.parentid
and a.id in
(select c.parentid
from childTBL as c
where c.parentid = a.id)
order by b.due_date desc
limit 1

Any help much appreciated! i've been trying for the last day and half with no success! 🙄

cheers!

dj

[poweractivate]

In Power Apps, you can achieve the same result by using the Filter, LookUp, and Sort functions. Assuming you have a data source for parentTBL and childTBL, you can try this:

1. First, create a collection to store the filtered childTBL data:

ClearCollect(
    FilteredChildTBL,
    LookUp(parentTBL, id = parentid) // This filters childTBL rows where the parentid exists in parentTBL
)

2. Now, create a collection to store the result of your query:

ClearCollect( Result, AddColumns( FilteredChildTBL, "docname", LookUp(parentTBL, id = parentid).docname, "status", status, "due_date", due_date ) )

3. And then, sort the result collection by the due_date column in descending order and limit it to 1 item:

Sort(Result, due_date, Descending).First

This should give you a sort of equivalent result that's similar to your MySQL query in your Power Apps Canvas App.

You should perform Steps 1 & 2 in something like OnVisible of a screen or OnSelect of a button.

Step 3 belongs in something like the Items property of a Gallery for example.

Please note that depending on your data source and its connector, you might need to make adjustments to the field names and the exact syntax of the LookUp or Filter functions.

Additionally, note that if you have a large dataset, using ClearCollect might not be the most efficient way to achieve this. In that case, you might want to consider using delegation-capable functions or filtering the data on the server side, and writing the queries differently from above. Note that the default data row limit is 500, and it can be raised to 2000 here:

Note that using a Collection does not give any yellow triangle warning, but using a Collection is still subject to the data row limit in Power Apps. Note that the data row limit is not only for non-delegable formulas that give the yellow triangle warning, it is also for specific things like Collections.

The data row limit states to "Set how many rows are retrieved from server-based connections where delegation is not supported" - however note that the data row limit actually also applies to certain situations and formulas like ClearCollect, Collect and so forth, and is not only for the formulas that give a yellow triangle warning.

[Flyingfishnm]

Hi

thank you very much for your detailed suggestion. It never occurred to me to split the process over a number of steps, i was in the 'SQL one statement' mind set, 😄. I have question about step 1, the lookUp() function you have id = parentid... how does that work? theres no reference to the child table in the statement?