Updating Current Items or Added New Items to Sharepoint List

[4wordsback]

I have 2 SharePoint lists: One (MASTER_ITEMS) with a list of Items and Item Sku, and one (LAST_ORDER) that contains item_skus  and dates and qty.

I want to Update the second list (LAST_ORDER) everytime an order is placed.

Currently i am gathering items from (MASTER_ITEMS) into a collection (colCart), then i want to do a bulk update (LAST_ORDERS) with (colCart) by checking to see if the item sku is present and if so, updating the QTY and Date only, however if the item is not present i would want it to create a new item and update the item_sku, qty and Date.

Please assume that all ITEM_SKU records are Unique.

MASTER ITEM LIST

ITEM	ITEM_SKU
BLACK SHIRT	BLKSHRT
WHITE SHIRT	WHTSHRT
RED SHIRT	RDSHRT
BLUE SHIRT	BLUSHIRT

colCart

ITEM	ITEM_SKU	DATE	QTY
BLACK SHIRT	BLKSHRT	Now()	2
RED SHIRT	RDSHRT	Now()	4

LAST_ORDER

ITEM_SKU	QTY	DATE
WHITE SHIRT	1	7/19/2021
BLACK SHIRT	4	7/15/2021
BLUE SHIRT	3	7/15/2021

HOW DO I GET?

LAST_ORDER

ITEM_SKU	QTY	DATE
WHTSHRT	1	7/19/2021
BLKSHRT	2	Now()
BLUSHIRT	3	7/15/2021
RDSHRT	4	Now()

Thanks for your time!

[RandyHayes · Accepted solution]

@4wordsback 

This is done easily if you focus more on the ID of the record.  The ID is the primary key and is always unique.

That said, when you collect items in your cart, make sure you are collecting the ID of the record in LAST_ORDER.  Now, if that record does not exist, then the ID will end up being blank...this is important.

If your collection is based on the exact column names of LAST_ORDER, then your final statement to update and create can simply be:  Collect(yourDataSource, yourCollection)

This is not your case though based on the schema of your cart collection, so there is some prep to get to this.

First, for your cart collection - when you collect a row to your collection, change to the following:

Collect(colCart,
    {ITEM: yourItemColumnValue,
     ITEM_SKU: yourItemSKUvalue,
     QTY: yourQtyValue,
     DATE: Now(),
     LAST_ORDER_ID: LookUp(LAST_ORDER, ITEM_SKU=yourItemSKUvalue, ID)
    }
)

Now, with the ID in place, when you submit, the formula would be the following:

Collect(LAST_ORDER,
    ForAll(colCart,
        {ID: LAST_ORDER_ID,
         QTY: QTY,
         DATE: DATE
        }
    )
)

This will create records with blank ID's and update records with ID's.

I hope this is helpful for you.

[4wordsback]

Sorry, It is not working. There is no Error, but the list is not updating at all.

Sorry, i maybe wrong but isn't Patch suppose to be used for updating sharepoint, not collect?

[RandyHayes]

@4wordsback 

What are your formulas at this point?  

No, Collect, Patch, Update and UpdateIf all perform record updates.  In your scenario either Collect or Patch can be used.  I find collect to be more performant and tend to favor it.  Results are the same though.

[4wordsback]

//Inside Gallery Plus Icon
Collect(
    colCart,
    {
        ATTN: ThisItem.ATTN,
        DEPARTMENT: ThisItem.DEPARTMENT,
        ITEM: ThisItem.ITEM,
        SUBCATAGORY: ThisItem.SUBCATAGORY,
        DATE: ThisItem.DATE,
        DESCRIPTION: ThisItem.DESCRIPTION,
        SUPPLIER: ThisItem.SUPPLIER,
        ITEM_SKU: ThisItem.ITEM_SKU,
        UNIT: ThisItem.UNIT,
        QTY: Value(TextInput2.Text),
        PRICE: ThisItem.PRICE,
        SUBTOTAL: ThisItem.PRICE * Value(TextInput2.Text),
        URGENT: ThisItem.URGENT,
        USER: ThisItem.USER,
        OrderID: ThisItem.OrderID,
        ID:ThisItem.ID,
        LAST_ORDER_ID:LookUp(LAST_ORDER_PO, ITEM_SKU=ThisItem.ITEM_SKU, ID)
    }
);



//On Button:
Collect(LAST_ORDER_PO,
    ForAll(colCart,
        {
        ID: LAST_ORDER_ID,
        ITEM_SKU:ITEM_SKU,
        QTY: QTY,
        DATE:Now()
        }
    )
);

[RandyHayes]

@4wordsback 

Are you getting an ID in the collection for LAST_ORDER_ID ?

Also, you can tighten up your initial collect by modifying the formula to the following:

Collect(
    colCart,
    Patch(ThisItem, 
       {
        QTY: Value(TextInput2.Text),
        SUBTOTAL: ThisItem.PRICE * Value(TextInput2.Text),
        LAST_ORDER_ID:LookUp(LAST_ORDER_PO, ITEM_SKU=ThisItem.ITEM_SKU, ID)
       }
    )
);

I only mention because you are repeating the ThisItem.column for columns that have no no changes.  In the above, the original values and columns are patched with the updates and then added to your collection....just a lot less typing!

[4wordsback]

Thank you for the tip!, i just added it.

Currently LAST_ORDER_ID is empty so there is nothing to collect.

[RandyHayes · Accepted solution]

@4wordsback 

Actually, I just had a "duh" moment - yes Patch is preferred in this case because we are updating.  Collect will actually assume all new records (not enough coffee in my blood yet!)

So, your formula should be:

Patch(LAST_ORDER_PO,
    ForAll(colCart,
        {
        ID: LAST_ORDER_ID,
        ITEM_SKU:ITEM_SKU,
        QTY: QTY,
        DATE:Now()
        }
    )
);

This will do as prescribed - update if exits (ID) and create if not (blank).

However your are stating now what your LAST_ORDER_ID in the collection is empty.  This should not be the case on existing SKU's.

Click your plus Icon on an item that there is an existing SKU in the LAST_ORDER_PO list.  Then look at the collection and see what the LAST_ORDER_ID is...It should have a value.

[4wordsback]

This last Modification worked!

I had to add a Title field, but i am getting the results i want!

Thank you for your help!

///FINAL SOLUTION

Patch(LAST_ORDER_PO,
    ForAll(colApr,
        {
        ID: LAST_ORDER_ID,
        Title:ITEM_SKU,
        ITEM_SKU:ITEM_SKU,
        QTY: QTY,
        DATE:Now()
        }
    )
);

[RandyHayes]

@4wordsback 

Yes, I was not sure if you had the Title column as required, so I omitted because I hadn't seen it in any of your other formulas.

Glad it is working now!