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.

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (5,237)