USE_CASE_ID: cross_floqer_table_lookup NAME: Cross-table lookup via HTTP API Call CATEGORY: Data operations The user needs to read rows from another Floqer sheet or workflow as part of the current workflow's chain — typically to filter, aggregate, or join against data produced elsewhere. This use case covers the `http_api_call` pattern that hits Floqer's own `/rows/list` endpoint, which complements (and in some cases supersedes) the native `lookup_another_floqer_workflow_row` action. For routing, file structure, and cross-cutting principles, see https://floqer.com/docs/use-case-catalog.txt. This file does not repeat them. INDEX: 1. When to use / when not to use 2. Inputs 3. Outputs 4. Workflow design 5. Implementation 6. Best practices 7. Common variations 8. Failure modes and mitigations 9. Related use cases ================================================================================ 1. WHEN TO USE / WHEN NOT TO USE ================================================================================ USE WHEN: - You need to filter rows by an *action output* on the target sheet (e.g. `tier === "T1"`, `score > 80`). The native lookup queries target-sheet input columns only — it can't filter on a JS formatter's output or an LLM's classification. - You want a single call to pull every row from a target sheet (e.g. a daily digest reads every qualified account from a scoring sheet) without configuring per-column filters. - You need to inspect multiple per-row cells (inputs *and* outputs) from each match in one fetch. - You need workflow-config simplicity for input-keyed lookups in addition to (or instead of) output-column filtering — but for input-keyed cases, `lookup_another_floqer_workflow_row` is the one-action alternative and almost always preferable. This use case stays relevant when you specifically need to filter by an action output, read every row in one fetch, or pull multiple cells per match in one call. DO NOT USE WHEN: - You're filtering by a unique input key (e.g. lookup by email or account_id) and only need one row at a time. Use `lookup_another_floqer_workflow_row` — one action, no JSON parsing, no auth wiring, no schema discovery. - The target sheet is very large (tens of thousands of rows) and the match rate is sparse. `http_api_call` returns every row in the page; native lookup returns only matches. For sparse matches at scale, the payload cost matters. - You want to keep credentials out of the configured action. `http_api_call` stores an `Authorization: Bearer ` header in the action's configuration; the native lookup uses the workflow's own auth context implicitly. ================================================================================ 2. INPUTS ================================================================================ - Target workflow ID and target sheet ID. For a workflow's main sheet these are equal; for a sub-sheet they differ. List sheets with `GET /workflows/{workflow_id}/sheets`. - A Floqer API key with read access to the target workflow. - Optional: filter predicates to apply client-side in a downstream JS formatter (e.g. tier value, score threshold, status enum, recency window). - Optional: `action_instance_id`s for any output columns you intend to filter on. Read these from `GET /workflows/{wid}/sheets/{sid}/actions/graph` on the target sheet; cells in the row payload are keyed by `action_instance_id`, not display name. ================================================================================ 3. OUTPUTS ================================================================================ - A structured view of the matched rows, projected to whichever columns the consumer needs. Typically piped into: - A digest message (`llm_models` or a JS formatter that builds the text inline). - A fan-out into another sheet (`push_data_to_sheet`). - A count / aggregate written to the current row. - A CRM write (`hubspot_update_object`, `salesforce_update_record`). - Per-row cells contain both the original inputs (`row.inputs`) and the outputs of every action that ran on that row (`row.cells[].outputs`). ================================================================================ 4. WORKFLOW DESIGN ================================================================================ High-level stages: 1. Configure `http_api_call` to POST to Floqer's own `/rows/list` endpoint on the target sheet. 2. Discover the action's output schema (one-time bootstrap — see https://floqer.com/docs/action-detail/http_api_call.txt section 3). 3. Parse the returned JSON in a JS formatter; filter on inputs OR action outputs as needed; project to the shape the downstream consumer expects. 4. Use the filtered results downstream — fan out, build a digest, write to a CRM, etc. ================================================================================ 5. IMPLEMENTATION ================================================================================ QUICK REFERENCE — chain shape on the consuming sheet: CONSUMING SHEET (your workflow that reads cross-sheet data) inputs: whatever the consuming chain already needs — this pattern is sheet-agnostic and slots into any existing chain chain: http_api_call "Fetch Target Rows" // POST /rows/list → source sheet [http_api_call "Fetch Second Sheet"] // optional — additional sources (e.g. exclusion list / CRM mirror) for set-difference / joins [http_api_call "Fetch Page 2 / 3 / ..."] // optional — only for sheets with >200 rows; chain pages then merge in the JS step format_data_using_js_expression "Filter + Project" // filter on inputs OR action outputs; reshape for the consumer // see Stage 4 — push_data_to_sheet, slack_send_message, salesforce_update_record, format/extract steps, etc. Detailed stages below. STAGE 1 — Configure http_api_call Action: http_api_call { "inputs": { "method": "POST", "endpoint": "https://api.floqer.com/api/v1/workflows//sheets//rows/list", "headers": [ { "name": "Authorization", "value": "Bearer floq_..." }, { "name": "Content-Type", "value": "application/json" } ], "body": [ { "name": "page_size", "value": "200" } ] } } Pagination note: /rows/list caps at 200 rows per call (default 20). For sheets larger than 200 rows, chain multiple `http_api_call` actions with `page_no: 1`, `page_no: 2`, etc., and merge the results in a downstream JS formatter. STAGE 2 — Discover the output schema `http_api_call` exposes its outputs only after the first run. Per https://floqer.com/docs/action-detail/http_api_call.txt section 3: 1. Add one sample row on the sheet that hosts this chain. 2. Run it so the HTTP call actually fires. 3. GET /workflows/{wid}/sheets/{sid}/actions/{aiid}/outputs Returns `outputs: [{name: "status", type: "number"}, {name: "data", type: "json"}]`. 4. Delete the discovery row and re-add a fresh one — the discovery row ran before the schema existed and won't carry typed outputs in its cell. Downstream reference: `{{.data}}` resolves to a JSON object with `rows`, `total_count`, `page_no`, `page_size`. The `rows` array is the payload to filter. STAGE 3 — Filter and project in JS Action: format_data_using_js_expression Example — filter by tier (an action output on the target sheet): (() => { const parseJson = raw => { try { return JSON.parse(raw); } catch (e) { return null; } }; const data = parseJson(`{{.data}}`) || {}; const rows = Array.isArray(data.rows) ? data.rows : []; const TIER_KEY = "format_data_using_js_expression_XX"; // tier-producing action const matches = rows.filter(r => r.cells && r.cells[TIER_KEY] && r.cells[TIER_KEY].outputs && r.cells[TIER_KEY].outputs.formatted_data === "T1" ); return JSON.stringify(matches.map(r => ({ account_id: r.inputs && r.inputs.account_id, company_name: r.inputs && r.inputs.company_name, score: r.cells && r.cells["format_data_using_js_expression_YY"] && r.cells["format_data_using_js_expression_YY"].outputs.formatted_data }))); })() Cells are keyed by `action_instance_id`. Read them from `GET /actions/graph` on the target sheet. STAGE 4 — Downstream consumers - Fan out into a sub-sheet: format_data_using_js_expression (extract CSV of IDs) -> csv_to_structured_array_format -> push_data_to_sheet. See csv_to_structured_array_format.txt key notes — append a trailing comma upstream to handle single-result lists. - Skip CSV and use `raw_to_structured_array` directly on the matches JSON. Same one-time schema-discovery pattern as `http_api_call`. - Write a row-level aggregate (count, max, sum) back to the current row by stringifying the reduce result. ================================================================================ 6. BEST PRACTICES ================================================================================ - Set `page_size: "200"` (max) unless you intentionally want a smaller response. Default is 20 and silently truncates. - Turn off `cache_enabled` on the consuming sheet when the target sheet's data changes between runs and you want fresh reads. Otherwise repeated triggers will return cached output. - Keep filter and projection logic in one JS step, not multiple chained steps. Re-parsing JSON across steps compounds the escape-sequence chaining risk — see https://floqer.com/docs/action-detail/format_data_using_js_expression.txt §8.3 Chained JSON between formatters. - When you only need one row by its primary key, fall back to `lookup_another_floqer_workflow_row`. Strictly simpler when the join key is an input column. - Scope the API key. Anything in the action's headers is stored in the workflow config; anyone with read access can see it. Use a key with the minimum permissions needed and rotate after sharing. ================================================================================ 7. COMMON VARIATIONS ================================================================================ - Filter by input column instead of action output: `r.inputs && r.inputs. === `. `http_api_call` is column-agnostic — same shape, different field. - Recency window (e.g. "rows updated within 30 days"): parse `r.created_at` per row, compare to `Date.now() - 30 * 86400e3`. - Aggregations: reduce in JS over `rows` to compute counts, distinct categories, max-by-field, etc. No separate count action needed. - Set-difference / anti-join: chain two `http_api_call` actions against different sheets (e.g. "qualified accounts" and "already mentioned"), feed both into one JS step, compute the difference. This is the canonical "cooldown / exclusion list" pattern. - Cross-workflow: target a sheet in a *different* workflow by putting that workflow's IDs into the endpoint. The same API key must have access to both. ================================================================================ 8. FAILURE MODES AND MITIGATIONS ================================================================================ - Pagination silently truncates large sheets. Mitigation: set `page_size: "200"` explicitly. For >200 rows, chain multiple `http_api_call` actions and merge in JS, or pre-filter at the source by maintaining a narrower target sheet. - Cached responses hide data changes between runs. Mitigation: set `cache_enabled: false` on the consuming sheet, or vary an input column (e.g. `trigger_date` with a timestamp) so the cache key changes per run. - Escape sequences in chained JSON break downstream `JSON.parse`. Mitigation: see https://floqer.com/docs/action-detail/format_data_using_js_expression.txt §8.3 Chained JSON between formatters. Use the `__NL__` sentinel pattern when re-emitting JSON for another formatter to consume. - API key in the action's headers is visible in workflow config. Mitigation: scope the key minimally; rotate after sharing the workflow. - `action_instance_id`s in the JS filter are stable per instance but not portable across workflows. Copying the consumer chain to a new workflow targeting a different scoring sheet means updating the cell keys. - The target sheet doesn't exist or the API key lacks access — `http_api_call` returns 401/404 in the `status` output, and the downstream JS sees an empty/null `data`. The pattern `Array.isArray(data.rows) ? data.rows : []` handles this gracefully and produces an empty match set rather than erroring the row. ================================================================================ 9. RELATED USE CASES ================================================================================ - Account research and scoring — produces the kind of per-row output (tier, score, signals_summary) this pattern typically consumes downstream. - Native single-row lookup — https://floqer.com/docs/action-detail/lookup_another_floqer_workflow_row.txt. Use it when the join key is an input column and you only need one row. ================================================================================ This file is maintained manually. Last updated: 2026-05-11. Use case catalog: https://floqer.com/docs/use-case-catalog.txt Action detail: https://floqer.com/docs/action-detail/http_api_call.txt