I have two tables: Customers and Orders.

The products that can be on orders are:

Widget A and widget B. Each kind of widget can only be ordered once per customer.

The order status can be backordered or shipped

I need a report or export where each customer appears only once, in a single row, and with a column for each of the Widgets ordered. Like this:

Customer, Widget A Status, Widget B Status

Joe Customer, backordered,shipped

I have tried everything I can think of:

o A calculated field for the status of each widget in Orders

o A calculated field for the status of each widget in Customers

o A report or export with grouping

Every time, for customers that have ordered both widget types, the status for only one of the widget types shows up in the report or export.

What kind of field do you have for the status of widget A and widget B? It sounds like you'd need a separate field for each one. Like status_A and status_B. Then they should both show up on the report.

They are separate text fields. It's as if the relationship between Customers and Orders only looks at the first related order it finds.

Calc for Widget A status field in Customers:

If (Orders::Type="Widget A";Orders::Status)

Calc for Widget B status field in Customers:

If (Orders::Type="Widget B";Orders::Status)

I have a customer who has ordered both Widget types, yet their Widget B status field is blank.

It's as if the relationship between Customers and Orders only looks at the first related order it finds.

That's exactly what happens when you use a related field in a calculation - the data comes from the first related record (in the sort order of the relationship).

What should the status be when customer has ordered both types?