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

Adding value to matching record

@EricLott 

One last Question for ya! Attached is the code that works in the program. As of now it subtracts the appropriate amount from the starting location and adds the appropriate amount to the end location. The only issue I have is lets say I have 100 of part ABC in location 100-1. I also have 100 of part ABC in location 200-2. Right now when I move it they show up as separate records. How can I get the value to add to a location if it already possess the part?? Let me know what you think. 

7 REPLIES 7
EricLott
Resident Rockstar
Resident Rockstar

I don't see any code attached, but it sounds like the If() statement is always executing the process for if a part/location pair doesn't already exist. You can do If(CountRows(Filter(YourDataSource,And(PN=PartNumber,Location=NewLocation)))=0,AddEntry,UpdateEntry)

So sorry about that, Now it is attached! Is that something I should add into the collect function??

 

CODEthatWorks.PNG 

No, swap the Lookup(InventoryData,Total=StartLocation.Text && PN + PartNumber.Text,PN)

with this:

CountRows(Filter(YourDataSource,And(PN=PartNumber.Text,Title=StartLocation.Text)))=0

 inputed the code and it I get the message that it wont work with large data sets (Which is ok) but it says it wont work with the connector which is Sharepoint. Is there a way to create a collection of the share-point list every time the button is pressed and then updates accordingly and puts it back on the share point list? I would not know a work around for this. Maybe a clear collect to a gallery? I am worried this will be too many steps and cause the system to be slow 

 

Let me know what you think 

 

Screen Shot 2020-01-23 at 7.25.44 PM.png

 

v-xida-msft
Community Support
Community Support

Hi @MaxWendorff ,

Could you please share more details about your scenario?

Do you want to move part amount from one location to another location?

 

If you want to move part amount from one location to another location, I think there is something wrong with the Collect formula that you mentioned. I have made a test on my side, please consider modify your formula as below:

 

If(
    !IsBlank(LookUp(InventoryData, Title = StartLocation.Text && PN = PartNumber.Text)),
    Patch(   // Modify formula here. Modify QTY value in your New location within your InventoryData
          InventoryData,
          LookUp(InventoryData, Title = NewLocation.Text && PN = PartNumber.Text),
          {
             QTY: QTY + Value(QuantityOut.Text)
          }
    );UpdateIf(
               InventoryData,
               Title = StartLocation.Text && PN = PartNumber.Text,
               {
                 QTY: QTY - Value(QuantityOut.Text),
                 'Last Scan': Now(),
                 User: "Name"
               }
    )
)

 

Please consider take a try with above solution, check if the issue is solved.

Note: The Collect function is used to add a new record into a data source rather than modify existing records in data source.

 

More details about the Patch function, please check the following article:

Patch function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your response it is much appreciated! I will be trying your code after writing this but I wanted to give you some more information on exactly what I am doing!

 

The warehouse I am building this fro has many Locations.

Each Location is a spot in the factory where a certain Part Number goes 

Locations can have more than one Part Number 


**For example Location 100-1 could hold part number ABC as well as GHJ or more.**

 

Part Numbers can exist in more than one location 

 

**For example part number ABC could be in location 100-1 and 200-3.**

 

For my scenario lets say that part ABC is in location 100-1 and has 1000 pieces. My job as a material handler is to move only 200 pieces to location 200-1. Currently the part number is not in 200-1 so I need a new record to be made listing that part ABC is in 200-1 and there are 200 of it. I also need the quantity to subtract from location 100-1. 

 

So in the end 200-1 would have 200 of part ABC and 100-1 would have 800. 

 

On the flip side lets say I wanted to move the same material to the same location except this time part ABC existed in location 200-1. Instead of creating a new record I need the quantity to add to the existing record. 

Let me know if you have any questions and I appreciate the help! 

Just getting back to you now! I just sat down and worked with the code and it semi works. My only issue is that on the patch function it highlights "QTY" and says its invalid. I need it to add the records current quantity with the "QuantityOutput.Text" field. It seems if patch cant pull the column data. Is this a code error or a limitation on patch. The UpdateIF function works perfectly in this syntax. Let me know when you get the chance!

 

TestTest.PNG

 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,545)