Cannot compare List with Text (Lookup formula)

[Sarah_Keown]

Hi, I’m attempting a lookup formula, which I have done successfully in the past but this particular scenario isn’t working for me.

I have an Ordering App, and I want to send out automatic emails to suppliers when we have short/incomplete orders.

The Orders table is the parent, the Order Details is the child table.

One order may have many order details (I call them Requests).

I only want the supplier to be contacted on two conditions.

The Order Status (Order Tbl), is incomplete
There is at least 1+ Requests assigned to that order, with a shortage. Request Status will be Order Short.

Here is the layout for better understanding.

In this example, I want that automatic email to send.

I’m having an issue trying to identify that the request status of the items, within the related details is short