Episode Details

Quick question: When was the last time you wondered if you’re using the best tool for querying or formatting your Microsoft Lists? If your answer is ‘every time I hit a performance bottleneck,’ you’re definitely not alone. Today, we break down the real pros and cons of CAML, REST, and JSON formatting that every Power Platform professional should know before choosing a data strategy. Spoiler: The most obvious choice might not be the best one.Why CAML Refuses to Die: The Hidden Power of Old-School QueriesLet’s be honest—CAML isn’t winning any popularity contests. But if you’ve ever seen someone suggest tossing it out because “REST does everything now,” there’s a good chance they haven’t run up against the specific corners of Microsoft Lists that still make CAML feel like the only grownup in the room. It’s like seeing a retired engineer hanging around the factory floor, outlasting every new hire. Most pros—especially folks new to Power Platform or SharePoint—just assume REST is supposed to be the replacement for everything legacy. In presentations, REST arrives dressed in a cloud-friendly suit. Documentation after 2018 barely mentions CAML except in a footnote. Teams default to REST endpoints. Power Automate connectors wave the REST API flag. Ask around in most Microsoft 365 circles, and you’ll probably hear that “nobody uses CAML anymore.”But then, quietly, the old XML-based ranger pops up right where you need that one thing REST can barely do. We’ve all hit that point: You open a list, the UI is modern, the Teams app syncs fine, and then you try to pull a filtered report with multiple levels of AND/OR logic. You stack nested lookups, date filters, and a couple quirky calculated columns. Suddenly, REST throws up its hands and gives you either a generic timeout or an error that sends you down two dozen GitHub threads. Try expressing a query with eight different filters and some child-parent relationships. REST will let you write it, sure, but the JSON gets ugly fast—and performance drops like a rock.Not long ago, I watched a Power Platform team migrate an old SharePoint workflow—years of business logic packed into classic designer flows—over to REST-based flows in Power Automate. On paper, it looked simple: swap out CAML for REST queries, update connections, and call it a modernization win. But on day one of testing, the migration choked on a batch operation pulling data from a list containing 75,000 items. With CAML, the old flow sliced off results in neat little pages, kicked out only the columns it needed, filtered everything server-side, and shipped back exactly what the automation required. The REST-based version, running nearly identical logic, ballooned in both response size and query time. And just for fun, it blasted past the 5,000-item list view threshold, which is always a favorite trouble ticket. Tech support spent a week flipping through REST documentation, only to end up re-enabling the CAML-based query for those edge cases.Here’s what stands out about CAML: the way it lets you build queries with complex grouping and advanced filtering that REST, for all its flexibility, still finds clunky. CAML’s structure isn’t just XML for the sake of pain—it actually shapes the query so it can be parsed, executed, and optimized server-side. That’s code running in the SharePoint backend, not your browser or Power Automate client pulling tens of megabytes over the network. When you need to return only the rows that hit three different lookup values, don’t bring along every item in the list, and avoid blowing past thresholds, CAML can quietly make that happen. Try this—auto-approve a set of requests based on five nested business rules, filtering for calculated fields and workflow status, all without loading every entry. Most REST-based setups either throw you back more data than you need or require running extra filtering logic after-the-fact, burning up memory, flow time, and patience.There’s even a well-known backend story in the