Sort Table 1 by newest available record in Table 2

[maxtarneberg]

Hi all,

I am attempting to sort Gallery items by a value found in a related table.

My primary goal is to sort the stocks in the gallery, with the least recently inspected stock appearing at the top, and the most recently inspected at the bottom. This will help prioritise workflow as you can imagine.

There can be many inspections to a stock.

So,

I would like to sort CashedStocks by the newest available record in CashedInspections

(I am aware that it should have been cached btw, but these things happen!)

Both are Collections

PK in CashedStocks is StockUniqueID

FK in CashedInspections is StockID 

I have attempted to use the "AddColumns" function, and this works to a certain extent. It is sorting the Stocks by date of Inspections, but not by most the recent inspection.

I am not sure how to add that layer of sorting to the formula

Any help greatly appreciated

SortByColumns(
    AddColumns(
        CashedStocks,
        "Date",
        LookUp(
            CashedInspections,
            Stock = StockUniqueID,
            Created
        )
    ),
    "Date",
    Ascending
)

Many Thanks

[v-bofeng-msft · Accepted solution]

Hi @maxtarneberg :

Do you want to sort CashedStocks by the newest available record in CashedInspections?

Could you tell me:

Is the value of the field "Created" the creation time recorded in CashedInspections?

Is the Stock in the formula the “StockID” you mentioned earlier?

Whether the two tables are mapped through the two fields "Stock" and "StockUniqueID"?

I assume the above assumption holds.

The key is to use the characteristics of the lookup function.

The LookUp function finds the first record in a table that satisfies a formula. 

I suggest you try this code.

SortByColumns(
    AddColumns(
        CashedStocks,
        "Date",
        LookUp(
            Sort(CashedInspections,Created,Descending),
            Stock = StockUniqueID
        ).Created  /* Sort records in CashedInspections by date */
    ),          /* The LookUp function finds the first record in a table that satisfies a formula. Since we have placed the newly created record to the front, the returned record is the most recently created*/         
    "Date",
    Descending
)

I think this link will help you a lot:

Filter, Search, and LookUp functions in Power Apps

Best Regards,

Bof

[maxtarneberg]

Hi @v-bofeng-msft ,

Thank you, your assumptions were correct, and all of the answers were 'Yes!'

Your code works perfectly, thank you.

Just to further improve on it and make it perfect for my needs, could the Stocks be sorted so that those with no inspection against them appear first, followed by those with the least recent inspection (which we have achieved), followed by alphabetically by "GROWER", and finally filtered/search by entries into TextSearchBox1 under columns "GROWER", "Variety", "CertificateNo".

I feel that it would need more add columns, but am unsure of how to write that syntax correctly.

I'm sure this is a useful exercise for any job based app that needs to prioritise jobs against customers etc.

Many Thanks for your ongoing support,

Max 

[maxtarneberg]

I have solved my own question with some tough perseverance.

When added to the Gallery Items property, this will sort records in the gallery by least recently created record in a related table, but gallery records with no related records in the other table appear first. It will also filter by 3 different columns in a search box on top of all this.

This is handy in prioritising work as stocks/customers/sites that have least recently had a job logged against it will appear at the top.

I share the solution below in case its useful to anyone. First using my source names:

SortByColumns(
    AddColumns(
        Search(
            CashedStocks,
            TextSearchBox1.Text,
            "VARIETY",
            "GROWER",
            "CertificateNo"
        ),
        "Date",
        LookUp(
            CashedInspections,
            Stock = StockUniqueID,
            Last(
                Sort(
                    CashedInspections,
                    Stock = StockUniqueID,
                    Ascending
                )
            ).Created
        ),
        "NotInspected",
        Coalesce(StockUniqueID in CashedInspections. Stock)
    ),
    "NotInspected",
    Ascending,
    "Date",
    Ascending
)

And now in more generic and adaptable code:

SortByColumns(
    AddColumns(
        Search(
            Table1,
            TextSearchBox1.Text,
            "Column1",
            "Column2",
            "Column3"
        ),
        "Date",
        LookUp(
            Table2,
            T2ID = T1ID,
            Last(
                Sort(
                    Table2,
                    T2ID = T1ID,
                    Ascending
                )
            ).Created
        ),
        "NoRelatedRecordYet",
        Coalesce(T1ID in Table2.T2ID)
    ),
    "NoRelatedRecordYet",
    Ascending,
    "Date",
    Ascending
)

I hope this is useful to somebody.

Thank you for your help @v-bofeng-msft