I have an HD structure where I need to get a count of the nsCoxPLPostMetadata subtable where nsCoxPLIdentifier == Employee Bridge ID OR nsCoxPlIdentifier == Employee Number (Pictured below)
Is there a way that I can get a count of this subtable with the required filtering clauses without looping over it?
Is this a subtable sub? No. Subtable subs are persisted in the parent record. Looping is the most efficient because the records are already in memory. Loop and conditional away.
Is this a subtable main? Yes. There are a lot of things I would like to improve wrt data modeling and an abstraction over what I’m about to suggest, but with the current platform capabilities here’s your technique:
Subtable main records are tied to their parent record via a system field - nwHeaderId. A sub-main can be directly queried (or aggregated, as is your case) as long as you add a filter for the parent’s nwId in the nwHeaderId field of the subtable. Assuming I’ve understood your scenario correctly the action to use is Aggregate with the where clause (expression) including something like the screenshot below AND-ed with your criteria. What this is doing is counting records/nwIds in the subtable main narrowed to those records who’s nwHeaderId matches the parent. In my screenshot Emails is the subtable main and Directory is the parent record.
Put that all together and you have a single trip to the DB to obtain a count of the records you are interested in. If I’ve missed some nuance please lmk.
The subtable is a subtable main, I will try the aggregate method. Thank you!
@kendra.b For interest sake, when you use the Loop subtable action, is a fetch happening in the background or are the records already available/fetched from the original fetch on the subtable’s parent record? Am asking from a performance perspective if Looping is more efficient than adding another fetch/aggregatge to the DB.
cc: @colby.barrett
