Gallery Grouped Data Lookup

(0) Share

Report

Posted on by RobThrive

Hello,

When I create a gallery in my app, I'm using the following code for the Items property.

SortByColumns(GroupBy('[dbo].[vw_BSC_Targets]',"KPI_Name","KPI_Description", "Others"),"KPI_Name")

This is because there are multiple lines for the KPI_Name and Descriptions.

However when I select one of the items in the gallery and click on my "add new record" button which loads a new form for recording new measures each month, I'd like to be able to reference the "DateTo" and "Period_Target" columns which is wrapped up in the "Others" group in the above code.

If I moved that column out of "Others" it would result in 12 rows per KPI name, which i do not want.

But in the new form I have a label which takes the "Period_Target", I'd like this label to change based on the date that the user selects in the calendar drop down.

So if the user select end of month for July, the Period_Target label will look up the target value for July and show this, if the user changes the date selector to August the lookup happens again to get the correct/new value for the label.

Is this possible at all?

Hope it has all made sense,

Thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

v-monli-msft on at

Like (0)

Report
Hi @RobThrive ,

You can create a new collection with the grouped table and use this collection as the data source of gallery. Then use the original data source table as the data source of edit form control.

Set below formula in the Period_Target label:

Lookup('[dbo].[vw_BSC_Targets]',Month("DateField")=Month(DatePicker1.SelectedDate),TargetValueField)

Regards,

Mona

Was this reply helpful? Yes No
Verified answer

RobThrive 53 on at

Like (0)

Report
Thanks @v-monli-msft this was a great help.
I ended up using something slightly different but based on your code.
For others who have similar questions and need this, consider using the code mentioned but also include the year or just the date as a whole.

Code with Year:
Lookup('[dbo].[vw_BSC_Targets]',Month("DateField")=Month(DatePicker1.SelectedDate) && Year("DateField")=Year(DatePicker1.SelectedDate),TargetValueField)

I ended up creating a variable that gets set via the datepicker "OnSelect" which selects the last day of the previous month. I then used this in my lookup.
Set (target_Date, // This bit of code ensure that no matter which day of the month the user picks // it will find the last day of that month to be sent to the SQL Server. // This might be problematic if we need to start recording weekly targets though. // If we need more flexibility I recommend getting the target_frequency value and using // an if statement to control whether it should be EOMonth or EOweek date sent to SQL DateAdd( DateAdd( Date(Year(Input_Obs_Date_1.SelectedDate), Month(Input_Obs_Date_1.SelectedDate),1), 1, Months ), -1, Days))

lookup code for my label. I found I had to use Text around both sets of dates for this to work.
Text(LookUp('[dbo].[vw_BSC_Targets]', Text(DateTo) = Text(target_Date) && KPI_Name = Input_KPI_Name_1.SelectedText.KPI_Name, Period_Target))

Only issue now, is when the form loads for the first time, I have it set the DefaultDate to the last day of last month. But the variable doesn't see this value correct, it still picks it up as the current month, until the user actually selects a date on the picker. I believe this to be a bug in Power Apps.

Was this reply helpful? Yes No