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

Matching Records between user imputed data and a share point list and updating them!

Hello!

I am building a warehouse management app and I am stuck on an issue! I have created a test environment and test app in hopes to simplify my question! Below is a share point list where the inventory data will be held called "InventoryData" It has 3 main columns- "Location" This identifies where in the factory a part is. "PartNumber" This identifies what part something is and finally "Quantity" This is how much of a part number is in a certain location. I have filled the below fields with test data:

InventoryData.PNG

 

Now what I need this app to do is to be able to move inventory around based on user input. The first field below "Start Location" Is a lane in the factory where a part sits and the "Part Number" field is the part number in that lane. The "Quantity" field represents how much much of a part you are taking out of a location. The final field "New Location" is where the Part number and user specified quantity is going too. 

TestAppScreen.PNG

 

So my sample problem is I want to move 20 pieces of part ABC from location 100-1 to location 200-1. (Different part numbers can exist in the same location) I am unsure what function to use to compare where a part is at and then change its Location ID! Attached is some code that i am testing. If anyone can point me in the right direction it would be much appreciated!!

TestCode.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

That's correct. There's an UpdateIf() in the code above that removes the qty from the first location.

View solution in original post

4 REPLIES 4
EricLott
Resident Rockstar
Resident Rockstar

Try this:

UpdateIf(InventoryData,And(Location=StartLocation,PN=PartNumber),{QTY:NewQty,Location:NewLocation})

 

EricLott
Resident Rockstar
Resident Rockstar

Since UpdateIf() will only update existing records, you'll need to add some additional logic for if a PN/Location combo is not already in your list. That would look something like this

 

If(IsBlank(Lookup(InventoryData,And(Location=NewLocation,PN=PartNumber),PN)),
Collect(InventoryData,{PN:PartNumber,QTY:NewQuantity,Location=NewLocation});
UpdateIf(InventoryData,And(PN=PartNumber,Location=OldLocation),{QTY=QTY-QtyChange}),
//UpdateIf() above
)

 

 

Thank you so much for the response. What I am getting from this code is that its first checking the database to see if the part number and location are in the database. If they are not it is taking data from those fields and adding it in as a new record. If it is it is updating based off the same input? If so that is what I need. 

 

The only issue I am having is that if I only move lets say 20 of a part from one location to another I need the first location to subtract 20 and a new record to appear in the second location? Would i need to add a patch function somewhere or would I use collect? Let me know what you think  

That's correct. There's an UpdateIf() in the code above that removes the qty from the first location.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,883)