cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahmoodhsaud
Helper IV
Helper IV

Update rows from gallery

Hi Everyone,

I have an excel which calculates the efforts based on the percentage, I wanted to update each row individually and calculate the total of each row.

And also need to do validation for percentage, If the percentage of the total rows increases more than 100 it needs to give an error warning. How to replicate the entire setup in powerapps? 

I started with the custom gallery but hit a roadblock while doing the calculation.

Can anyone give a `suggestion, so it will be helpful for me to start building the app

 

Thanks in Advance

 

Regards,

Mahmood

2 ACCEPTED SOLUTIONS

Accepted Solutions
CNT
Super User
Super User

@mahmoodhsaud To do something like this,

 

gif.gif

 

First you'll need a Collection. Set this collection in the Items property of the gallery and add the necessary controls.

ClearCollect(colTable, {Phase:"Research",Percentage:0,Hours:0,Weeks:0,Months:0}, {Phase:"Planning",Percentage:0,Hours:0,Weeks:0,Months:0})

 

In the OnChange property of the Percentage checkbox, you'll have the formula (change the calculations as per your needs),

Patch(colTable, ThisItem, {Percentage:Value(tinPercentage.Text), Hours:Value(tinPercentage.Text)*2, Weeks:Value(tinPercentage.Text)*3, Months:Value(tinPercentage.Text)*4})

 

The Text property of the Total labels will have Formulas like,

Sum(GalleryCalc.AllItems, tinPercentage.Text)

 

The visible property of the Warning label will have the formula,

Value(lblTotalPercentage.Text) >100

 

View solution in original post

@mahmoodhsaud You can't do the ClearCollect in the Items property. Do it in the OnStart of the App of the OnVisible of the Screen and set the Items property of the gallery to colTable

To Patch the information to the Excel Table, use this,

 

ForAll(colTable As colTableValues,
	Patch(excelTable, LookUp(ExcelTable, Phase=colTableValues.Phase),{Percentage:colTableValues.Percentage,Hours:colTableValues.Hours,Weeks:colTableValues.Weeks,Months:colTableValues.Months})
)

View solution in original post

5 REPLIES 5
CNT
Super User
Super User

@mahmoodhsaud To do something like this,

 

gif.gif

 

First you'll need a Collection. Set this collection in the Items property of the gallery and add the necessary controls.

ClearCollect(colTable, {Phase:"Research",Percentage:0,Hours:0,Weeks:0,Months:0}, {Phase:"Planning",Percentage:0,Hours:0,Weeks:0,Months:0})

 

In the OnChange property of the Percentage checkbox, you'll have the formula (change the calculations as per your needs),

Patch(colTable, ThisItem, {Percentage:Value(tinPercentage.Text), Hours:Value(tinPercentage.Text)*2, Weeks:Value(tinPercentage.Text)*3, Months:Value(tinPercentage.Text)*4})

 

The Text property of the Total labels will have Formulas like,

Sum(GalleryCalc.AllItems, tinPercentage.Text)

 

The visible property of the Warning label will have the formula,

Value(lblTotalPercentage.Text) >100

 

View solution in original post

Hi @CNT, Thanks for your inputs

The below function I couldn't use it on items property and I need to update the value on excel after calculating percentage. Is it possible to use collection in items property.

ClearCollect(colTable, {Phase:"Research",Percentage:0,Hours:0,Weeks:0,Months:0}, {Phase:"Planning",Percentage:0,Hours:0,Weeks:0,Months:0})

Regards,

Mahmood

@mahmoodhsaud You can't do the ClearCollect in the Items property. Do it in the OnStart of the App of the OnVisible of the Screen and set the Items property of the gallery to colTable

To Patch the information to the Excel Table, use this,

 

ForAll(colTable As colTableValues,
	Patch(excelTable, LookUp(ExcelTable, Phase=colTableValues.Phase),{Percentage:colTableValues.Percentage,Hours:colTableValues.Hours,Weeks:colTableValues.Weeks,Months:colTableValues.Months})
)

View solution in original post

mahmoodhsaud
Helper IV
Helper IV

@CNT  The code works fine, But a single row of percentage and hours is static for that too I wanted to calculate weeks and hours, and also the value for weeks and months are not updating on the first change. Can you please help out in rectifying this issue?Screen Recording (17-06-2021 03-56-08 PM).gif

Patch(
    colCost,
    ThisItem,
    {
        Percentage: Value(percent_estgallery_TextInput_1.Text),
        Hours: Value(percent_estgallery_TextInput_1.Text) *Value(TotalTCO_Label.Text),
        Weeks: Value(hours_estgallery_Label_1.Text)/5 ,
        Months: Value(hours_estgallery_Label_1.Text)/30
    }
)

 Thanks and Regards,

Mahmood

@mahmoodhsaud Weeks & Hours are not getting updated properly because their calculation is based on hours_estgallery_Label_1.Text. Change it so that the calculations are done directly based on percent_estgallery_TextInput_1.Text.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,482)