Reminder before the exp. date

[koray1]

Hello,

My sharepoint list is like below.

There are 2 things I want to do.

1-) SKT column means expiration date. I want it to send an e-mail 7 days before the expiration date.

2-) There are lot numbers for the material codes in the titer column. If the Total_Stock total in all lots of a material code is less than the number in Safety_Stock, send a notification mail.

I checked many topics in the forum, I tried, but unfortunately I could not. I will be grateful if you could help me.

Regards.

[Sundeep_Malik]

Hey @koray1 '

You will have to make two different flows for this:

1) For Expiration Date:

Run a Recurrence flow that runs daily once.

Keep the filter query of get items as:

Expression above: 

addDays(utcNow(),7,'dd.MM.yyyy')

Full filter query, you can just copy the below in your filter query:

SKT eq '@{addDays(utcNow(),7,'dd.MM.yyyy')}'

2) For 2nd flow do this:

Keep the get items filter query empty. Take a compose and store the the first value of Safety_Stock like this:

put the dynamic field of that column here. 

Though I am little worried in your picture above I can see some values are empty for that column. And you might have to convert it to an integer by writing: int(first(dynmicfield))

After this use a condition like this:

This will just check if the total number of rows in the list is less than safety stock, send a reminder.

I hope this will help. 🙂

If you liked the explanation, you can give a thumbs up and if this was the solution you required, you can accept this as the solution. 🙂

[koray1]

Hello @Sundeep_Malik 

Thanks a lot for your help.

I'm new about poweautomate. I guess that's why I couldn't. 
The flow is giving the following error and I don't know how to email these notifications to me.

Step 2 was again very difficult for me. Isn't there an easier way to do this?

Regards.

[Sundeep_Malik]

@koray1 

Can you show what you have written in get items for Flow 1 in the filter query?

For flow 2 cant think of a method currently.

Tagging some people, who might help.

@Pstork1 

@grantjenkins 

@v-liwei-msft 

@v-yujincui-msft 

@v-jefferni 

@v-chengfen-msft 

[koray1]

Hi @Sundeep_Malik 

addDays(utcNow(),7,'dd.MM.yyyy')

[grantjenkins]

@koray1 

For 1)

1. Is your SKT column in SharePoint a DateTime field type, or Single line of text?

For 2)

1. Did you want to check these totals once a day? Just wondering how often you would be sending that notification.
2. Are you checking the total grouped by Material Code?
   1. If so, how would you want the email to be displayed if say there were 5 Material Codes that had Total Stock less than Safety Stock?

[koray1]

Hi @grantjenkins 

For1)

1. Is your SKT column in SharePoint a DateTime field type, or Single line of text?

Yes. SKT column is Date and Time

For 2)

1. Did you want to check these totals once a day? Just wondering how often you would be sending that notification.

- Time is not important. I want it to send an e-mail as soon as the Total_Stock column falls below the number written in Safety_Stock.

Are these grouped by lot number - for instance you would check the totals for 423538, the totals for 423534, etc.

- There is no grouping. There may be different lot numbers for each "titer". For example, there are 2 different lot numbers for 423538. The app does not give automatic sum of them. I don't know how to calculate them. But if it is difficult to calculate, we can proceed independently of the lot number.

And if they are grouped, what would the email look like if there were say 5 lots that had Total Stock less than Safety Stock?

- It should have been, safety stock 480 for 423538 in the list but your stock is 400. Check this out. I wish it was 🙂

[grantjenkins]

@koray1 Can you do what @Sundeep_Malik mentioned about the formatDateTime, but set the format to 'yyyy-MM-dd' and see if that works.

[grantjenkins]

@koray1 

For the Total Stock / Safety Stock we can definitely build what you're after (including totals), but just wanted to make sure I've got it right.

You mentioned that you wanted an email as soon as the total goes too low. I'm assuming you want to check the total every time you update an item - so the trigger would be something like when item created or modified.

Then say if you update the item with Lot Number 2069960 (which belongs in Titre 423538), you will want to get the sum of Total_Stock across all records with Titre 423538.

Then with Safety_Stock, is 480 the total for Titre, or Lot Number? I mean are we checking to see if the total of Total_Stock is less than 480 in this instance or less than 480 + 480?

On another note, Safety_Stock has a lot of empty fields. If empty, do we just default to 0? Or will there always be a number in there, but just not in your example?

[koray1]

Hi @grantjenkins 

Flow did not error now. But he needs to email me this process. How do we do this?

[koray1]

hi @grantjenkins 

Absolutely right. The trigger should run when a new titer and lot number are created or Total_Stock is changed.

I made a sample table below for calculation, I think it will be better understood.

Thank you for help!

Regard.

[grantjenkins · Accepted solution]

That makes complete sense now 🙂

For this example, I'm using the following list:

Full flow below. I'll go into each of the actions.

When an item is created or modified will give us the item that was created or modified.

Get items will return items in the list where the Title is equal to the Title of the item that was created or modified. Or more precisely, items that have the same Material code.

XML is a Compose action. Rather than looping over each of the items and adding the totals, I'm going to use XPath which is much more efficient (no need for a loop). To use XPath, we need to convert our JSON to XML. The expression used is below, including adding a root element to ensure its valid XML.

xml(json(concat('{"root": ', outputs('Get_items')?['body'], '}')))

Total Stock is a Compose action that uses XPath to calculate the sum of all the Total Stock of the current Material code items. The expression used is:

xpath(outputs('XML'), 'sum(//value/Total_Stock)')

Safety Stock is a Compose action that gets the first Safety Stock value since they are duplicated across all our items. This assumes that there will always be a number entered in the Safety Stock column.

first(outputs('Get_items')?['body/value'])?['Safety_Stock']

We then have a Condition to check if Total Stock is less than Safety Stock.

If Total Stock is less than Safety Stock, we send an email.

Hopefully that's what you're after.

[koray1]

Hello @grantjenkins 
Thank you very much for your effort.
Where am I going wrong? 😞

[grantjenkins]

In your Filter Query on Get items you should be testing with Titre, not Product No.

And for Total Stock and Safety Stock you need to add those expressions as per the screenshot below (not just pasted into the input as text):

[koray1]

It doesn't accept Titre so I changed as Title

New issue:

[grantjenkins]

Are some of your Safety Total columns blank (don't have a number)?

[koray1]

Hi @grantjenkins 

Columns don't actually have blank lines. I also checked the column types. All of them are number type.

The Total_Stock row has numbers 0. Could it be related to that?

[grantjenkins]

Values of 0 wont' cause an error.

Are you able to check the internal column names for your Total Stock and Safety Stock and make sure those are the names you're using in your expressions?

Also, just to confirm, you updated both the expressions for Total Stock and Safety Stock (not just pasted the text)?

[koray1]

Hi @grantjenkins 

I've checked everywhere but it keeps giving the same error.

When I run a test for Flow, it calculates Total Stock and Safety Stock correctly.

Test for product no: 423538
Total stock is:  445 (400+45)

Safety stock is: 480

but it gives an error in the condition part 😞

[grantjenkins]

When you run the flow can you click on Show raw outputs for both Total Stock and Safety Stock to ensure they are both numbers (not enclosed within double quotes).