Automatically update others rows data when same KPI data updated

[PijushRoy]

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

[ANB]

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

[PijushRoy]

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.

[PijushRoy]

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

[happyume]

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.

[WarrenBelz]

@PijushRoy ,

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

[PijushRoy]

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

[WarrenBelz]

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.

[samfawzi_acml]

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:

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.

[PijushRoy]

Thanks @samfawzi_acml for your time & effect.
I will try the solution and let you know.

Regards
Pijush