Searching on summary fields

Questions
1

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!

2

Given it’s searching on the total of related records, any implementation is going to be unstored and so potentially quite slow depending on how you implement.

On products, create a calc, call it something like total_orders

on orders table, add an unstored calc, call it “foundCount” and set it to Get ( FoundCount ) - make sure it’s unstored.

Back on the total_orders, just reference the “foundcount” field through the relationship from products to orders. This will give you the count of records through the relationship.

Note that the result is obviously unstored, and so while you can search it, the relationship has to be evaluated for every product record in order to determine a result.

If you plan on using this a lot moving forward, I’d suggest storing the total orders on the product record, so that searching specifically is much faster (if you purely want to display the total, you don’t need to store it, but searching is another story..)

Easiest way is change your “total_order” calc to instead be an auto-enter calculation. Same result, but modify the calc slightly:

Let ( ~triggerField = refresh_field ; Products::foundcount )

It is referencing a new field called “refresh_field” which is simply a number field you can add. The reason I do it this way is so anytime I want the total orders value updated, I simply need to set “refresh_field” to 1. It is a nicer way to trigger the auto enter than having to always actually calculate the # of orders and store that. - let the auto enter do that work for you from its context.

… Anyways with that said, the pitfall of storing the value is you need to ensure it is updated. So the value of “refresh_field” would need to be set to 1 in circumstances like:

• New order raised for product

• Order deleted

If you use things like order statuses, and do not wish to include cancelled orders, you might need to simply adjust the relationship to include a predicate that excludes cancelled orders…

3 Likes
3

Thank you! This is EXACTLY what I was trying to do! It works perfectly, and I took your advice and used a trigger field.

1 Like
4

What Weetbicks said :slight_smile:

A few suggested enhancements;

  1. The trigger field does not have to have anything to do with the actual calc itself; it is there to force the calc to evaluate on a change.
  2. (personal pref) make the trigger field a timestamp, then you can inspect it to see when it was last triggered.
  3. Set the trigger field with an on-commit script trigger - that way, it will always be up to date when data changes. Not every auto-enter is mandatory, but situational, although I do this all the time just to make sure.
  4. Set up a scheduled script to go through all records at night and reset the trigger field, to make sure that all that data is up-to-date.

Calc fields are always up to date, but when unstored, they are a performance drag. Moving them to auto-enters is great but you need the discipline to ensure that they are updated - they don’t evaluate unless some content in the auto-enter calc changes.

A common use case is calcs based on Get ( CurrentDate ), which, as with many of the GET commands, has to be evaluated at run time. By setting up a trigger in an auto-enter LET, you can mitigate the impact.

Another common use case is the de-normalization of fields - say you want the first record of a child table on a parent context layout. A search on that field would search all the child records, not just the current one that you are displaying. A work-around is to use a triggered auto-enter to create that field in the parent, which would then contain only one child record’s data.