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

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 Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,097)