Compare earliest date with conditions?

[matt_stewart1]

Continuing the discussion from Formula to choose Earliest Date from list of 6 dates:

I am trying to adapt the latest solution from @kuovonne regarding comparing dates and finding MIN or MAX. However I am trying to have some filters to include or not include these dates to compare. In other systems I can use Collect function to determine if a value should be considered or not in this comparison, but im not sure how to do here.

I have a table of records, where each record is a product.

Each product record has numerous fields associated with different types of purchase orders (8 different order types) and their respective due date, review status, and an approval status.

Review status is managed by a variety of team members and indicates if its ready for review or waiting on something.
Approval Status is managed by management to track approval status or N/A.

I am trying to look at these 8 orders per product, and:

• 1 column have the date of the earliest order due that is

  • OR
    • review status = “R”
    • AND
      • approval status != “OK to PO”
      • approval status != “N/A”

• 1 column have the associated order due name (field name) of the earliest order due