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
Super User

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.

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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,976)