You’re offline. This is a read only version of the page.
Hi Team
I am building one Power APP and I have data like below
Forecast field users should update and I have thousands of KPIs. The forecast is the same for all months for one KPI.
For example, the KPI ABC forecast is the same for 12 months i.e. GREEN
But the requirement is, that users should not update Forecast of the one KPI 12 times. Once updated in January, in SQL Table, that KPI forecast should be automatically updated in Feb to Dec.
I tried to use the PATCH function, but not able to succeed.
Please help
| KPI | Month | Forecast |
| KPI ABC | January | GREEN |
| KPI ABC | February | |
| KPI ABC | March | |
| KPI ABC | April | |
| KPI ABC | May | |
| KPI ABC | June | |
| KPI ABC | July | |
| KPI ABC | August | |
| KPI ABC | September | |
| KPI ABC | October | |
| KPI ABC | November | |
| KPI XYZ | January | YELLOW |
| KPI XYZ | February | |
| KPI XYZ | March | |
| KPI XYZ | April | |
| KPI XYZ | May | |
| KPI XYZ | June | |
| KPI XYZ | July | |
| KPI XYZ | August | |
| KPI XYZ | September | |
| KPI XYZ | October | |
| KPI XYZ | November | |
| KPI PQR | January | RED |
| KPI PQR | February | |
| KPI PQR | March | |
| KPI PQR | April | |
| KPI PQR | May | |
| KPI PQR | June | |
| KPI PQR | July | |
| KPI PQR | August | |
| KPI PQR | September | |
| KPI PQR | October | |
| KPI PQR | November |
Output
Thanks
Thanks @samfawzi_acml for your time & effect.
I will try the solution and let you know.
Regards
Pijush
hi @PijushRoy , Because I'm not familiar with the specific codes you've implemented and the interface you've built in your app, I'll make a few assumptions with the approach outlined below:
1. Assuming you have a collection (colKPIs) that pulls data from the SQL table:
Share
Report
All responses (
Answers (ClearCollect(colKPIs, '[dbo].[YourSQLTable]');
2. Form to Update the Forecast:
Let's say you have a form where the user updates the forecast for a specific KPI for January.
3. Update Button Logic:
When the user updates the forecast for January, you use the following logic to update all months:
// Assuming txtKPI is the TextInput for the KPI and txtForecast is the TextInput for the Forecast
// colKPIs is your collection that has all the KPI data from the SQL table
ForAll(
Filter(colKPIs, KPI = txtKPI.Text), // Filter the collection for the specific KPI
Patch(
'[dbo].[YourSQLTable]', // Your SQL table
LookUp('[dbo].[YourSQLTable]', KPI = txtKPI.Text && Month = ThisRecord.Month), // Find the record for each month for the specific KPI
{Forecast: txtForecast.Text} // Update the Forecast field
)
);
// Optionally refresh the collection to reflect changes
ClearCollect(colKPIs, '[dbo].[YourSQLTable]');
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.
Hi @PijushRoy ,
I am not an SQL user with Power Apps, so cannot comment further other than what I posted would technically work with SharePoint.
Hi @ANB @WarrenBelz @happyume
Thank you so much for your help, but I had no luck in solving the requirement.
As @happyume asked, why do I need a month column if forecasts are the same for all months?
This forecast column is for the yearly forecast which should be the same for all months.
I have more columns in my real table like Monthly Target, Monthly Actual etc, so I have a month column. Users will update the target and actual monthly.
But the yearly forecast will update the starting month of the year and same for rest of the months.
I missed the notification, sorry for the delay.
Your further help is appreciated.
Thanks
Pijush
Responding as you tagged me - firstly what @happyume has posted is fundamentally correct - small syntax error, but I am sure that would be fixed. I will add a couple of things in this code
UpdateIf(
Filter(
'YourDataSource',
KPI = 'KPI ABC' && Forecast = Blank()
),
{Forecast: 'Green'}
)firstly to only update fields not already updated and also that you need to turn this on if you have a large dataset.
Lastly (and one of the main reasons I posted) was that @ANB asked you to share the code you have attempted and there is good reason for this, even if it has errors. Your post is not entirely clear on a few things, particularly the process the user would employ to execute this and often (as well as containing all the control and field names) the structure / logic of the code, combined with your other details greatly assists in putting the "picture together".
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
MVP (Business Applications) Visit my blog Practical Power Apps
If the Forecast value will always be the same for a given KPI, then in your database model, why do you even need the month column? If this is in place so that in future you will have a Year column as well, then perhaps you need two separate tables.
However, if you want to use a single table structure that you have shown, then I am assuming that you have the table with months already present, and you are trying to update existing rows and not adding new rows.
You should try UpdateIf function
UpdateIf(
'YourDataSource',
KPI='KPI ABC',
{Forecast='Green'}
)
See https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-update-updateif#syntax
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.
Please suggest any solution if you have one.
@WarrenBelz @ANB @Amik @anandm08 @mmbr1606 @SpongYe @madlad @DBO_DV @timl @Pstork1 @KeithAtherton @happyume @BCBuizer @LaurensM @Rajkumar_404 @MarkBandR @CatSchneider and all team members.
Please help, it is urgent
Hi @ANB
Thank you
I have a very primary level of knowledge of Power App. The patch is incorrect which I tried. This requirement is from one of the big projects in VM.
I have shared the example of the requirement, could you please share an idea or code to solve the requirement.
HI @PijushRoy , Could you please share more on your code part? I mean as you mentioned that you are using patch then have you tried ForAll and patch both?
Thanks,
ANB
#1
WarrenBelz
394
Most Valuable Professional
#2
MS.Ragavendar
193
#3
stampcoin
149
