Display oldest entry plus additional filter

(1) Share

Report

Posted on by BHaapi

134

First Photo-I have a oldest entry field displayed on my page for a list item:

First(Sort('Law Enforcement Referral Tracking',Created, SortOrder.Ascending)).Created

Second Photo- and an additional filter that i was able to use to see how many items by facility (the total items are broken down by 2 large collections, to prevent delegation issues):

Sum(CountRows(Filter(MSPLocal, StartsWith('Facility Initiated',Facilitylookup1.Text))),CountRows(Filter(LEOInactive, StartsWith('Facility Initiated',Facilitylookup1.Text))))

but now i need to lookup just that facilities oldest active item (which is MSPLocal collection). is there a way to combine the Facilitylookup1 box with the oldest date in the list?

summary, i would like a date displayed like this:

but additionally sorted by the 'Facility Initiated' like this feeding ONLY from collection MSPLocal:

Categories:

General Questions

All responses (4)

Answers (1)

BHaapi 134 on at

Like (1)

Report

Re: Display oldest entry plus additional filter

I had to handjam the SortOrder.Ascending, it didnt pick up on the ascending in the equation. thank you so much!!
Verified answer

poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report
Re: Display oldest entry plus additional filter

@BHaapi

If you're receiving an error that the Sort function has invalid arguments, it suggests that there might be an issue with the fields you're attempting to sort or filter, or with the logic used in these functions.

Here's what you should check:

1. Check your data source: Ensure the fields 'Facility Initiated' and 'Created' actually exist in your 'MSPLocal' collection. Remember, PowerApps is case-sensitive, so make sure the field names match exactly.

2. Check the data types: Make sure 'Facility Initiated' is a Text field and 'Created' is a DateTime field.

3. Check your logic: '&&' is used to combine conditions in a logical 'and' operation. In the Sort function, you should use a comma to separate the field to sort by and the sort direction.

If you're still having trouble, you can try to break down the formula to identify at which point the error occurs.

1. Start by checking if you can filter the collection properly. Put this into a Label's Text property or just look at it in a gallery:

CountRows(Filter(MSPLocal, StartsWith('Facility Initiated', Facilitylookup1.Text)))

This should return the number of rows in the MSPLocal collection where 'Facility Initiated' starts with the text in Facilitylookup1. If this doesn't work, your issue is likely in the Filter function or in the 'Facility Initiated' field.

2. Next, add the sorting functionality. Change your Label or Gallery items property to:

First(Sort(Filter(MSPLocal, StartsWith('Facility Initiated', Facilitylookup1.Text)), Created, Ascending)).Created

This should return the earliest 'Created' date from the filtered items. If this doesn't work, your issue is likely in the Sort function or in the 'Created' field.

3. Add the text formatting. Change your Label or Gallery items property to:

Text(First(Sort(Filter(MSPLocal, StartsWith('Facility Initiated', Facilitylookup1.Text)), Created, Ascending)).Created, "m/d/yyyy h:mm")

This should return the earliest 'Created' date from the filtered items, formatted as "m/d/yyyy h:mm" (or whichever formatting you prefer to use).

If this doesn't work, your issue is likely with the Text function or the format string.

Remember to replace 'Facility Initiated', 'Created', 'MSPLocal', and 'Facilitylookup1' with your actual field and control names.

By breaking down and testing each part of the formula separately, you can identify at which point the error occurs and focus your troubleshooting efforts there.

See if it helps @BHaapi
BHaapi 134 on at

Like (1)

Report

Re: Display oldest entry plus additional filter

Im getting that the sort function has invalid arguments. This is similar to one i tried building but didnt work (hence im here). im not sure why, i double checked the datalinks and they all seem good to go. i tried replacing the , with && and that didnt work either
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Re: Display oldest entry plus additional filter

@BHaapi

Here's how to solve this:

First, try to filter the collection by the selected facility, then try to sort the filtered items by the 'Created' field and finally, try to get the first (oldest) item. Here's a formula example:

First(Sort(Filter(MSPLocal, StartsWith('Facility Initiated', Facilitylookup1.Text)), Created, Ascending)).Created

However, this will return a datetime value. To format it as "mm/dd/yyyy hh:mm", we can use the Text function:

Text(First(Sort(Filter(MSPLocal, StartsWith('Facility Initiated', Facilitylookup1.Text)), Created, Ascending)).Created, "mm/dd/yyyy hh:mm")

This should display the date in the desired format. The "m" is for month, "d" is for day, "yyyy" is for a four-digit year, "h" is for hour in 12-hour format, and "mm" is for minute. If you want to display hour in 24-hour format, use "H" instead of "h".

If you want the date to be in the format "m/d/yyyy h:mm", you simply need to change the format string in the Text function.

Text(First(Sort(Filter(MSPLocal, StartsWith('Facility Initiated', Facilitylookup1.Text)), Created, Ascending)).Created, "m/d/yyyy h:mm")

In this formula, replace 'Facility Initiated' and 'Created' with the actual column names of your 'MSPLocal' collection. The formula assumes 'Facilitylookup1' is the name of your facility input box, replace it if necessary.

This formula will get the oldest active item's 'Created' date for a specific facility and display it in the format "mm/dd/yyyy hh:mm". If the facility has no active items, it will return an empty string. If the specified facility does not exist, it should also return an empty string.

Hope it helps @BHaapi