# Match requested record categories to receipts Canonical page: https://agents.paretoid.com/workflows/match-requested-record-categories-to-receipts/ Category: Client Service Practice relevance: cross-practice. Useful supporting administrative method; no claim of practice-specific legal instructions. Jurisdiction/provider scope: Excel/Power Query administrative request-category comparison; not a statutory disclosure completeness determination. Compare administrative request categories with received-item metadata. Status: Proposed / not tested. The public source describes the problem. Documentation supports only the stated method components; no result from this recipe has been verified. Verification: Source methods and reported problems have been reviewed. We have not run the adapted workflow. The sample below describes expected behavior on synthetic data, not a customer result. ## The problem Compare administrative request categories with received-item metadata. Why it matters: A receipt can cover one requested category while another is still outstanding. A category-by-category check gives staff a clear collection list without deciding whether the evidence is legally sufficient. ## Sources and evidence - pain: [Pain evidence: Records requests](https://www.reddit.com/r/paralegal/comments/1v1jui0/records_requests/) Records requests, 2026-07-20, older. OP is asked to check whether all records and bills are present and fears being blamed for omissions; comments distinguish records received without corresponding billing. - method: [Method: Power Query merge](https://learn.microsoft.com/en-us/power-query/merge-queries-overview) Power Query merge provides deterministic table matching. The proposed completeness report is our design, not a documented medical feature. Source checked: October 15, 2026. Pain accounts are self-reported. Documentation supports components; this adapted recipe and prompt have not been tested. ## Suggested approach Start when: Staff records a receipt 1. compare an attorney/staff-approved request manifest with receipt metadata 2. Keep source references and report unknowns instead of filling gaps. 3. The responsible reviewer checks the report before any separately approved action. ## What you need - Excel desktop with Power Query (documented for supported Microsoft 365/Excel versions); optional firm-approved Copilot. - Actual approved request letters/log and receipt cover sheets or staff receipt log. An authorized staff member reviews them locally. No clinical records or patient identifiers go to the AI workspace. - Staff-defined request aliases, administrative category vocabulary and status meaning. The responsible lawyer determines evidentiary sufficiency separately. - Use synthetic inputs first. Keep real data in firm-approved accounts and processing, with appropriate access, confidentiality and retention controls. Aliases alone do not anonymize records. Never paste credentials, API keys or unnecessary client details. ## How to use Manual source preparation required; native Excel reconciliation is documented; no case-management extraction integration verified - There is no universal Clio/MyCase/records-provider AI query or CSV export verified for this task. The first step is explicit manual recordkeeping. - A filename or cover-sheet label alone cannot prove the contents are complete. Staff_recorded_status is a human determination, not an AI clinical conclusion. Access and setup: - The firm’s actual request/receipt storage product was not supplied, so its retrieval menus cannot be truthfully specified. - Power Query and Copilot availability vary by installed version/license; this route is intended for supported desktop Excel. ### 1. Create the two small source tables from real records Tool: Local document viewer and Excel Location: Open the actual request and receipt cover sheets locally; Excel > blank workbook For each request, read the approved request’s administrative categories and assign a non-identifying request_id. In Expected, enter one row per requested category. In Receipts, staff enter one row per receipt/category after checking the receipt; assign receipt_id and copy their reviewed status. Exclude names, patient dates, treatment detail and raw records. Keep pointers to original sources only in a separate restricted index. Expected output: expected.csv: request_id,administrative_category,expected_flag. receipts.csv: receipt_id,request_id,administrative_category,staff_recorded_status. These are staff-created ledgers, not a product export. Next step: Select each range > Home > Format as Table; name the tables Expected and Receipts. Check before continuing: Check category spelling and exact request aliases across tables. Missing evidence stays unknown. Do not infer a request ID from similar names. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model ### 2. Load the tables into Power Query Tool: Excel Power Query Location: Select a table cell > Data > From Table/Range Repeat for Expected and Receipts. Set request_id and administrative_category to Text consistently. Load/keep both queries; use their names to identify them. Expected output: Two queries containing the reviewed administrative rows. Next step: Open Expected in the Power Query editor. Check before continuing: Check row counts and headers against the input tables; no external connector is required. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/import-data-from-data-sources-power-query ### 3. Join expected categories to receipts Tool: Excel Power Query Location: Power Query editor > Home > Merge Queries > Merge Queries as New Choose Expected first and Receipts second. Select request_id then administrative_category in both previews in the same order. Choose Left outer explicitly and leave fuzzy matching off. Expand receipt_id and staff_recorded_status. Use Home > Close & Load for the result. Expected output: Every expected category retained with zero, one or multiple matched receipt rows. Null receipt data indicates no matching logged receipt. Next step: Use the merged table for staff review or optional AI summary. Check before continuing: A duplicated match may be a valid partial shipment or duplicate log; do not collapse it without review. Filter expected_flag only according to the staff-defined rule. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/merge-queries-power-query https://support.microsoft.com/en-us/excel/import-data-from-data-sources-power-query ### 4. Summarize exceptions without inventing completeness Tool: Excel Copilot, optional Location: Deidentified workbook > Copilot icon at lower right > Chat only Ask the prompt against the original two tables and merged result. Without Copilot, filter the result for null receipts, partial/unclear statuses and duplicate keys, then write the same checklist manually. Prompt: ```text Compare the reviewed Expected and Receipts tables by exact request_id and administrative_category, and check the merged result. List expected categories with no matching receipt, partial/unclear staff statuses, duplicate receipts and contradictory statuses, citing worksheet rows and receipt IDs. Return request_id, category, matched_receipt_ids and discrepancy. Preserve unknowns. Do not inspect clinical records, infer missing treatment, decide legal or medical sufficiency, invent receipt status, edit the workbook or send requests. ``` Expected output: request_id,category,matched_receipt_ids,discrepancy and cited worksheet rows. Next step: Staff open the original request/receipt locally to clear or explain each exception. Check before continuing: A missing BILLS row means no matching BILLS receipt in this log, not proof that medical evidence is inadequate. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/copilot/get-started-with-copilot-in-excel https://support.microsoft.com/en-us/excel/merge-queries-power-query ### 5. Approve the administrative follow-up checklist Tool: Records staff and responsible lawyer Location: Reviewed reconciliation worksheet Staff verify exceptions against original cover sheets and actual received contents under firm procedures. Correct the staff ledger only after checking; refresh the query when ready. The lawyer decides whether any substantive evidentiary gap exists. Expected output: Reviewed missing/partial/unclear administrative-category checklist. Next step: Any new records request or external follow-up is a separately authorized action. Check before continuing: A clean join does not establish full record coverage; keep staff’s verification notes. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/merge-queries-power-query ## What you will get Excel/Power Query or approved assistant workspace; output receipt-reconciliation.csv with request_id, category, matched_receipt_ids, discrepancy. ## Human review and limits Staff determine whether received material actually meets the authorized request; attorney determines evidentiary sufficiency. Test a records receipt with absent billing and a duplicate receipt. The assistant drafts or reports only. Check source records and review the output before a human performs any separately approved send or change. No automatic legal decision, money movement, account connection or publication is included. What to measure: In a synthetic pilot, count correctly found exceptions, false alarms, and unresolved inputs. No time or cost saving is assumed. ## Original prepared-input schema expected.csv: request_id, administrative_category, expected_flag. receipts.csv: receipt_id, request_id, administrative_category, staff_recorded_status. No records themselves or clinical data. Synthetic example input: ```text REQ2 expects categories RECORDS and BILLS; one RECORDS receipt exists → BILLS has no matching receipt, not a conclusion that medical evidence is incomplete. ``` Expected synthetic output: ```text REQ2 expects categories RECORDS and BILLS; one RECORDS receipt exists → BILLS has no matching receipt, not a conclusion that medical evidence is incomplete. ``` Failure check: Staff determine whether received material actually meets the authorized request; attorney determines evidentiary sufficiency. Test a records receipt with absent billing and a duplicate receipt. ## Starter files Native blank scaffolds and original synthetic examples may use different schemas. Follow NATIVE-INPUT-MAPPING where supplied. These files do not connect accounts or install automation. - [Download starter kit](https://agents.paretoid.com/assets/starter-kits/a06-starter-kit.zip) — All inputs, synthetic examples and review notes in one ZIP. No automation is installed. - [Start here: get the sources and prepare the review](https://agents.paretoid.com/assets/starter-kits/a06/START-HERE.txt) — Documentation-checked acquisition, explicit manual mapping, exact prompts and human review limits - [Setup and review checklist](https://agents.paretoid.com/assets/starter-kits/a06/README.txt) — Required inputs, file use, review steps and limits - [Expected blank CSV](https://agents.paretoid.com/assets/starter-kits/a06/expected-template.csv) — Blank input headers for expected.csv - [Expected synthetic example](https://agents.paretoid.com/assets/starter-kits/a06/expected-example.csv) — Synthetic test records for expected.csv - [Receipts blank CSV](https://agents.paretoid.com/assets/starter-kits/a06/receipts-template.csv) — Blank input headers for receipts.csv - [Receipts synthetic example](https://agents.paretoid.com/assets/starter-kits/a06/receipts-example.csv) — Synthetic test records for receipts.csv - [Synthetic example expected results](https://agents.paretoid.com/assets/starter-kits/a06/expected-results.txt) — Compare a pilot result with the planted exceptions ## Optional original prepared-input prompt Destination: Excel Copilot, optional · prepared inputs This original prompt uses its own prepared-input schema. Do not mix it with native-step tables without the supplied mapping. ```text Compare these deidentified request-manifest and receipt-status tables using request ID and category. Report expected categories with no matching receipt, partial receipts and contradictory status. Cite rows. Do not decide whether medical evidence is adequate, infer missing treatment or inspect clinical records. Produce a staff-review checklist only. INPUTS expected.csv: request_id, administrative_category, expected_flag. receipts.csv: receipt_id, request_id, administrative_category, staff_recorded_status. No records themselves or clinical data. OUTPUT Excel/Power Query or approved assistant workspace; output receipt-reconciliation.csv with request_id, category, matched_receipt_ids, discrepancy. Use only these synthetic or firm-approved inputs and preserve source row/page IDs. Keep missing or conflicting evidence unresolved. Return a draft for the named reviewer. Do not send messages, connect accounts, change records or permissions, move money, or make legal decisions. Keep source files unchanged. List missing prerequisites before continuing. ``` Proposed / not tested · editorial prompt Published by Law Firm Agents. Proposed adaptation; not tested. Not legal advice.