Skip to content

[FEATURE REQUEST] - Configurable lookup fields for filtered-query IN-clause pivots #1197

@3SigmaTech

Description

@3SigmaTech

Feature title
Configurable lookup fields for filtered-query IN-clause pivots.

Problem statement
When a child object has several parent lookups, the filtered-query
planner (_createFilteredQueries in MigrationJobTask.ts) emits one IN-clause
query per lookup whose referenced object is also a script task. For
example, an Offer_Line_Item__c with Parent_Offer__c, Product__c,
and Related_Product__c triggers three filtered SELECTs in
STAGE 2, even when only one of those lookups is a meaningful
record-scope driver. The extra queries pull in unrelated records and
inflate both runtime and the result set.

The existing excludedFields setting can drop a field entirely, but
that also removes it from the SELECT clause and prevents the value
from being migrated.

Proposed solution
Add two opt-in settings on ScriptObject:

  • includedInClauseFields — strict allow-list of lookup field names
    permitted as pivots. When set, only these fields produce filtered
    queries.
  • excludedFromInClauseFields — deny-list of lookup field names that
    must not be used as pivots. When both are configured, the deny-list
    subtracts from the allow-list.

Listed fields remain in SELECT and migrate normally; only the
planner's pivot selection is affected. Behaviour is unchanged when
neither setting is provided.

Expected user impact

  • Reduces wasted Bulk/REST calls on objects with multiple parent
    lookups.
  • Avoids pulling unrelated records into a migration scope without
    forcing users to lose lookup field data.
  • Gives finer control over query planning without resorting to fully
    custom query overrides (which forfeit the auto-generated SELECT).

Configuration example (if applicable)

{
  "objectName": "Offer_Line_Item__c",
  "includedInClauseFields": ["Parent_Offer__c"]
}

After this, only WHERE Parent_Offer__c IN (...) is emitted; the
other two lookups still appear in the SELECT and resolve normally.

Alternatives considered

  • excludedFields — drops the field from SELECT entirely, losing
    data on the target.
  • Removing Product__c from the script's objects list — works as
    a side-effect (no parent task → no pivot), but only if Product
    records aren't part of the migration; brittle.
  • Custom query — works but means hand-maintaining the SELECT field
    list and losing the auto-generated behaviour.

Additional context
I have a working implementation on a feature branch and am happy to
open a PR if the maintainers are interested. The change is contained
to ScriptObject.ts and _createFilteredQueries in
MigrationJobTask.ts; ~28 lines added.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions