Sharepoint Inventory list update automation

[conorsanchez]

Hi, Im trying to create a flow between 2 Sharepoint lists where when a purchase list is updated, it calculates remaining inventory (subtracts) and updates an inventory list.

The purchase list gets updated through another automation involving a form. When the purchase list gets updated the new number of devices on the purchase list needs to be subtracted from the inventory list and the inventory list updated.

   

I've started to create the flow but i'm a bit unsure on how to action the sub function (which i assume is the correct way to do this) with all of the items. I'm worried that because its several items I need check it might be a large process. I wanted to check that this flow is possible and there isnt a better way of doing things. Any help is greatly appreciated. 

 

[wskinnermctc]

Hi @conorsanchez I don't necessarily agree with how you have the Inventory List since it normally has items in rows with a quantity column. However, without product-Ids and specific references, your Inventory List in columns seems to work well enough.

The main thing you will need to recognize is how to use the InternalName or EntityPropertyName of the column. That is what will need to be typed into the expression to update the inventory list. I made a post here to go over InternalNames of columns.

I made an example Purchase List and example Inventory List. My column names are slightly different than yours but basically the same, my inventory columns begin with the "Inv_" to help with the expression.

The starting quantity of items is:

Title	Inv_HPg250G7	Inv_HPg250G8	Inv_DellLaptopB35
Inventory List Row	10	20	30

Screenshot of the Inventory list below:

Initial Inventory List - No items have been subtracted yet

Now I made an example of the Purchase list that has the first customer who placed an order.

CustomerName	PurchaseDate	HPg250G7	HPg250GB	DellLaptopB35
Steve Nash	08/30/2023	3	0	1

Steve Nash ordered 3-HPg250G7 and 1-DellLaptopB35

This new purchase order triggered the Power Automate flow-

The basic steps are 

• Trigger - When Item is Created in Purchase List
• Get Items - This will get the Inventory row from the Inventory List
• Apply to Each - Get Items values (Automatic since Get Items output is array even if only 1 row is returned.)
  • Update Item - This will update the Inventory Row in the Inventory List

Update Inventory Flow

The Update Item used the ID value from the Get Items - Inventory because we are updating that row which has the inventory quantities. The Title is from Get Items - Inventory because it is required to have (Title basically overwrites itself.)

The only difficult part is putting the correct fields into the expression that is being used. This is where the InternalName comes in to play. If you use the column display names it won't work.

This is the subtract sub() expression that will subtract the purchase order from the inventory. sub(8,1) = 7

The expression needs to be like sub({InventoryItem},{PurchaseItem})

Expression for Inventory Item Inv_HPg250G7:

sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G7'],triggerOutputs()?['body/HPg250G7'])

Expression for Inventory Item Inv_HPg250G8:

sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G8'],triggerOutputs()?['body/HPg250G8'])

Expression for Inventory Item Inv_DellLaptopB35:

sub(items('ApplytoEach-InventoryListItem')?['Inv_DellLaptopB35'],triggerOutputs()?['body/DellLaptopB35'])

You put the expression into the fx box that pops up when you click to add dynamic content:

Enter Expression into Fx box

And here my flow ran and updated the inventory list:

Title	Inv_HPg250G7	Inv_HPg250G8	Inv_DellLaptopB35
Inventory List Row	7	20	29

And here is a screenshot of the Inventory list:

Results Inventory List

Hopefully this helps your flow get set up correctly

[conorsanchez]

Thank you so much for helping with this, they way you have laid everything our really helps me understand the process. 

I've set the flow up identically to yours however I'm now having an issue with null values 

 I've had issues with null values before and used the function if(empty) but im not quite sure how to fit that in to the previous function. I did try to make sure all of the Purchase fields had a value but im still getting the error.

[wskinnermctc]

That is strange? I don't have that issue. I just tested it with purchase and inventory as 0 zero, and even made the expression backwards; but still didn't have an error.

What are the column types in your SharePoint list? Are they number or Single Line Text? My columns are Number type.

I'm thinking the expression has the incorrect column name, so it is trying to get a value from a column that doesn't exist and will be null. Like if you made the expressions then renamed the Apply to Each, the expression will be incorrect since the name didn't change inside the expression.

If you just renamed your steps, sometimes that throws off power automate. You should try deleting all of the steps and recreating them new. And if you are making yours look like mine, rename the steps right after you create each one and before you create the next one. Sometimes that helps with the references.

Can you test it with a purchase order that has an item in every column getting purchased? See if you get the same error when every column is filled with a real value that is not 0 zero. Like if a customer purchased 1 of every item.

[conorsanchez]

Thanks for getting back to me.

I've tried recreating the whole flow and trying what you have said but still getting the error. 

I'll give it another go from scratch today when i have some time and update. 

[conorsanchez]

So I've tried to recreate it from scratch exactly how you have set yours out, but still no luck.

I've tried making sure all of the fields on the purchase list have a value to rule out that reason.

I've also made sure all of the columns are set as number. 

So I'm not too sure what could be causing the issue

 

 

 

[wskinnermctc]

Can you show me 2 things. 

1st the Internal Name of  your SharePoint columns in each list Purchase and Inventory.

2nd the subtraction expressions that you are using. 

[conorsanchez]

Thanks for getting back to me again,

Here are the two column settings: 

 

 

For the expressions im just using these 3:

sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G7'],triggerOutputs()?['body/HPg250G7'])

sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G8'],triggerOutputs()?['body/HPg250G8'])

sub(items('ApplytoEach-InventoryListItem')?['Inv_DellLaptopB53'],triggerOutputs()?['body/DellLaptopB53'])

[wskinnermctc]

The list settings shows the DisplayName/Titles of the columns. The Internal Name can be found in the URL if you were to edit each column. See HERE

I'm thinking the Internal Name of the columns could be the only problem since I'm able to get it to work for mine. Our flows look alike, but the only thing I can't confirm is the Internal Name of your columns.

[conorsanchez]

The Internal Names are:

TestInventory

-Inv_HPg250G7

-Inv_HPg250G8

-Inv_DellLaptopB53

PurchaseList

-Inv_HPg250G7

-Inv_HPg250G8

-Inv_DellLaptopB53

-CustomerName

-PurchaseDate

All copy and pasted 

[wskinnermctc · Accepted solution]

Ok, the problem is the names being used in the expression. 

Since both of your lists have columns that begin with "Inv_" but your expressions only have one of the columns beginning with "Inv_" which are from the Inventory List:

What you put earlier

sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G7'],triggerOutputs()?['body/HPg250G7'])
sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G8'],triggerOutputs()?['body/HPg250G8'])
sub(items('ApplytoEach-InventoryListItem')?['Inv_DellLaptopB53'],triggerOutputs()?['body/DellLaptopB53'])

Notice how the last values from the triggerOutputs do not have "Inv_" for the beginning of the column name?

You need the expressions to be:

sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G7'],triggerOutputs()?['body/Inv_HPg250G7'])
sub(items('ApplytoEach-InventoryListItem')?['Inv_HPg250G8'],triggerOutputs()?['body/Inv_HPg250G8'])
sub(items('ApplytoEach-InventoryListItem')?['Inv_DellLaptopB53'],triggerOutputs()?['body/Inv_DellLaptopB53'])

Try the expressions with the column names corrected

[conorsanchez]

I have tried changing the expression to your new suggestion but I'm still getting the null error. I've reduced the items to just the one HPg250G7 to make it a bit easier. So I'm not too sure what can be wrong, the column names seem to be correct so I assume it should be working.

 

 

[wskinnermctc]

Can you show me something? Make these two HTTP Requests in a separate flow. Run the flow and then copy and paste the outputs body so I can see exactly what the columns are.

In the HTTP requests you use the List name. I think yours are the same as mine, but if not, replace where I have put "TestInventory" and "TestPurchase" in the Uri.

HTTP Request to GET sharepoint column fields

The Uri is the same for each request except for the different list name:

_api/web/lists/GetbyTitle('TestInventory')/fields?$select= description, id, Title, InternalName, EntityPropertyName, StaticName,DefaultValue, TypeAsString, TypeDisplayName, FromBaseType &$filter=FromBaseType eq false

The Headers section of the HTTP request is:

Left column: Accept

Right column: application/json;odata=nometadata

Run these http requests and then copy and paste the outputs here so I can see what it says.

HTTP Request Results

Here is the copy and paste of the HTTP Request Outputs body of my TestInventory:

{
  "value": [
    {
      "DefaultValue": "0",
      "Description": "",
      "EntityPropertyName": "Inv_HPg250G7",
      "FromBaseType": false,
      "Id": "68b308fe-5c3e-498f-8722-b1542dda78c4",
      "InternalName": "Inv_HPg250G7",
      "StaticName": "Inv_HPg250G7",
      "Title": "Inv_HPg250G7",
      "TypeAsString": "Number",
      "TypeDisplayName": "Number"
    },
    {
      "DefaultValue": "0",
      "Description": "",
      "EntityPropertyName": "Inv_HPg250G8",
      "FromBaseType": false,
      "Id": "10fe218e-7f02-40c9-a1ab-d73844562c5d",
      "InternalName": "Inv_HPg250G8",
      "StaticName": "Inv_HPg250G8",
      "Title": "Inv_HPg250G8",
      "TypeAsString": "Number",
      "TypeDisplayName": "Number"
    },
    {
      "DefaultValue": "0",
      "Description": "",
      "EntityPropertyName": "Inv_DellLaptopB35",
      "FromBaseType": false,
      "Id": "326a0ee4-e8b5-4023-828e-7f4b6f6ba6a9",
      "InternalName": "Inv_DellLaptopB35",
      "StaticName": "Inv_DellLaptopB35",
      "Title": "Inv_DellLaptopB35",
      "TypeAsString": "Number",
      "TypeDisplayName": "Number"
    },
    {
      "DefaultValue": null,
      "Description": "",
      "EntityPropertyName": "OData__CommentFlags",
      "FromBaseType": false,
      "Id": "c274cbfd-084a-4017-925f-cce50c9e3eec",
      "InternalName": "_CommentFlags",
      "StaticName": "_CommentFlags",
      "Title": "Comment settings",
      "TypeAsString": "Lookup",
      "TypeDisplayName": "Lookup"
    },
    {
      "DefaultValue": null,
      "Description": "",
      "EntityPropertyName": "OData__CommentCount",
      "FromBaseType": false,
      "Id": "d307dff3-340f-44a2-9f4b-fbfe1ba07459",
      "InternalName": "_CommentCount",
      "StaticName": "_CommentCount",
      "Title": "Comment count",
      "TypeAsString": "Lookup",
      "TypeDisplayName": "Lookup"
    }
  ]
}

Here is the copy and paste of the HTTP Request Outputs body of my TestPurchase:

{
  "value": [
    {
      "DefaultValue": null,
      "Description": "",
      "EntityPropertyName": "CustomerName",
      "FromBaseType": false,
      "Id": "1b453aeb-b6b4-4e1b-a961-12982b904c90",
      "InternalName": "CustomerName",
      "StaticName": "CustomerName",
      "Title": "CustomerName",
      "TypeAsString": "Text",
      "TypeDisplayName": "Single line of text"
    },
    {
      "DefaultValue": null,
      "Description": "",
      "EntityPropertyName": "PurchaseDate",
      "FromBaseType": false,
      "Id": "d4779e57-f58d-4a8f-a734-834cecee1e27",
      "InternalName": "PurchaseDate",
      "StaticName": "PurchaseDate",
      "Title": "PurchaseDate",
      "TypeAsString": "DateTime",
      "TypeDisplayName": "Date and Time"
    },
    {
      "DefaultValue": "0",
      "Description": "",
      "EntityPropertyName": "HPg250G7",
      "FromBaseType": false,
      "Id": "bc04241b-258d-4370-8873-2e05b45e081f",
      "InternalName": "HPg250G7",
      "StaticName": "HPg250G7",
      "Title": "HPg250G7",
      "TypeAsString": "Number",
      "TypeDisplayName": "Number"
    },
    {
      "DefaultValue": "0",
      "Description": "",
      "EntityPropertyName": "HPg250G8",
      "FromBaseType": false,
      "Id": "5b9d3085-847d-4deb-a4ac-bab058f7f6af",
      "InternalName": "HPg250G8",
      "StaticName": "HPg250G8",
      "Title": "HPg250G8",
      "TypeAsString": "Number",
      "TypeDisplayName": "Number"
    },
    {
      "DefaultValue": "0",
      "Description": "",
      "EntityPropertyName": "DellLaptopB35",
      "FromBaseType": false,
      "Id": "b6767cd5-e0b2-4909-859d-4d38dd11b266",
      "InternalName": "DellLaptopB35",
      "StaticName": "DellLaptopB35",
      "Title": "DellLaptopB35",
      "TypeAsString": "Number",
      "TypeDisplayName": "Number"
    },
    {
      "DefaultValue": null,
      "Description": "",
      "EntityPropertyName": "OData__CommentFlags",
      "FromBaseType": false,
      "Id": "c274cbfd-084a-4017-925f-cce50c9e3eec",
      "InternalName": "_CommentFlags",
      "StaticName": "_CommentFlags",
      "Title": "Comment settings",
      "TypeAsString": "Lookup",
      "TypeDisplayName": "Lookup"
    },
    {
      "DefaultValue": null,
      "Description": "",
      "EntityPropertyName": "OData__CommentCount",
      "FromBaseType": false,
      "Id": "d307dff3-340f-44a2-9f4b-fbfe1ba07459",
      "InternalName": "_CommentCount",
      "StaticName": "_CommentCount",
      "Title": "Comment count",
      "TypeAsString": "Lookup",
      "TypeDisplayName": "Lookup"
    }
  ]
}

Can you run that and show me the results. I think the issue has to be with the column names or the column type.

Here is another example of how these HTTP request function.

The only other alternative is that your Get Items which is getting the row from the Inventory List is getting the incorrect row and there is no value in it. But if your Inventory list only has 1 row, I don't know how that could happen.

[conorsanchez]

So for TestInventory I get: 

{

  "value": [

    {

      "DefaultValue": "0",

      "Description": "",

      "EntityPropertyName": "HPg250G7",

      "FromBaseType": false,

      "Id": "759bb24a-ee3d-4ca0-ac18-4c134e549ef2",

      "InternalName": "HPg250G7",

      "StaticName": "HPg250G7",

      "Title": "Inv_HPg250G7",

      "TypeAsString": "Number",

      "TypeDisplayName": "Number"

    },

    {

      "DefaultValue": null,

      "Description": "",

      "EntityPropertyName": "OData__CommentFlags",

      "FromBaseType": false,

      "Id": "c274cbfd-084a-4017-925f-cce50c9e3eec",

      "InternalName": "_CommentFlags",

      "StaticName": "_CommentFlags",

      "Title": "Comment settings",

      "TypeAsString": "Lookup",

      "TypeDisplayName": "Lookup"

    },

    {

      "DefaultValue": null,

      "Description": "",

      "EntityPropertyName": "OData__CommentCount",

      "FromBaseType": false,

      "Id": "d307dff3-340f-44a2-9f4b-fbfe1ba07459",

      "InternalName": "_CommentCount",

      "StaticName": "_CommentCount",

      "Title": "Comment count",

      "TypeAsString": "Lookup",

      "TypeDisplayName": "Lookup"

    }

  ]

}

And for PurchaseList I get: 

{

  "value": [

    {

      "DefaultValue": "0",

      "Description": "",

      "EntityPropertyName": "HPg250G7",

      "FromBaseType": false,

      "Id": "759bb24a-ee3d-4ca0-ac18-4c134e549ef2",

      "InternalName": "HPg250G7",

      "StaticName": "HPg250G7",

      "Title": "Inv_HPg250G7",

      "TypeAsString": "Number",

      "TypeDisplayName": "Number"

    },

    {

      "DefaultValue": null,

      "Description": "",

      "EntityPropertyName": "CustomerName",

      "FromBaseType": false,

      "Id": "e28ca9b6-a9d1-4469-a56c-aebf72d816b7",

      "InternalName": "CustomerName",

      "StaticName": "CustomerName",

      "Title": "CustomerName",

      "TypeAsString": "Text",

      "TypeDisplayName": "Single line of text"

    },

    {

      "DefaultValue": null,

      "Description": "",

      "EntityPropertyName": "PurchaseDate",

      "FromBaseType": false,

      "Id": "7e8315ce-ed42-4b8d-8039-cff596b7ff15",

      "InternalName": "PurchaseDate",

      "StaticName": "PurchaseDate",

      "Title": "PurchaseDate",

      "TypeAsString": "DateTime",

      "TypeDisplayName": "Date and Time"

    },

    {

      "DefaultValue": null,

      "Description": "",

      "EntityPropertyName": "OData__CommentFlags",

      "FromBaseType": false,

      "Id": "c274cbfd-084a-4017-925f-cce50c9e3eec",

      "InternalName": "_CommentFlags",

      "StaticName": "_CommentFlags",

      "Title": "Comment settings",

      "TypeAsString": "Lookup",

      "TypeDisplayName": "Lookup"

    },

    {

      "DefaultValue": null,

      "Description": "",

      "EntityPropertyName": "OData__CommentCount",

      "FromBaseType": false,

      "Id": "d307dff3-340f-44a2-9f4b-fbfe1ba07459",

      "InternalName": "_CommentCount",

      "StaticName": "_CommentCount",

      "Title": "Comment count",

      "TypeAsString": "Lookup",

      "TypeDisplayName": "Lookup"

    }

  ]

}

So I assume its the TestInventory column which has the entitypropertyname come back as 

HPg250G7 which I assume needs to have INV_ or it wont work
Which is strange because we've double checked the column name here: 

 

Thanks for getting back to me again!