Nested Gallery Sort

[jonfrost101]

I have a main gallery which is populated by records in a collection which is in turn populated by data held in SQL table

The collection is filtered based on criteria in one of the fields in this collection. This collection contains a key field.

Another collection is made at the same time based on another SQL table which has a key in common with the primary table. The relationship is a 1 to many between the primary and secondary table.

I have successfully added a galley as an item in the main gallery linking the two with the common key. The secondary galley is then filtered to just one record, the record within the table with the highest key value from the matching secondary records (inthis case that is the most recent record of the many records)

All of this works and I can display data nicely in my gallery and its sub gallery.

My problem now is the user has asked can we sort by one of the fields displayed in the sub gallery, not a key field just a data field (contains a number of days something has been happening for, but could be anything really)

I have tried a number of ways but none have worked.

My closest and one that I think should work is by using a ForAll Loop on the main screen OnVisible property to loop through all the records in the main collection, finding the related related record I need from the secondary collection, getting the number field value I need and adding that as a new column in a third collection that just contains the fields I need from both matching records from the two tables. I can understand the logic for this but my code in PowerApps is not working, the records are made in the 3rd collection but the field I want just contains nulls. I have used As function to make sure the keys were recognised as coming from the two separate collections and making sure there was no ambiguity, but still no data in the field. If this worked then the data I need to sort by is in the main gallery's own columns and a standard sort by column will work.

Any help much appreciated.

Here is the code from the OnVisible property of the screen that contains the galleries

ClearCollect(colJAMMVoids,Filter('[dbo].[PROPERTY]',PropertyStatus = "Void"));
ClearCollect(colVoidDetails,'[dbo].[tblPropertyVoids]');

ClearCollect(colVoids,
ForAll(
colJAMMVoids As JV,

{
PropertyCode: JV.PropertyCode,
Address1: JV.Address1,
PropertyTypeID: JV.PropertyTypeID,
VoidDueDate: JV.VoidDueDate,
PDDisabledAdaption: JV.PDDisabledAdaption
NumberOfDaysVoid: First(
SortByColumns(
Filter(colVoidDetails As VD,VD.PropertyCode = JV.PropertyCode),"PropertyVoidsID",Descending)
).PropertyVoidsDays}
)
)

[Drrickryp]

@jonfrost101 

When you create the collection that provides the items for the subgallery, sort the filtered table at that time.  so if the sorting column is PropertyVoidsDays, the formula would be  

ClearCollect(
             colJAMMVoids,Sort(
                                Filter('[dbo].[PROPERTY]',PropertyStatus = "Void"
                                 ),PropertyVoidsDays
                            )
);

[jonfrost101]

Sorry @Drrickryp but that wont work as the field is not in the Property table, that's why I am doing the ForAll to try and add it!

[RandyHayes · Accepted solution]

@jonfrost101 

Set your Items property of the Gallery to the following:

With({_details:
          SortByColumns('[dbo].[tblPropertyVoids]',"PropertyVoidsID", Descending)
     },

    AddColumns(
        Filter('[dbo].[PROPERTY]',PropertyStatus = "Void") As JV,
        "NumberOfDaysVoid", LookUp(_details, PropertyCode=JV.PropertyCode, PropertyVoidsDays)
    )
)

This will give you a table of PROPERTY records where PropertyStatus is Void.  It will also include an added column called NumberOfDaysVoid that will contain the PropertyVoidDays value of the most recent record for that property.

I hope this is helpful for you.

[jonfrost101]

Hi @RandyHayes 

Your script for the gallery items selection has given me the exact same issue as my forall, but I like your code better!

I am getting the column "NumberOfDaysVoid" available to use in my gallery, but just as in my collection building it is an empty column.

As I have access to the main SQL source I have written a new view that does the work of joining the various tables needed there and then linking the powerapps screens to that new view, works a treat. It is probably a foible of the SQL connector, just not writing back a good enough SQL query to the DB to get good data back again. Offloading the code construction to source has worked, and is probably faster anyway.

Thanks for giving me this neater code, though, sure to use somewhere.

Jon  

[RandyHayes]

@jonfrost101 

I must have misunderstood your original issue then if you got the same results.  

But, a view is always a good way to get the data the way you need it.  Just be careful with performance from views.  You cannot update a view record from PowerApps, so this means that you have to update the original table values and then refresh your view.  This is a lot of overhead that you can avoid if possible.