# GetFirmAdvancedSearches - Advanced Documentation

## When to Use

**This is the entry point for any firm-level data query** - surveys, caseloads, reports, statistics, etc. The firm has pre-configured saved searches for all reporting needs. Use this endpoint to find them by keyword.

## Discovery Script - Find Searches and Get Their Columns

This script searches with both broad and narrow keywords to find the right saved searches, then gets columns for all candidates. Break the user's question into broad terms (category words) and narrow terms (specific phrases) to maximize coverage.

Example: "how polite are our staff from the 7 day survey" -> broad: "%survey%", "%staff%" - narrow: "%7 day%", "%polite%", "%feedback%"

```javascript
async () => {
  const opts = { take: 50, skip: 0, page: 1, pageSize: 50 };

  // Broad keywords (category) + narrow keywords (specific) in parallel
  const [r1, r2, r3, r4, r5] = await Promise.all([
    SecureApi.GetFirmAdvancedSearches('%survey%', opts),      // broad
    SecureApi.GetFirmAdvancedSearches('%staff%', opts),        // broad
    SecureApi.GetFirmAdvancedSearches('%7 day%', opts),        // narrow
    SecureApi.GetFirmAdvancedSearches('%polite%', opts),       // narrow
    SecureApi.GetFirmAdvancedSearches('%feedback%', opts),     // narrow
  ]);

  // Combine and deduplicate by ID
  const all = [
    ...(r1?.Data || []),
    ...(r2?.Data || []),
    ...(r3?.Data || []),
    ...(r4?.Data || []),
    ...(r5?.Data || []),
  ];
  const unique = _.uniqBy(all, 'ID');

  // Get columns for every unique search in parallel
  const withColumns = await Promise.all(
    unique.map(s =>
      SecureApi.GetAdvancedSearchColumns(s.ID)
        .then(cols => ({
          id: s.ID,
          name: s.Name,
          description: s.Description || '',
          // DataType lives on the LAST FieldList entry (the leaf), never on the column itself
          columns: cols.map(c => ({ DisplayName: c.DisplayName, DataType: c.FieldList.at(-1).DataType }))
        }))
    )
  );

  return withColumns;
}
```

## Aggregation Script - Execute and Summarize

After reviewing the discovery results, write a second script that runs the chosen search and aggregates. Keys in the result rows match column DisplayNames.

```javascript
async () => {
  const raw = await SecureApi.GetAdvancedSearchResultString(SEARCH_ID, '');
  const rows = JSON.parse(raw);

  return {
    total: rows.length,
    byCategory: _.countBy(rows, 'Column Name'),
    // Add all dimensions needed: groupBy, filter, trends, top-N
  };
}
```

## Key Points

- **The search runs once.** Before executing, list every figure you need and compute them all in that one script. If you expect follow-up questions, row-level output, or any chance of a re-check, cache the fetched rows in the same script via `processAndCacheResults` and reuse the `batchId` in later scripts - each `ExecuteScript` is a fresh invocation with no memory of the last one, and re-running the search pays the full query time again.
- NEVER call GetEndpointDetail for GetAdvancedSearchColumns or GetAdvancedSearchResultString - their calling conventions are shown above.
- Always call GetAdvancedSearchColumns before GetAdvancedSearchResultString - you need column names and types to write aggregation logic. `DataType` lives on the last entry of each column's `FieldList` (the leaf, `FieldList.at(-1)` in scripts), not on the column itself.
- `GetAdvancedSearchResultString` returns compact JSON keyed by column DisplayName - `JSON.parse` and use directly; best for in-script aggregation. `GetAdvancedSearchResults` returns columnar `{ Columns, DataJson }` optimized for transport - convert with `Helpers.toSearchResultObjects`; best when returning row data.
- Always aggregate before returning - never return raw rows.
- **Sentinel dates.** `1900-01-01` in a date field means "not set", not a real date. Treat it as empty and sanity-check date-derived rates against status before reporting them.
- To pass parameters for date ranges, staff IDs, or other filters: call `GetAdvancedSearchFilters(AdvancedSearchID)` to see the search's filters, then call `GetEndpointDetail` for `GetAdvancedSearchResults` - its documentation carries the ParametersJson construction guide (copy the filter you found by ParameterName, replace whichever value slot is populated, pass a flat array). For queries without parameters, pass empty string "" for ParametersJson.