i am struggling with a solution to the below issue,
Price Table: [dbo.Vw_Prices]
Customer_ID | Site_ID | Price_ID(PK) | Effective_Date
5 | 1 | 100 | 1-1-2001
5 | 2 | 123 | 1-5-2001
5 | 1 | 145 | 1-2-2001
2 | 3 | 124 | 5-3-2001
2 | 4 | 165 | 4-3-2001
3 | 5 | 196 | 21-1-2001
1 | 6 | 156 | 4-5-2001
1 | 6 | 197 | 9-1-2001
3 | 5 | 186 | 1-3-2001
What i want to get is the most recent effective price (Effective_Date), for all the sites (Site_ID) registered to the a customer (Customer_ID). Customer and effective date will be picked based on User input. My table has data more than 2,000 rows and is frequently updated.
This is the Code im trying to use but it give all the prices for all the sites for the customer
SortByColumns( Filter( '[dbo].[Vw_Prices]', Customer_ID = Var_SelectedCust.Customer_ID, Effective_Date <= Inp_PricingDate.SelectedDate ), "Site_ID", Descending, "Effective_Date", Descending )
I tried working with Filter and Group by but cant fully get that to work. I have a Azure SQL, set for the data source
Thanks in advance for any help.
GroupBy( SortbyColumns( '[dbo].[Vw_Prices]', "Effective_Date", Descending, "Site ID", Descending ), "Site_ID", "Restofdata" )
In the outer gallery Items property and First(Restofdata) in the inner gallery, Nested galleries on left. Same nested gallery w/o First() on inner gallery.
Thanks for the suggestion, however this is evaluating only the first 500 records and not any after, due to delegation limit of "Groupby" function. any way to replace group by?
You can increase your limit to 2k. File>Settings>Advanced settings>Data row limit change from 500 to 2k. Otherwise you will have to either filter your dataset down to less than 2k and then apply the GroupBy() or create a collection from the entire dataset. There are a number of posts on how to create a collection from large sharepoint lists.
Watch Microsoft Business Applications Summit sessions on-demand.
And the winner is...
The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!
Features releasing from April 2020 through September 2020