Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 9zz4Oh4k/GD/HPeQtxsLkT
Power Apps - Building Power Apps
Unanswered

Automatically update others rows data when same KPI data updated

Like (0) ShareShare
ReportReport
Posted on 21 Jun 2024 15:53:02 by 34

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

KPIMonthForecast
KPI ABCJanuaryGREEN
KPI ABCFebruary 
KPI ABCMarch 
KPI ABCApril 
KPI ABCMay 
KPI ABCJune 
KPI ABCJuly 
KPI ABCAugust 
KPI ABCSeptember 
KPI ABCOctober 
KPI ABCNovember 
KPI XYZJanuaryYELLOW
KPI XYZFebruary 
KPI XYZMarch 
KPI XYZApril 
KPI XYZMay 
KPI XYZJune 
KPI XYZJuly 
KPI XYZAugust 
KPI XYZSeptember 
KPI XYZOctober 
KPI XYZNovember 
KPI PQRJanuaryRED
KPI PQRFebruary 
KPI PQRMarch 
KPI PQRApril 
KPI PQRMay 
KPI PQRJune 
KPI PQRJuly 
KPI PQRAugust 
KPI PQRSeptember 
KPI PQROctober 
KPI PQRNovember 

Output

PijushRoy_0-1718985125507.png

Thanks

Categories:
  • PijushRoy Profile Picture
    34 on 10 Jul 2024 at 08:00:08
    Re: Automatically update others rows data when same KPI data updated

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

    Regards
    Pijush


  • SamFawzi-SmartSolutions Profile Picture
    258 Super User 2025 Season 1 on 09 Jul 2024 at 14:16:05
    Re: Automatically update others rows data when same KPI data updated

    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.

     

     

  • WarrenBelz Profile Picture
    146,947 Most Valuable Professional on 28 Jun 2024 at 21:55:29
    Re: Automatically update others rows data when same KPI data updated

    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.

  • PijushRoy Profile Picture
    34 on 28 Jun 2024 at 18:04:41
    Re: Automatically update others rows data when same KPI data updated

    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 Profile Picture
    146,947 Most Valuable Professional on 23 Jun 2024 at 07:06:36
    Re: Automatically update others rows data when same KPI data updated

    @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.

    WarrenBelz_0-1719125887498.png

    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

  • Mitanshu Profile Picture
    1,614 Super User 2025 Season 1 on 23 Jun 2024 at 05:46:48
    Re: Automatically update others rows data when same KPI data updated

    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.

  • PijushRoy Profile Picture
    34 on 23 Jun 2024 at 05:24:15
    Re: Automatically update others rows data when same KPI data updated

    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

  • PijushRoy Profile Picture
    34 on 21 Jun 2024 at 16:35:10
    Re: Automatically update others rows data when same KPI data updated

    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.

  • ANB Profile Picture
    7,081 Super User 2025 Season 1 on 21 Jun 2024 at 16:18:50
    Re: Automatically update others rows data when same KPI data updated

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 89 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 58

#3
Michael E. Gernaey Profile Picture

Michael E. Gernaey 42 Super User 2025 Season 1

Overall leaderboard