cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PainterMC
Regular Visitor

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

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

DateJobCost TypeTotal $

5/10/2021

Job A1 Labor$500
5/11/2021Job B1 Labor$750
5/12/2021Job A2 Overtime Labor$100

 

Budgets

JobLabor BudgetLabor ActualLabor Budget Spent
Job A$1000$60060%
Job B$750$750100%

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

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

v-albai-msft_0-1620976999590.png

 

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

v-albai-msft_1-1620976999594.png

 

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

v-albai-msft_2-1620976999596.png

below screenshot just for your reference, tell you where to put above code.

v-albai-msft_3-1620977127935.png

 

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

View solution in original post

5 REPLIES 5
seanbrogan
Advocate V
Advocate V

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

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

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

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?

v-albai-msft
Community Support
Community Support

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

v-albai-msft_0-1620976999590.png

 

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

v-albai-msft_1-1620976999594.png

 

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

v-albai-msft_2-1620976999596.png

below screenshot just for your reference, tell you where to put above code.

v-albai-msft_3-1620977127935.png

 

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

View solution in original post

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 (3,192)