Power Apps PATCH & Lookups functions challenge

[PowerFlames]

Hello team,

I am encountering an issue with my Power App named "Admin Store Manager." The challenge lies in the interaction between two SharePoint lists: "adminstock," which comprises the complete inventory list, and "releasedstock," which includes all released inventory items. Notably, the "ItemDescription" column in "releasedstock" is a lookup column referencing "adminstock.Description."

My objective is to have the "ReleasedQuantity" subtracted from "adminstock.TotalQuantity" for the selected item in the "ItemDescription" lookup. To achieve this, I am attempting to update the "TotalQuantity" with the formula:

Updated value of TotalQuantity=Current (adminstock.TotalQuantity - ReleasedQuantity)Updated value of TotalQuantity=Current (adminstock.TotalQuantity - ReleasedQuantity)

Despite having a code without errors, the system is not successfully performing the subtraction and updating "adminstock.TotalQuantity." It seems that the challenge lies in the lack of recognition or association between the selected item from the "ItemDescription" combo and its corresponding "TotalQuantity."

I would greatly appreciate any insights or suggestions to address this issue effectively. Thank you for your assistance.

Set(
    varSelectedItem,
    releasedFormEdit.LastSubmit.ItemDescription.Value
);
Set(
    varSelectedRecord,
    LookUp(
        adminstock,
        Description = varSelectedItem
    )
);

Patch(
    adminstock,
    varSelectedRecord,
    {
        TotalQuantity: varSelectedRecord.TotalQuantity - Value(releasedFormEdit.LastSubmit.ReleasedQuantity)
    }
);

SubmitForm(releasedFormEdit);
ResetForm(releasedFormEdit)

[WarrenBelz]

Hi @PowerFlames ,

Firstly, please bear in mind that for LastSubmit to work, the Form needs to be submitted previously in the session. You are also submitting the Form after the Patch, which may lead to a potential record conflict if you are writing to the same record. Try this for a start

With(
   {
      _Record:
      LookUp(
         adminstock,
         Description = releasedFormEdit.LastSubmit.ItemDescription.Value
      ),
      _Released: Value(releasedFormEdit.LastSubmit.ReleasedQuantity)
   },
   Patch(
      adminstock,
      {
         ID: _Record._ID, 
         TotalQuantity: _Record.TotalQuantity - _Released
    }
);
SubmitForm(releasedFormEdit);
ResetForm(releasedFormEdit)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[PowerFlames]

Hi @WarrenBelz ,
Appreciate your assistance. Unfortunately, the code you supplied wasn't successful. I attempted to troubleshoot by using the Notify function to examine the values returned for the variables (_Record and _Released), but both came back as NULL (zero). I suspect the issue lies in the absence of a connection between the selected item (in the Description column) and its corresponding TotalQuantity. If we can establish a method to retrieve the TotalQuantity of the selected item, it should resolve the issue.

With(
   {
      _Record: LookUp(
         adminstock,
         Description = releasedFormEdit.LastSubmit.ItemDescription.Value
      ),
      _Released: Value(releasedFormEdit.LastSubmit.ReleasedQuantity)
   },
   Notify("_Released: " & _Record.TotalQuantity, NotificationType.Warning);
   Notify("TotalQuantity: " & _Record.TotalQuantity, NotificationType.Warning);
   
   Patch(
      adminstock,
      {
         ID: _Record.ID, 
         TotalQuantity: _Record.TotalQuantity - _Released
      }
   );
   SubmitForm(releasedFormEdit);
   ResetForm(releasedFormEdit)
)

but rather added item with no records but 0 in the TotalQuantity column see below image: 

[WarrenBelz · Accepted solution]

Hi @PowerFlames ;

My first question is whether the Form has been submitted before in the session as the second value _Released certainly should have data if there was a value in the field submitted.

[PowerFlames]

Hi @WarrenBelz , this solved my issue of deduction, I moved the (SubmitForm(releasedFormEdit);) at the top , thank you so much for the support. 

however i have observed that if edit an item in the form (added some remarks) and tries to save again the released Quantity value is deducted again the total quantity and keep on deducting whenever a say save existing item. can you please suggest a way to only deduct when adding a new item only. 

SubmitForm(releasedFormEdit);

// Declare _Record outside the With statement
Set(
   _Record,
   First(Filter(adminstock, ID = releasedFormEdit.LastSubmit.ItemDescription.Id))
);

// Now use the _Record variable
With(
   {
      _Record: _Record,
      _Released: Value(releasedFormEdit.LastSubmit.ReleasedQuantity)
   },
   // Check if _Record is blank
   If(
      IsBlank(_Record),
      Notify("Record not found", NotificationType.Error),

      // Deduct ReleasedQuantity from TotalQuantity in adminstock
      Patch(
         adminstock,
         _Record,
         {
            TotalQuantity: _Record.TotalQuantity - _Released
         }
      );

      ResetForm(releasedFormEdit)
   )
)

[WarrenBelz]

Hi @PowerFlames ,

You need to set a Variable when initiating a new item

NewForm(releasedFormEdit);
UpdateContext({varNewItem: true})

then submit the Form and put this OnSuccess of the Form

If(
   varNewItem,
   With(
      {
         _Record,
         LookUp(
            adminstock, 
            ID = Self.LastSubmit.ItemDescription.Id
         ),
         _Released: Value(Self.LastSubmit.ReleasedQuantity)
      },
      If(
         IsBlank(_Record),
         Notify(
            "Record not found", 
            NotificationType.Error
         ),
         Patch(
            adminstock,
            _Record,
            {TotalQuantity: _Record.TotalQuantity - _Released
         };
         ResetForm(Self)
      )
   );
   UpdateContext({varNewItem: false})
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[PowerFlames]

Hello @WarrenBelz ,  I appreciate your ongoing support. The new form code is functioning well, and it deducts correctly. However, based on the attached snapshot, when I attempt to select an item and save the form without making any changes, it adds it as a new item without the quantity. Additionally, if changes are made to the item, those changes do not seem to take effect and adds as new item. Any insights or assistance on this matter would be greatly appreciated. Thank you!

NewForm(releasedFormEdit);
UpdateContext({varNewItem: true});

SubmitForm(releasedFormEdit);

If(
   varNewItem,
   With(
      {
         _Record: LookUp(
            adminstock, 
            ID = releasedFormEdit.LastSubmit.ItemDescription.Id
         ),
         _Released: Value(releasedFormEdit.LastSubmit.ReleasedQuantity)
      },
      If(
         IsBlank(_Record),
         Notify(
            "Record not found", 
            NotificationType.Error
         ),
         Patch(
            adminstock,
            _Record,
            {TotalQuantity: _Record.TotalQuantity - _Released}
         );
         ResetForm(releasedFormEdit)
      )
   );
   UpdateContext({varNewItem: false})
)

[WarrenBelz · Accepted solution]

@PowerFlames ,

You need to set the Variable when you initiate the new form - not on the top of that code (which is for when the user has finished inputting the new record).

[PowerFlames]

Worked Thank you @WarrenBelz