List Sync Part 1

Have you ever needed to sync a list one way in Power Automate? If you’re reading this you probably need to and that’s what this blog post is for!

Say you need an inventory of all the fruit you have, some being sold and some not. You want your salespeople to only have a list of the ones not sold, however, so that they can sell stuff you actually have. Sound interesting enough? Let’s build it!

Let’s create the lists first- the first one is the Inventory List

Column Name Column Type
NameSingle Line of text (Title)
Price PerCurrency
AmountNumber
Expiration DateDate and Time
IsSoldYes/No
Next ShipmentDate and Time

Next is the Child list, Current Inventory

Column Name Column Type
Name Single Line of text (Title)
Price Per Currency
Amount Number
FruitID Number

The second step is to decide the trigger. Now, there are four things that could happen-

  1. There is a fruit already listed and the IsSold column would be updated to true
  2. They add an item already sold with the IsSold column as true to the list
  3. They add an item (not sold) to the Inventory
  4. They update an item ( not sold ) in the Inventory

On item creation in the Inventory list the workflow should create a corresponding item in the Current Inventory list. On item update in the Inventory list it should update all of the fields in Current Inventory to their new values. If the IsSold value is true, no matter if it’s on update or creation, it should prevent that item from being in the Current Inventory list.

So now that we have the potential scenarios down we can decide the trigger, and the best option is ‘When an item is created or modified’. Create your workflow. I named mine Fruit Inventory Sync and selected our trigger.

Now we need to figure out if it was an update or creation of an item to trigger the workflow running in our workflow. How this works is when someone updates/creates an item in the Inventory list it gives us the item ID. When we create an item in the Inventory list it should create an item in the Current Inventory list with the column FruitID being the item ID for the created item. If it was an update, however, we can get a match and update this item.

The first step to figuring out what the workflow was caused by is to get the items in the Current Inventory. If there isn’t a FruitID with the trigger item ID they made an item. If there is a match that means it was an item update.

Just getting the items doesn’t get if it was an update or a creation- we have to sort through each item and check if the Fruit ID matches the item Id from the trigger. Add the action ‘Apply to each’ and fill in the ‘Select an output from previous steps’ to be the dynamic content ‘value’. This is found in the ‘Get Items’ section of the Dynamic content pop-up and this will contain every item in the list.

Inside this apply to each we want to check if there is a FruitID match- we need to add a condition. The first input should be the current item’s column Fruit ID. Select add dynamic content in the input box and add in ‘FruitID’ located in the ‘Get Items’ section. Note: this will not be located in the apply to each section.

The middle comparison selection should be ‘is equal to’. For the last input, select the ‘ID’ from the dynamic content popup located in the ‘When an item is created or modified’ section.

Now that we know what the change is- how are we going to use this information? We’re not storing it anywhere to sort our actions later currently so we definitely need to do that. Before the Apply to each action add in the action ‘Initialize variable’. The Name will be ‘IsModified’, Type is Boolean, and leave the initial value empty.

In the ‘If Yes’ path ( it was an update ) create the action ‘Set Variable’ and set the variable ‘IsModified’ to true. Make it false in the ‘If No’ section* Make sure you’re setting them to the lowercase true and false.

So, now that we know it was updated ( If Yes ) or created ( If No ) we can get if the item is Sold. The next steps are:

  1. Add a condition in the If Yes path fill in the options like this: add the dynamic content ‘IsSold’, is equal to, and true.
  2. We need to do something with this information so, before the Apply to each, add in another initialize variable action. This will be name ‘IsSold’ ( so unique, right? ), type is Boolean, and then leave the initial value blank again.
  3. In the condition set the IsSold variable to true / false ( make sure it’s lowercase ) based on the condition path
  4. Repeat step 1 and 3 for If No path

Now that we know if the item is sold, created, or modified, we still need to store the corresponding items’ ID. Using it we will be able to update/delete/create an item in the Current Inventory list. Create a variable before the Apply to each action. This will be named ID, the type Integer, and the initial value empty.

Using the Data

Now that we’ve gotten the Apply to each done and stored all of our data, we can use it to update the Current Inventory list. After the Apply to each add in a condition. The condition should be filled in: IsModified variable, is equal to, true.

Add a condition to both paths of the condition- The condition should be filled in: IsSold variable, is equal to, true.

In the IsModified IsSold side add the action ‘Delete item’. Select the site, list, and then use the ID variable for the Id.

In the IsModified isn’t Sold side add the action ‘Update item’. Select the site, list, use the ID variable for the Id, and then fill in all of the columns.

In the isn’t Modified isn’t Sold side add the action ‘Create item’. Select the site, list, use the ID variable for the Id, and then fill in all of the columns. Leave the isn’t Modified IsSold side empty- we want to prevent that item from being added if it’s already bought.

Summary

That’s it! Now you can easily manage your fruit data and make accurate sales. We thought of every potential scenario and achieved our management goal. I hope this helped improve you daily processes! If you have ideas for another blog comment below!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: