cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Sort Table 1 by newest available record in Table 2

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Sort Table 1 by newest available record in Table 2

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

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Sort Table 1 by newest available record in Table 2

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

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Sort Table 1 by newest available record in Table 2

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 

Highlighted
Advocate II
Advocate II

Re: Sort Table 1 by newest available record in Table 2

 

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 

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,049)