Not sure if this is the right place to post, but appreciate direction if not the case.

Scenario: Two tables. 1) Main table with product ID and details; 2) related table with orders of Product ID. Relationship is one-to many, similar to invoices.

I create a summary field (Number of Orders) in the related table to count the number of Product ID records. The main table layout has a portal to the related table. Also on the main layout is the summary field to show the number of orders for that product ID.

My desire is to be able to search Number of Orders to see which Product ID records have multiple orders. However, you can't search on a summary field, so the field is not available with Find.

I've tried created a calculated field (countof RelatedTable::ProductID) but the results are not correct.

I've tried using the summary field AND a "helper" calculation field to just pull the summary field contents but it doesn't work.

I'm on Windows 11, FM 21

Thanks for any help!

First thing, this part is not clear:

1 hour ago, Clayton King said:

I've tried created a calculated field (countof RelatedTable::ProductID) but the results are not correct.

If the calculation field is defined in the parent Products table as:

Count ( Orders::ProductID )

then it should accurately reflect the number of child orders of each product. If you're getting a different result, then something doesn't fit your description. Seeing a file that demonstrates this issue would help to determine the cause.

Now, the truth is that you don't need such calculation field to accomplish the stated task of:

1 hour ago, Clayton King said:

to see which Product ID records have multiple orders.

You can search the Orders table for ! (duplicate values) in the ProductID field. Then do:

Go to Related Record [ Show only related records; Match found set ]

to get a found set of only parent products that have multiple orders.