Answered

Adding to Existing Quantity

(0) Share

Report

Posted on by KarlAddi

So I am building an Inventory management app and currently when you want to add an item to the app you fill out a form and I use

SubmitForm(EditForm)

to update the list.

This form includes the name, part number, location and quantity. What I want is that when the form is filled out, the user clicks submit and IF the part number he has entered already exists in the excel file, the quantity he entered will just be added to the existing quantity in the excel file.

I'm sure this isn't as hard as I think it is but I have tried using an If statement as shown below but it hasnt worked.

If(LookUp(Table1 , 'PartNumber' = PNCard.Text),{Qty:Value( Qty.Text +QtyInputCard.Text))}, SubmitForm(EditForm3);

I am aware this syntax is way off but I'm hoping you can get an Idea as to what I am thinking

Any help will be greatly appreciated, Thanks 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Hi @KarlAddi

Use Lookup to get the item for the same part number and same Quantity
Get the PartNumber from the Lookup.
If Partnumber exist it means part number exists
Adding !
The form will submit if no part number and qty

If(!IsBlank(LookUp(Table1 , 'PartNumber' = PNCard.Text && Qty = QtyInputCard.Text).PartNumber), SubmitForm(EditForm3));

My Sample for Reference
If( !IsBlank( LookUp( IssueTracker, Title = Title1.Text && Qty = Value(Qty.Text) ).Title ), SubmitForm(EditForm1) );

Was this reply helpful? Yes No
KarlAddi 53 on at

Like (0)

Report

Hi thanks for your reply 🙂

That's not exactly what Im looking for. So I want to check by just the part number and if that part number exists, what ever the user has input for the quantity on the form, I want to add that quantity to the current quantity we already have on the excel file.

Was this reply helpful? Yes No
StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Hi @KarlAddi

Get Existing Item
Get the Qty
If item found , add to existing qty with current qty

ClearCollect( GetExisting, LookUp( IssueTracker, Title = Title1.Text ) ); Set( GetQty, If( IsBlank(GetExisting) || IsEmpty(GetExisting), 0, Value(First(GetExisting).Qty) ) ); Patch( IssueTracker, If( !IsBlank(GetExisting.Title), LookUp( IssueTracker, Title = Title1.Text ) ), {Qty: Value(QtyDataCardValue.Text) + GetQty} );

Was this reply helpful? Yes No
Verified answer

StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Hi @KarlAddi

Updated with your table and column names
This patch command creates a new record if not found

ClearCollect( GetExisting, LookUp( Table1, Title = Title1.Text ) ); Set( GetQty, If( IsBlank(GetExisting) || IsEmpty(GetExisting), 0, Value(First(GetExisting).Qty) ) ); Patch( Table1, If( IsBlank(GetExisting.Title), Defaults(Table1), LookUp( Table1, Title = PNCard.Text ) ), { 'PartNumber': PNCard.Text, Qty: Value(QtyInputValue.Text) + GetQty } );

Note: We need to add other columns to this patch command

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@KarlAddi

You will want to take care of this in the OnSuccess action of your form rather than before you submit form. This adds a level of error management in case there is a failure in the submit.

In your OnSuccess, set the Formula to the following:

With({_item: LookUp(Table1, 'Part Number'=Self.LastSubmit.'Part Number')}, Patch( Table1, Coalesce(_item, Defaults(Table1)), {'Part Number': Self.LastSubmit.'Part Number', Qty: Coalesce(_item.Qty, 0) + Self.LastSubmit.Qty } ) )

This will increment the Qty of the existing part, or add the part and set the Qty if it does not exist.

Note: The above formula assumes that the names of the columns in your EditForm are the same as in your Table1 records (i.e. 'Part Number' and Qty), if they are not, adjust accordingly.

Also, importantly, this references the LastSubmit property of the EditForm which should be used rather than referencing the controls that are in the form as their state may change. LastSubmit is a snapshot of the record that was last submitted.

I hope this is helpful for you.

Was this reply helpful? Yes No
pablik01 on at

Like (0)

Report

How to change quanity in collection?

Was this reply helpful? Yes No