cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skong
Helper II
Helper II

Trying to update another list when I posting a form using patch

I have a form bind to list abc which I need to update. When user click submit. I would like to update a record in list abc and another list cde.

list aaa : title(text), amount(number)

My list abc    has the following structure  :  

     -  title (text),  account_no (lookup column from list cde)  ,  amount(lookup column from list aaa )

My list  cde   has the following structure :

     - title (text)  , account_no (text),  allocation(number)    ,   total_expense(number column)

I would like to update total_expense = total_expense + amount     on the record with same account_no on the cde list. How can I do it using patch ?

 

patch(cde, ?? , ?? + ?? );     

Thanks

 

Stan

3 ACCEPTED SOLUTIONS

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi@skong,

Based on the issue that you mentioned, do you want to update a record in listcde sharing the same account_no with the record in the listabc?

Could you please share a bit more about the scenario?

I have a similar test on my side that have the same configuration as your 3 SP lists, please check as below.

Patch(
    Listcde,
    LookUp(
        Listcde,
        account_no = DataCardValue8.Selected.Value
    ),
    {
        total_expense: LookUp(
            Listcde,
            account_no = DataCardValue8.Selected.Value
        ).'total_expense' + Value(DataCardValue7.Selected.Value)
    }
)

Note: The DataCardValue8 represents the Combo Box corresponding to the 'account_no' in the listabc.

The DataCardValue7 represents the Combo Box corresponding to the 'Amount' in the listabc.

I am not sure which control you used to display the 'account_no' and 'Amount', I assume you use the Combo Box.

In addition, when you have a Lookup field which looks up from a Number column, you should meet an issue that some decimals will follow behind the numbers. To avoid this, please set the DefaultSelectedItems property of the LookUp field as below

{
    Id: LookUp(Listaaa, Amount = Value(ThisItem.Amount.Value), ID),
    Value: Text(Value(ThisItem.Amount.Value), "[$-en-US]##,###,###")
}

Hope it could help you.

Regards,

Qi

View solution in original post

Just try your code with if statement in front of your code it works.

If (DataCardValue15.Selected.Value = "approved",

patch( …..)

 

)

Thanks

 

Stan

View solution in original post

Hi@skong,

So glad that you have solved your problem.

I provide the formula just based on your description, so the formula works for the Patch() you described in the case, right?

For that you just don't know the patch formula, so I provide the Patch(). The If statement is the specific condition in your scenario that I could never know.

So if my formula works for the Patch() formula that you requires in your case, I think you should either mark the Patch() formula to help the users find the solution if they have the same issue.

Again, congratulations for that.

Regards,

Qi

View solution in original post

7 REPLIES 7
BlessedCobba
Resolver I
Resolver I

Something along these lines should do the trick

Patch(
    CDE, 
   Filter(Cde, 
    account_no = (formfield))
        { Total_Expense: Total_Expense + Value(amount.text) }
)

 

v-qiaqi-msft
Community Support
Community Support

Hi@skong,

Based on the issue that you mentioned, do you want to update a record in listcde sharing the same account_no with the record in the listabc?

Could you please share a bit more about the scenario?

I have a similar test on my side that have the same configuration as your 3 SP lists, please check as below.

Patch(
    Listcde,
    LookUp(
        Listcde,
        account_no = DataCardValue8.Selected.Value
    ),
    {
        total_expense: LookUp(
            Listcde,
            account_no = DataCardValue8.Selected.Value
        ).'total_expense' + Value(DataCardValue7.Selected.Value)
    }
)

Note: The DataCardValue8 represents the Combo Box corresponding to the 'account_no' in the listabc.

The DataCardValue7 represents the Combo Box corresponding to the 'Amount' in the listabc.

I am not sure which control you used to display the 'account_no' and 'Amount', I assume you use the Combo Box.

In addition, when you have a Lookup field which looks up from a Number column, you should meet an issue that some decimals will follow behind the numbers. To avoid this, please set the DefaultSelectedItems property of the LookUp field as below

{
    Id: LookUp(Listaaa, Amount = Value(ThisItem.Amount.Value), ID),
    Value: Text(Value(ThisItem.Amount.Value), "[$-en-US]##,###,###")
}

Hope it could help you.

Regards,

Qi

View solution in original post

Let me try it first. Thanks

Hi Qi,

 

That is what I have 3 SP list let me try it first.  Thanks

 

Stan

Hi BlessedCobba,

 

I try this it show error on 

If (DataCardValue15.Selected.Value = "approved",
Patch(
CDE,
Filter(CDE,
account_number = DataCardValue18.Selected.Value )
{ Total_Expense: Total_Expense + Value(DataCardValue16.Selected.Value) }
)

)

 

It show error on "{" 

Just try your code with if statement in front of your code it works.

If (DataCardValue15.Selected.Value = "approved",

patch( …..)

 

)

Thanks

 

Stan

View solution in original post

Hi@skong,

So glad that you have solved your problem.

I provide the formula just based on your description, so the formula works for the Patch() you described in the case, right?

For that you just don't know the patch formula, so I provide the Patch(). The If statement is the specific condition in your scenario that I could never know.

So if my formula works for the Patch() formula that you requires in your case, I think you should either mark the Patch() formula to help the users find the solution if they have the same issue.

Again, congratulations for that.

Regards,

Qi

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (47,261)