Update rows from gallery

[mahmoodhsaud]

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

[CNT · Accepted solution]

@mahmoodhsaud To do something like this,

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

[mahmoodhsaud]

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 · Accepted solution]

@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})
)

[mahmoodhsaud]

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

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 · Accepted solution]

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