Cannot compare List with Text (Lookup formula)

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

Screenshot of the expression, please.

1 Like

This is as far as I have got, image

I understand the syntax of the LOOKUP but I dont want to check the values that match each other, I just want to check that the line references in the related order details, have a status of order short.

Perhaps a lookup formula is required in addition to something else?

That or else my values are incorrect currently

1 Like

If I understand correctly, the following may be sufficient for your action condition:

IN("Order Short", [Related Order Details][Request_Status])

This would be instead of the LOOKUP() expression you tried.

The above answers the question, “do any of the related order details have an order status indicating a short order?”