Answered

How to have one table automatically update columns in other table through Powerapps? Similar to SUMIF function in excel

(0) Share

Report

Posted on by PainterMC

I just started converting an excel document with multiple tables into a PowerApp to make it easier to add costs. I have two main tables: Log and Budgets.

The Log table has columns like Date, Job, Cost Type, and Total $. I have another table called Budgets that has the Job, Labor Budget, Labor Actual, and Labor Budget Spent (Also columns for other budgets but I can repeat the process on the other budgets). Here's a sample of what the tables look like.

Log

Date	Job	Cost Type	Total $
5/10/2021	Job A	1 Labor	$500
5/11/2021	Job B	1 Labor	$750
5/12/2021	Job A	2 Overtime Labor	$100

Budgets

Job	Labor Budget	Labor Actual	Labor Budget Spent
Job A	$1000	$600	60%
Job B	$750	$750	100%

Now I have the log setup where you select the Job from a list of active jobs, and the cost types from a cost type list, and the Total $ is entered.

The Budgets page already has all the Jobs that need to be on there, the Labor Budget is manually entered, but the Labor Actual should be doing something like a SUMIF in excel to get that value of $600 for Job A.

This is the formula I have in the Datacardvalue for Labor Actual in the form for editing a row in the Budgets table.

Job (From Budgets table) = Datacardvalue18

Sum(Filter(Log, Job=DataCardValue18.Selected.Job_x0020_Name,Or('Cost Type'="1 Labor", 'Cost Type'="2 Overtime Labor")),'Total $')

This formula works perfectly fine, but it only updates the Budgets table if the form is submitted. What I need to know is how can I build this so the Budgets tab updates as soon as a cost is added. For example, if I were to add a cost to Job A, 1 Labor, for $500. I would want Job A in the Budgets tab to immediately have Labor Actual of $1100. Instead, I would have to edit the Budgets form of Job A, submit the form, and then it would update to $1100.

Is there anyway to do this?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

seanbrogan 428 on at

Like (0)

Report

I think you could to this in the textbox for entering the budget data and the UpdateIf command.

In the OnChange property of the box where the user enters the budget figure you could put something along the lines of...

UpdateIf(BudgetsTable, Job = ThisItem.Job,{LabourBudget : (LabourBudget + Textbox.Value)})

Was this reply helpful? Yes No
PainterMC 10 on at

Like (0)

Report

Would I do this on the whole form or would it be on each individual Datacard?

Was this reply helpful? Yes No
seanbrogan 428 on at

Like (0)

Report

Ah good question, I tend not to use forms that much I find them very restrictive. I would personally do this on just textboxes or a gallery.

I'm specualting now because I dont really use the forms. But could you try...

In the datacard for the labourbudget figure, have a hidden field that has the budget figure in it, and another textbox not bound to anything. Then in the unbound textbox, in the OnChange event, put something like...

Set(NewValue,LabourBudget+UnboundTextbox.Value)

Bit of a stretch I think to do this inside a form

Was this reply helpful? Yes No
PainterMC 10 on at

Like (0)

Report

I have a gallery set up as well for the Budgets table. How would I do this in the gallery when there's no OnChange property?

Was this reply helpful? Yes No
Verified answer

v-albai-msft on at

Like (0)

Report
Hi @PainterMC ,
Do you want to change the value of “Labor Actual” in Budgets table when creating a new cost in Log table?
How do you add a cost? Using an EditFrom?
If my understand is correct, you can achieve this using below method.

See my example, I have a form called Form3, it is used to add new cost(with Job column and Total column). The control name of Job Textinput is “DatacardValue46”, and the name of Total Textinput is “DatacardValue48”.

By default, when click submit button, new cost will be created in my DataSource(Log table).

Method1.
Since the control type of Total is TextInput, you can set OnChange property of Total to:
Patch(Budgets,LookUp(Budgets,Job=DataCardValue46.Text) ,{'Labor Actual': LookUp(Budgets,Job=DataCardValue46.Text) .'Labor Actual' + DataCardValue48.Text)})
below screenshot just for your reference, tell you where to put above code.

Method2.
Set OnSelect property of your Submit button to below( after you submit new cost into Log table, the specified value of 'Labor Actual' in Budgets will also be changed):
SubmitForm(Form3); Patch(Budgets,LookUp(Budgets,Job=DataCardValue46.Text) ,{'Labor Actual': LookUp(Budgets,Job=DataCardValue46.Text) .'Labor Actual' + DataCardValue48.Text)})
Best regards,
Allen

Was this reply helpful? Yes No