Bulk updating child data source fields on change in parent field.

(1) Share

Report

Posted on by lknudson1

202

I have a parent SQL data table, JobTask with a field for sub assignment to a given task.

Child SQL data table is JobTaskDetail.

jtaskdetail is a collection of JobTaskDetail that is filtered for display of left gallery shown below.

When the sub on JobTask table changes, a new calculated rate should be applied to all the child fields based on their individual sub pricing rules.

Below is the interface used. Sub shown on right side, Work Items with rates on left.

So, for example, a given task changes its assignment from SubA to SubB

New calculated sub specific rates should be applied to each of the child work items for the task.

I am having a really hard time getting this to work. It seems like it should be so easy, but I have spent hours and hours working on it,, searching web, etc.

The code below is basically what I would like to work. The Unit Costs should all be recalculated based on lookup to Sub Rates table.

I have tried many variations of this to get to work.

Help!

Patch('[dbo].[JobTaskDetail]',
ForAll(jtaskdetail,
{JTDDefault_Unit_Cost:
LookUp(SubRates,BuilderID=CurBuilder And TDTask_No=TaskG.Selected.JTTask_No And Class=Class And SubCode=TaskG_EmpSelCB.Selected.WCode,Unit_Cost)}))
 
Thanks!
 
Larry

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

lknudson1 202 on at

Like (0)

Report

Bulk updating child data source fields on change in parent field.

One quick update:

The code below does work to an extant. However, the work item rates are sometimes different depending on what work class they fall under.

So, if a work item is for a garage say, it will have a different rate than that for working on a ceiling in a house.

The code below will just take the first lookup rate found and apply it to all work items.

UpdateIf('[dbo].[JobTaskDetail]',JTDJob_ID=CurJob And JTDTask_No=TaskG.Selected.JTTask_No,{JTDDefault_Unit_Cost:

LookUp(SubRates,BuilderID=CurBuilder And Task_No=TaskG.Selected.JTTask_No And Class=Class And SubCode=TaskG_EmpSelCB.Selected.WCode,Unit_Cost)})

Thanks

Was this reply helpful? Yes No
WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report
Bulk updating child data source fields on change in parent field.

Hi @lknudson1

Try going back to the first one, but use the As Disambiguation operator
Patch( '[dbo].[JobTaskDetail]', ForAll( jtaskdetail As _Task, { JTDDefault_Unit_Cost: LookUp( SubRates, BuilderID = _Task.CurBuilder And TDTask_No = TaskG.Selected.JTTask_No And Class = _Task.Class And SubCode = TaskG_EmpSelCB.Selected.WCode, ).Unit_Cost } ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
lknudson1 202 on at

Like (0)

Report

Bulk updating child data source fields on change in parent field.

WarrenBelz,

Thanks for your suggestion. This errors out as:

Was this reply helpful? Yes No
lknudson1 202 on at

Like (0)

Report

Bulk updating child data source fields on change in parent field.

What is frustrating is that if I just add a calculated column to jtdetail gallery with the lookup, it pulls in all the right rates:

Was this reply helpful? Yes No
WarrenBelz 152,847 Most Valuable Professional on at

Like (0)

Report
Bulk updating child data source fields on change in parent field.

@lknudson1,

You have only two of the four filters in your example. The error is suggesting that there is a field type mismatch in one of the comparisons - you can also try the "long" and inefficient way
ForAll( jtaskdetail As _Task, Patch( '[dbo].[JobTaskDetail]', { JTDDefault_Unit_Cost: LookUp( SubRates, BuilderID = _Task.CurBuilder And TDTask_No = TaskG.Selected.JTTask_No And Class = _Task.Class And SubCode = TaskG_EmpSelCB.Selected.WCode, ).Unit_Cost } ) )

but I suspect that one of the field type in SubRates does not match the field type in jtaskdetail.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
Suggested answer

lknudson1 202 on at

Like (1)

Report

Bulk updating child data source fields on change in parent field.

I ended up checking into PowerApps 911 and got a solution using:

ForAll(
Filter(
jtaskdetail,
JTDJob_ID = CurJob,
JTDTask_No = TaskG.Selected.JTTask_No Or JTDTask_No = 8,
cboAllTaskDetail.Value = true Or JTDTemplate = galModelOptions.Selected.JO_Template
) As _Task,
Patch(
jtaskdetail,
{ID: _Task.ID},
{
JTDDefault_Unit_Cost: LookUp(
SubRates,
BuilderID = _Task.JTDBuilder And Class=If(TaskG.Selected.JTTask_No=5,LookUp('[dbo].[Job]',Job_ID=CurJob,Ceiling_Texture),_Task.Class) And SubCode = Self.Selected.WCode And Task_No=TaskG.Selected.JTTask_No,
Unit_Cost
)

}
)
)

Thanks Warren for suggestions!

Was this reply helpful? Yes No