Adding value to matching record

[MaxWendorff]

@Anonymous 

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. 

[Anonymous]

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)

[MaxWendorff]

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

 

[Anonymous]

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

[MaxWendorff]

 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 

[v-xida-msft]

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,

[MaxWendorff]

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! 

[MaxWendorff]

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!