cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mim
Helper I
Helper I

how to do Updateif else patch

I manage to make two buttons, one to add records and the other to Update

mim_0-1610686222867.png

The Data in this screen is based on Table1

 

I am trying to add a new button "Save" that save the results in a new Table "QTIES"

 

the formula will check if ID and Date exist already in the Table "QTIES" then Update INSTQTY,  otherwise add a new row with (ID,Date, INSTQTY), I am using Dataverse for Team, and the two Tables are not Connected by relationship ( can't make records lookup works, that's a problem for another time)

 

Code for Updating

 

ForAll(
Gallery_Mainform.AllItems,
UpdateIf(
QTIES,
ID = TextBox3.Value && DATE = DatePicker1.Value,
{INSTQTY: Value(LabelQTY.Text)}
)
)

 

 

The code for adding new records

 

ForAll(
Gallery_Mainform.AllItems,
Patch(
QTIES,
{
ID: TextBox3.Value,
DATE: DatePicker1.Value,
INSTQTY: Value(LabelQTY.Text)
}
)
)

 

Thanks for your help, and sorry, I don't know how to keep the formatting

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks @mim ,

The code I supplied was using your values - assuming ID is numeric (which it generally is) then

ID = Value(TextBox3.Text) && DATE = DatePicker1.SelectedDate

is simply looking for records where the ID field is equal to the value (numeric)  in TextBox3 and the DATE field is the same as that selected in DatePicker1.

It will then update all matching records 

INSTQTY: Value(LabelQTY.Text)

to change the field INSTQTY (again needs to be a numeric field in the data source) to the numeric value in LabelQTY.

Please tell me if any of that is untrue.

 

Please click Accept as solution 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 Thumbs Up.

View solution in original post

6 REPLIES 6
WarrenBelz
Super User III
Super User III

Hi @mim ,

Using your structure - 

Update

ForAll(
   Gallery_Mainform.AllItems,
   UpdateIf(
      QTIES,
      ID = Value(TextBox3.Text) && DATE = DatePicker1.SelectedDate,
      {INSTQTY: Value(LabelQTY.Text)}
   )
)

New

ForAll(
   Gallery_Mainform.AllItems,
   Patch(
      QTIES,
      Defaults(QTIES),
      {
         ID: TextBox3.Value, 
         DATE: DatePicker1.SelectedDate,
         INSTQTY: Value(LabelQTY.Text)
      }
   )
)

 I do have a question on ID however as this is auto set (I assume this is SharePoint you are dealing with)

 

Please click Accept as solution 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 Thumbs Up.

mim
Helper I
Helper I

I am using dataverse for Team

 

your formula, just added a new records

 

I need a condition to see if the ID is the same and the date is the same then update only INSTQTY

 

my PK = is the concatenation of ID and date

thanks 

Thanks @mim ,

The code I supplied was using your values - assuming ID is numeric (which it generally is) then

ID = Value(TextBox3.Text) && DATE = DatePicker1.SelectedDate

is simply looking for records where the ID field is equal to the value (numeric)  in TextBox3 and the DATE field is the same as that selected in DatePicker1.

It will then update all matching records 

INSTQTY: Value(LabelQTY.Text)

to change the field INSTQTY (again needs to be a numeric field in the data source) to the numeric value in LabelQTY.

Please tell me if any of that is untrue.

 

Please click Accept as solution 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 Thumbs Up.

View solution in original post

Jitendra_jangid
Resolver II
Resolver II

Hi,

Could you please try below formula

ForAll(
   Gallery_Mainform.AllItems, 
With({varExistingRecord : Lookup(QTIES, ID = Value(TextBox3.Text) && DATE = DatePicker1.SelectedDate)},
 Patch(
      QTIES,
if(IsBlank(varExistingRecord),      Defaults(QTIES),varExistingRecord),
      {
         ID: TextBox3.Value, 
         DATE: DatePicker1.SelectedDate,
         INSTQTY: Value(LabelQTY.Text)
      }
   )
)

Currently, I am using this and it works but not sure about the performance, please notice as it is dataverse for team, the unique ID is auto generated, so I can't use it.

 

 

If(
    IsBlank(
        LookUp(
            QTIES,
            ID = TextBox3.Value && DATE = DatePicker1.Value 
        )
    ),
    ForAll(
        Gallery_Mainform.AllItems,
        Patch(
            QTIES,
            {
                ID: TextBox3.Value,
                DATE: DatePicker1.Value,
                INSTQTY: Value(LabelQTY.Text)
            }
        )
    ),
    ForAll(
        Gallery_Mainform.AllItems,
        UpdateIf(
            QTIES,
            ID = TextBox3.Value && DATE = DatePicker1.Value,
            {INSTQTY: Value(LabelQTY.Text)}
        )
    )
)

 

@mim ,

So this is solved?

 

Please click Accept as solution 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 Thumbs Up.

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

Demo Extravaganza is Back!

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (57,901)