Exploring Power Automate and Business Central: Tell me if...

Do you want to keep on top of things in Business Central?

In this short post I'll be looking at the Find Records (V3) action and using it to meet the following business requirement:

Tell me if any of purchases haven't arrived in time. 

In terms on the nitty gritty we do need to agreed on what this means in BC, so we will say if the following are all true - then we need to fire a notification.

  • The Purchase Order has a status of Released (Purchase header)
  • Quantity Received is less than the quantity ordered (Purchase line)
  • The Expected Receipt Date is in the past (Purchase line)

For this approach we will use a Scheduled flow - as we just want to check all the PO's each day once Run flows on a schedule in Power Automate - Power Automate | Microsoft Learn.

We need to evaluate some logic from the header and then the lines. It just so happens the Find Records (V3) API for purchase lines also requires us to pass it one PO header at a time. This just means we have 2 Apply to Each loops to deal with, first the PO header, then the lines.

Here is a look at each step

Set the recurrence - once a day at 9am please...

Find all the Released PO's. A very nice gotcha in Power Automate is the status string. You need to use "Draft' for 'Open in BC' and 'Open' for 'Released in BC'. That's why I have status eq 'open' in the ODATA filter:

Now for each returned record we find the PO lines. Passing the "id" from the prior step into the Apply to each action. The purchaseOrderLines API demands that we only look up 1 PO at a time and we need to tell it which one - so I've passed the header id in the ODATA filter.  

Now is the next apply to each where I want to evaluate the how much is left to receive and if we are past the expected date yet. Its possible this could be done with the ODATA filter, but I've used a control condition as that seemed easer to me.

The Expected Receipt Date is in the format yyyy-MM-dd, so we should format the UTC time the same which you can do with an expression formatDateTime(utcNow(), 'yyyy-MM=dd').

Finally for any rows where these conditions are both met lets fire a notification to the team! The whole reason for this was to make things quick an easy - so naturally we will give some context and a link to the users can get directly to the document in question 

Over in teams, a bit spartan, but you get the idea..