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

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
Community Champion
Community Champion

@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

CNT
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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

CNT
Community Champion
Community Champion

@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 III
Helper III

@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

CNT
Community Champion
Community Champion

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Top Solution Authors
Top Kudoed Authors
Users online (2,128)