That O365 Guru from Cape Town (#NadirKamdar)

Mastering Excel Office Scripts from Beginner to Expert: Creating a Well-Designed Excel Report

2025-08-21T18:11:00.000+03:00

Excel Office Scripts allow you to automate and format Excel workbooks through code, similar to macros, but designed for the cloud. In this step-by-step guide, we’ll start from the basics of creating and recording an Office Script, then integrate scripts with Power Automate to generate a well-designed Excel document stored in SharePoint. By the end, you’ll know how to go from a simple recorded script to an advanced workflow that creates dynamic Excel reports with ease.

What are Office Scripts and Why Use Them?

Office Scripts in Excel are a powerful automation feature that let you record or write scripts (in TypeScript/JavaScript) to perform repetitive tasks. They operate much like traditional Excel macros, but work in the cloud (Excel for Web and modern Excel desktop):contentReference[oaicite:0]{index=0}. This means your Excel files can live in SharePoint or OneDrive and still run automated scripts, enabling new scenarios that VBA macros (which only run in desktop Excel) cannot:contentReference[oaicite:1]{index=1}. Office Scripts are especially useful for integrating Excel with other services via Power Automate flows, bridging gaps that built-in Power Automate Excel actions can’t cover:contentReference[oaicite:2]{index=2}:contentReference[oaicite:3]{index=3}.

Prerequisites: To use Office Scripts, you need a Microsoft 365 commercial or educational subscription (they are not available for consumer plans):contentReference[oaicite:4]{index=4}. Ensure you have the Automate tab visible in Excel (it’s enabled by default for eligible users; if not, check under File > Options > Customize Ribbon to enable the Automate tab). Also, for running scripts through Power Automate, a Microsoft 365 work or school account with the appropriate license (such as Office 365 E3/E5 or equivalent) is required:contentReference[oaicite:5]{index=5}.

Beginner: Recording and Writing Your First Office Script

Getting started with Office Scripts is easy – you can use the Action Recorder to capture your steps without writing code, or write a simple script yourself. Let’s create a basic script and get familiar with the environment:

:contentReference[oaicite:6]{index=6} *Excel’s Automate tab in the ribbon, highlighting the Scripting Tools group with Record Actions and New Script (Office Scripts):contentReference[oaicite:7]{index=7}.*

Open the Automate tab: In an Excel workbook (Excel for Web or latest Excel desktop for Microsoft 365), go to the Automate tab on the ribbon. In the Scripting Tools group, you’ll see options for Record Actions and New Script:contentReference[oaicite:8]{index=8}. If you click Record Actions, Excel will begin recording your actions (entering data, formatting cells, creating tables, etc.) and generate a script from those steps:contentReference[oaicite:9]{index=9}.
Use the Action Recorder (optional): For a first script, try recording a simple task. For example, enter some sample data, convert it into a table, add a Total row, or apply formatting. The Action Recorder will list each step in a pane as you perform them:contentReference[oaicite:10]{index=10}. Click the Stop button when done, and the recorded TypeScript code will appear in the Code Editor pane:contentReference[oaicite:11]{index=11}. You don’t need coding experience to do this – it’s a great way to generate a script that you can later edit if needed:contentReference[oaicite:12]{index=12}.
Create a New Script (Code Editor): Alternatively, click New Script to open the Code Editor with a blank script. A default template appears with a function main(workbook: ExcelScript.Workbook). This is where you write your code. For instance, to sum values in two columns into a third column, you might write a loop or use Excel formulas via script. Here’s a very simple example of an Office Script that writes a value to cell A1:

function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); sheet.getRange("A1").setValue("Hello, Office Scripts!"); }

This script gets the active worksheet and sets cell A1’s value. You can run the script by clicking the Run button in the Code Editor. After running, you should see the text appear in the worksheet. (If you recorded a script instead, clicking Run will replay all the recorded steps on the workbook.)

Save and Name the Script: Click the Save button in the Code Editor. By default, scripts are named “Script 1”, “Script 2”, etc., but you can rename it to something meaningful (click the script name in the gallery or the ... menu and choose Rename):contentReference[oaicite:13]{index=13}. The script is saved as an .osts file in your OneDrive under Documents/Office Scripts/ by default:contentReference[oaicite:14]{index=14}. (You can verify this by checking your OneDrive; scripts are stored here automatically.)

At this point, you’ve created a basic Office Script. You can run it anytime from the Automate tab’s script gallery. Now, let’s move on to integrating this with Power Automate to run the script in a flow.

Intermediate: Running Office Scripts with Power Automate

Power Automate allows you to trigger your Office Scripts as part of automated workflows. For example, you could run a script on a schedule or in response to an event (like a new email or form submission), then take further actions (such as emailing results). The Excel Online (Business) connector in Power Automate provides two actions to run scripts:

Run script – for scripts stored in your own OneDrive (default location):contentReference[oaicite:15]{index=15}.
Run script from SharePoint library – for scripts stored in a SharePoint site (for team or shared scenarios):contentReference[oaicite:16]{index=16}.

We’ll first use the basic Run script action (OneDrive). Suppose you have an Excel file on OneDrive and a script (like our “Hello, Office Scripts” or any other script) saved in your Office Scripts folder. Here’s how to set up a simple flow:

Create a Power Automate Flow: Go to Power Automate (flow.microsoft.com) and create a new flow (e.g., an Instant cloud flow for testing, triggered manually or on a schedule). Add a new step and select the **Excel Online (Business)** connector.
Add "Run script" Action: Choose the Run script action. You’ll be prompted to select:
- Location: e.g., OneDrive for Business.
- Document Library: usually “OneDrive”.
- File: browse to the Excel workbook on OneDrive that you want the script to run on (e.g., a template report workbook).
- Script: choose the script by name from the list (Power Automate will list the scripts available in that workbook or in your OneDrive Office Scripts folder):contentReference[oaicite:17]{index=17}:contentReference[oaicite:18]{index=18}.
(Optional) Provide Script Parameters: If your script’s main function has parameters (besides the workbook), the action will show additional input fields for each parameter so you can pass data from the flow to the script:contentReference[oaicite:19]{index=19}. For example, if we modified our earlier script to main(workbook, text: string) and use setValue(text), a field for “text” would appear in the Power Automate action where we could enter a value or dynamic content.
Run the Flow: Save and test the flow. The flow will open the Excel file and execute the Office Script. For instance, if the script adds “Hello” to cell A1, after the flow runs, you should see that value in the Excel file:contentReference[oaicite:20]{index=20}. You can then continue the flow (e.g., send an email with the workbook attached or move the file, etc.) based on your needs.

:contentReference[oaicite:21]{index=21} *Configuring the Power Automate Run script action to run an Office Script on a OneDrive-hosted Excel file:contentReference[oaicite:22]{index=22}.*

This basic integration shows how Power Automate can run your script on an existing Excel file. Now, let’s take it to the next level: storing scripts in SharePoint for team use, and creating new Excel files and populating them via scripts – an advanced scenario ideal for generating reports.

Expert: Advanced Automation – Generating Excel Reports via SharePoint and Office Scripts

In an advanced workflow, you might want to query data from a system, create a new Excel report file, insert and format the data, and distribute the file – all automatically. Office Scripts can be stored in a SharePoint library (so they can be shared across your team) and run on newly created workbooks. Let’s break down how to achieve a full automation, similar to the Power Automate workflow provided in the question:

1. Store Office Scripts in SharePoint for Team Use: By moving your .osts script file to a SharePoint site (or saving it there to begin with), the script becomes accessible to others with site access:contentReference[oaicite:23]{index=23}. You can move a script from your OneDrive to SharePoint by editing the script and using the Move command, or using Save as to copy it to a SharePoint document library:contentReference[oaicite:24]{index=24}. Once in SharePoint, the script will appear in the All Scripts list under Automate for users who have access, and can be run via the Run script from SharePoint library action in Power Automate:contentReference[oaicite:25]{index=25}:contentReference[oaicite:26]{index=26}. (Note: At the time of writing, running scripts from SharePoint in Power Automate is a relatively new feature and may be labeled as “Preview”.)

2. Designing an Office Script for Reporting: Instead of a simple script that writes one cell, your script can be more complex – for example, take a dataset and format it into a report. You might design a script that accepts parameters for things like:

A data table (e.g., an array of arrays representing rows and columns)
A header or title for the report
Metadata like report date range or filters used
Options like whether to format the data as an Excel Table, include a header row, etc.

This script (written in TypeScript) would create a new sheet or use a template sheet, insert the title, maybe some metadata info (like “Start Date: X, End Date: Y” in cells), then write the data table into the sheet. It could then format that range as a Table, apply styles or formulas (e.g., autosum, formatting of columns), and do any other polishing (freeze panes, adjust column widths, etc.). By encapsulating all these steps in one Office Script, you centralize the report formatting logic.

Example: The advanced script provided (referenced in the JSON code) likely had a function signature like:

function main(workbook: ExcelScript.Workbook, includesHeader: boolean, formatAsTable: boolean, sheetName: string, headerTitle: string, Metadata: string[][], worksheetData: (string | number)[][]) { // 1. Create or clear target worksheet // 2. Write the header title at top (e.g., merge cells A1:D1, set value to headerTitle) // 3. Write metadata (each entry of Metadata array) in subsequent rows (e.g., "Start Date: ", "End Date: ", etc.) // 4. If includesHeader is true, treat first row of worksheetData as column headers. // 5. Write the worksheetData into the sheet (e.g., starting from A5). // 6. If formatAsTable is true, format the inserted range as an Excel Table for easy filtering. // 7. Apply any additional formatting (column widths, date formats, bold headers, etc.). }

This approach separates the data (passed in via parameters) from the presentation (handled by the script). It makes the flow of data cleaner: Power Automate gathers data and calls the script, and the script handles how the Excel file looks.

3. Building the Power Automate Flow: Now, to create a flow that uses this script for report generation, here are the steps you would implement (similar to the advanced example given):

Trigger & Data Retrieval: Define how the flow is triggered – it could be manual, on a schedule (e.g., monthly report), or based on some event. Then add actions to gather the data for the report. In the provided example, a Power BI dataset query was used to retrieve data (using a DAX query):contentReference[oaicite:27]{index=27}, but you could use any data source (SQL query, SharePoint list, etc.). The result is a set of records that will go into Excel.
Compose Data for Excel: Use data operations in Power Automate to prepare the data for Excel. In the example, they created an array for the header row (column names) and an array of arrays for the data rows (using the Select action to shape the query output) – then combined them using union():contentReference[oaicite:28]{index=28}:contentReference[oaicite:29]{index=29}. Essentially, you want a two-dimensional array representing the table (including header as first row). They also created an array of “metadata” (like Start Date, End Date, etc.) to pass to the script:contentReference[oaicite:30]{index=30}.
Create a New Excel File: If the goal is to have a new Excel file report every time, you can use a SharePoint or OneDrive connector action to create a file. For example, the flow might generate a unique filename (like Report_20250821.xlsx) using the current timestamp:contentReference[oaicite:31]{index=31}, then call a SharePoint Create File (or in the code, they used a SharePoint HTTP request to add the file in a document library) to create an empty Excel file:contentReference[oaicite:32]{index=32}. This file will serve as the output report.
Run Script on the New File: Next, use the Run script from SharePoint library action (Excel Online (Business) connector):contentReference[oaicite:33]{index=33}. In this action, you specify:
- Workbook Location: URL of the SharePoint site (e.g., https://<yourtenant>.sharepoint.com/sites/<SiteName>).
- Workbook Library: Document library name (e.g., Documents).
- Workbook: The path to the Excel file (e.g., /Reports/Report_20250821.xlsx).
- Script Location: (if different site, specify the site URL where script is stored, otherwise same as workbook site).
- Script Library: The library containing the .osts script file (e.g., Documents or a specific scripts library).
- Script: The path to the script file (e.g., /Reports/MyReportScript.osts or simply select the script name if it’s in the same library):contentReference[oaicite:34]{index=34}.
- Once you select the script, Power Automate will show fields for the script parameters. Here is where you pass in the data prepared in previous steps:
  - includesHeader: a boolean (e.g., true, since our first row of data is headers).
  - formatAsTable: boolean for formatting as Table (true/false as desired).
  - sheetName: a string for the worksheet name (e.g., “Sheet1” or “Report”).
  - headerTitle: the report title (e.g., “Sales Report – All Data”).
  - Metadata: the array of metadata (e.g., an array of [Key, Value] pairs like [["Start Date", "2025-08-01"], ["End Date", "2025-08-21"], ["Status", "All"]] which you composed earlier).
  - worksheetData: the 2D array of the actual table data (including header row) that you prepared.
Send or Use the Report: After the script runs, your Excel file is populated and formatted. You can add steps to utilize this file – for example, attach it in an email to the requester or stakeholders:contentReference[oaicite:35]{index=35}, or simply notify someone that the file is ready. In the provided workflow, if an email was requested, they constructed an email with a nicely formatted HTML body (including some of the input details) and a link to download the report:contentReference[oaicite:36]{index=36}. You could also upload it to a specific SharePoint folder for record-keeping.

:contentReference[oaicite:37]{index=37} *Power Automate’s Run script from SharePoint library action, configured with workbook and script locations on SharePoint (Note: script file Colour current cell Yellow.osts selected as an example):contentReference[oaicite:38]{index=38}.*

Once this flow is set up, generating a report is as easy as triggering the flow – it will pull fresh data, create a workbook, run the Office Script to format everything, and deliver the output. This is a **fully automated solution** for producing consistent Excel reports.

Tips for Success and Best Practices

Plan Your Script’s Inputs and Outputs: When writing an advanced Office Script, think about what data it needs (inputs) and what it produces. For instance, your script might not need to return anything to Power Automate if the goal is just to produce a file. But if needed, you can have your script return values back to the flow (the Excel connector will capture the return value). Keep the parameter list logical and only as complex as necessary:contentReference[oaicite:39]{index=39}:contentReference[oaicite:40]{index=40}.
Error Handling: Consider what happens if the data is empty or too large, or if the script runs into an unexpected condition. While Office Scripts might not have advanced error handling like try/catch (currently), you can use conditional logic and checks in your TypeScript code to handle known potential issues (e.g., “if no rows returned, set a cell to ‘No data found’”). In Power Automate, you can also add parallel branches or additional checks (like verifying the file was created, etc.).
Testing: Test your Office Script independently in Excel first with sample data. Then test the Power Automate flow step by step. Use small datasets to ensure the flow runs quickly and the script does what you expect. Once it works, you can try larger data and see if any performance tweaks are needed (for example, writing values to a range in one go is faster than cell-by-cell). The example advanced script makes use of formatting the entire dataset as a table at once, which is efficient.
Office Scripts vs Traditional VBA: If you’re coming from a VBA background, remember Office Scripts run in a cloud context. They cannot do certain things like access local resources or make web requests (with some exceptions):contentReference[oaicite:41]{index=41}. They also require internet connectivity since they run on the server side. But the advantage is they can be triggered by cloud events and run on files stored online, enabling automation end-to-end without manual intervention.

Conclusion

By progressing from recording a simple script to deploying an advanced Power Automate flow, you’ve seen how Office Scripts can elevate your Excel automation to a new level. We started with basic action recordings (no coding needed) and moved to writing custom TypeScript code for more flexibility. We then integrated these scripts into Power Automate, first in a straightforward way and finally in a sophisticated scenario where data is dynamically collected and a polished Excel report is generated and delivered.

Office Scripts, combined with Power Automate and SharePoint/OneDrive, provide a robust platform for building automation around Excel. This means no more repetitive manual report updates – your “expert-level” solution can create a well-designed Excel document at the click of a button or on a schedule. With practice, you can extend this approach to many other use cases (data audits, dashboards, etc.), empowering your team to focus on insights rather than tedious Excel tasks.

Happy scripting and automating!

References:

Microsoft Learn – Run Office Scripts with Power Automate:contentReference[oaicite:42]{index=42}:contentReference[oaicite:43]{index=43}
Microsoft Learn – Office Scripts file storage and ownership:contentReference[oaicite:44]{index=44}:contentReference[oaicite:45]{index=45}
Microsoft Support – Record your actions as Office Scripts:contentReference[oaicite:46]{index=46}:contentReference[oaicite:47]{index=47}
Digital Mill Blog – Sending values from Power Automate to Excel with Office Scripts:contentReference[oaicite:48]{index=48}:contentReference[oaicite:49]{index=49}
SharePains Blog – 2 Actions to run script in Excel from Power Automate flows:contentReference[oaicite:50]{index=50}:contentReference[oaicite:51]{index=51}

Using SVG Images in PowerApps: Scalable Icons, Dynamic Data, and Animations

2025-08-18T20:29:00.004+03:00

Why Consider SVG in PowerApps?

SVG (Scalable Vector Graphics) images offer several advantages over standard image formats like PNG or JPEG, making them ideal for PowerApps:

Infinite Scalability: SVGs are vector-based, so they remain crisp at any size. You can resize an icon or graphic without any loss of quality – no more blurry images on high-resolution displays[1]. This is perfect for apps that need to look good on everything from phones to large monitors.
Small File Sizes: SVGs are essentially XML instructions for drawing shapes, not pixel-by-pixel data. This typically leads to smaller file sizes and faster load times compared to bitmap images[2]. A lighter app means better performance for your users.
Transparency and Styling: SVG supports transparent backgrounds (unlike JPEG)[3], so your icons can blend seamlessly with any theme or color. Moreover, because SVGs are code, you can edit their colors or shapes via that code – even dynamically in PowerApps formulas.
Interactivity & Animation: Unlike static images, SVGs can be animated or made interactive, which opens up creative possibilities. You can add hover effects, smooth transitions, or other dynamic changes to SVG icons to enhance user experience[4]. We’ll explore how to leverage this in PowerApps, from color changes on hover to animated charts.

In short, SVG images are flexible, high-quality, and dynamic – a perfect combo for creating modern, responsive PowerApps UI.

Embedding SVGs in PowerApps (Image Control vs. HTML)

To use an SVG in PowerApps, you won't simply upload the .svg file as an image. Instead, you'll embed the SVG code as a data URI. The most common method is using an Image control with a special formula:

Add an Image control to your app (this will serve as a container for the SVG).
Copy the SVG's XML code – you can get this by downloading the SVG file and opening it in a text editor, or by using your browser’s Inspect Tool if the icon is on a webpage. (For example, right-click an SVG image file and “Open with Notepad” to see the markup[5].)
Set the Image control’s Image property to a string that contains the SVG code. This string must start with the SVG MIME type and be URL-encoded. A typical formula looks like:

Image.Image =
"data:image/svg+xml;utf8," & EncodeUrl("<svg ...> ... </svg>")

Begin with "data:image/svg+xml;utf8," to tell PowerApps this is SVG content[6].
Wrap the raw SVG markup inside an EncodeUrl() function. This encodes special characters properly so the SVG renders. For example:

"data:image/svg+xml;utf8," & EncodeUrl("<svg xmlns='http://www.w3.org/2000/svg' width='16' height='16' ...> ... </svg>")
Make sure to replace any double quotes " inside the SVG code with single quotes ' to avoid breaking the string[6].

Once set, the SVG image will appear in the app[7].
(Optional) Use an HTML Text control: In older versions, creators sometimes used an HTML Text control to directly embed <svg> markup. However, recent updates stopped rendering raw <svg> tags in HTML controls[8]. The workaround is essentially the same as the Image control: embed the SVG via an <img> tag with a data URI source[9]. For example:

HtmlText = "<img src='data:image/svg+xml;charset=utf-8," & EncodeUrl("<svg ...</svg>") & "' />"
This will display the SVG in the HTML frame[9]. In general, using the Image control is simpler and more performant for SVGs, especially since the Image control supports features like hover fill and is more flexible with animations.

Tip: If hand-coding the data URI sounds tedious, there are tools to help. For instance, the PowerApps SVG converter by Charles Sexton allows you to paste SVG code and it generates the data:image/svg+xml;...EncodeUrl("...") format for you[10]. This saves time in preparing the SVG string.

With the SVG embedded, you can move/resize the Image control as needed. It behaves like any image in PowerApps – but now you have the power to modify the SVG via formulas!

Where to Find SVG Icons and Images

One of the great things about SVGs is the vast amount of icons and graphics available online. Here are some excellent sources:

Microsoft Fluent UI Icons: Microsoft’s own icon set (formerly Office UI Fabric icons) is available in SVG format[11]. These can help your app feel native to the Office/Teams ecosystem.
Font Awesome and Material Design Icons: Popular icon libraries that offer free SVG downloads[12]. Font Awesome, for example, has thousands of icons. Material Design by Google is another huge collection.
Bootstrap Icons: A rich set of open-source icons in SVG form[13] (Matthew Devaney’s blog uses a Bootstrap icon in his SVG example).
Flaticon, IconFinder, Noun Project: These platforms aggregate icons (some free, some paid). You can often filter for SVG format.
SVGBox or SVGRepo: Online repositories of SVG graphics and illustrations.

Creattie.com – My Go-To Library: Personally, I use Creattie as a source for high-quality SVG icons and even Lottie animations. Creattie offers a curated library of static and animated icons in SVG (and Lottie JSON) format. It’s a premium resource, but they do have some free assets as well[14][15]. Insider tip: try to catch their lifetime membership deal around Black Friday – I did, and I love it! Having unlimited access to thousands of icons and animations has been a game-changer for quickly spicing up my apps.

When you find an icon you like, download the SVG (or copy its XML code if provided). Many sites have a “Copy SVG” button or let you tweak the icon color before downloading. Once you have the SVG code, you can incorporate it into PowerApps using the method described above.

Example: SVG Icon (Creattie) with a Hover Color Change

Let’s walk through a simple example: adding an SVG icon from Creattie and changing its color on hover in the app.

1. Get the SVG Icon Code: Suppose we want to use a "heart" icon from Creattie. After finding the icon on Creattie, download it as an SVG or copy its code via the browser’s dev tools (some icon sites provide the raw code directly). For this example, assume we have the SVG markup for a heart icon.

2. Embed the SVG in an Image control: Add an Image control in PowerApps and set its Image property using the data URI formula. For example:

ImageHeart.Image =
"data:image/svg+xml;utf8," & EncodeUrl(
"<svg xmlns='http://www.w3.org/2000/svg' width='64' height='64' viewBox='0 0 24 24'>
<path fill='#FF0000' d='M12 21.35l-1.45-1.32C5...Z' />
</svg>"
)

This would render a red heart icon (the fill='#FF0000' in the path sets it to red). In practice, you'd paste the full <path d='...'/> from the icon.

3. Implement a Hover color change: Out of the box, the SVG itself cannot directly have CSS :hover effects inside an Image control – PowerApps only allows inline SVG/CSS, and pseudo-classes like :hover are not supported in that context[16]. However, we can achieve a hover effect through PowerApps controls. Here are two approaches:

Overlay a transparent control: Place a transparent Button or Label over the image (or group them in a container). That overlay can use its HoverFill or HoverColor properties to create a visual effect. For example, you could set the overlay Label’s HoverFill to a semi-transparent white, giving the icon a "highlight" glow on hover[17]. This doesn't change the icon's color itself, but can simulate a highlighted icon (e.g. making a dark icon appear lighter when hovered)[18].

Hover effect achieved by overlaying a transparent label: when the user hovers, the label's HoverFill makes the icon appear highlighted (lightened).[17][18]

Swap or modify the SVG via variables: If you want the icon color to change on hover (say from red to blue), one simple trick is to use two Image controls – one with the default color, one with the hover color – and toggle their visibility based on a context variable. For example, on the overlay control’s OnHover, set a variable varHover=true and OnUnHover set varHover=false, then show/hide the appropriate colored icon. This is a bit clunky but effective for small cases.
Advanced – Invert the SVG for HoverFill: A clever solution is to pre-process the SVG icon to create an inverted mask of the icon, as detailed by PowerApps experts[19]. Essentially, you make an SVG where the icon shape is cut out of a solid background. Then you use the Image control’s Fill and HoverFill properties to control color. The icon’s shape will appear in the Fill color (because the shape itself is transparent in the SVG). On hover, the HoverFill color kicks in, and since the background changes, the icon appears to change color[19]. This approach requires editing the SVG (e.g. using a tool like Inkscape to subtract the shape from a rectangle), but it lets you achieve true icon color swap on hover using a single Image control with no overlay.

In our heart icon example, the simpler overlay method might suffice: you could overlay a label that on hover displays a different colored heart behind the original, or just gives a glow. The key point is that while SVG code alone can't do :hover in PowerApps, we can combine SVG with PowerFX logic to get the interactivity we want.

Binding Dynamic Data to SVGs

One of the superpowers of using SVGs in PowerApps is that you can inject dynamic data into the SVG code. Since the SVG is just a text string, you can concatenate Power FX formulas and variables right into it. This allows your graphics to respond to app data in real-time.

Changing properties via variables: For example, say you have an SVG icon and you want its color to reflect a user-selected theme color. You might have a Color variable (e.g. varIconColor). In the SVG string, set the fill attribute to that variable. You can do this by breaking the string and concatenating the variable’s value. One technique is converting the color to a hex string. A Reddit solution suggests using the JSON() function to get a color in hex form and then slicing it, for instance: fill='" & Mid(JSON(varIconColor), 2, 7) & "'"[20]. This inserts a color like#FF5733` into the SVG code at runtime. As the variable changes (say the user picks a different color), the SVG will update to that new color.

Dynamic sizes and coordinates: You can also drive dimensions or positions from data. For example, if you are displaying a value as a bar length, you could insert the numeric value into the width of a <rect> element. If varValue = 75, your SVG string could include width='" & varValue & "' (assuming some scale) so the bar renders 75 units long. Whenever varValue updates, the bar length updates.

Generating multiple SVG elements from data: Power FX allows you to construct strings by iterating over tables or collections. Using functions like Concat() or ForAll(), you can dynamically create multiple SVG elements. For instance, imagine a simple bar chart with a collection of scores. You could do something like:

"data:image/svg+xml;utf8," & EncodeUrl(
"<svg width='100%' height='120' xmlns='http://www.w3.org/2000/svg'>" &
Concat(
colScores,
"<rect x='10' y='" & (10 + 30 * ID) & "' width='" & Score & "' height='20' fill='#4caf50' />"
) &
"</svg>"
)

Here colScores might be a collection with fields ID (index 0,1,2,...) and Score. The formula concatenates a <rect> for each item, spacing them vertically by 30 and setting width based on the Score value. The result is an SVG with multiple bars rendered. This technique was used by a PowerApps maker to dynamically draw a grid of rectangles (like a mini chart or warehouse map) inside a gallery – they used nested Concat( Sequence(...) ) calls to generate dozens of <rect> elements based on their item’s data[21][22].

Because the SVG is re-rendered on-the-fly, it can reflect whatever data is passed in. You can get creative: dynamically highlight certain parts of an SVG (by changing their fill via conditions), display text by inserting it into <text> elements, etc. If you can compute it in PowerApps, you can inject it into the SVG.

For example, consider a progress percentage displayed visually. We could have an SVG circular progress graphic where a variable varProgress (0–100) controls the arc length and text. In the SVG string, we insert varProgress in a couple of places – once for the text content (e.g. "& varProgress &"%) and once in a shape attribute (perhaps stroke-dasharray for the circle's fill). This is exactly what an animated progress circle example does: it defines an SVG with a text node showing varProgress and a circle whose stroke is partially filled based on varProgress[23]. As that variable changes (when a user clicks +10% for instance), the graphic updates to show the new percentage.

The ability to bind SVGs to data means you’re not limited to static icons. You can create data-driven visuals – gauges, charts, indicators – all within an image control, without relying on external chart controls. This gives you a lot of flexibility to craft custom visuals that match your app’s needs and style.

Adding Animations to SVGs in PowerApps

Static SVGs are great, but adding animation can make your app feel more alive and responsive. There are a couple of ways to animate SVG content:

SMIL Animations (SVG <animate> tags): SVG has a built-in animation capability via <animate> and related elements. You can animate attributes of SVG elements (like a rectangle’s width or a circle’s radius) over a duration. For example, to animate a bar growing from zero to full width, you might include:

<rect width="0" ...>
<animate attributeName="width" from="0" to="100" dur="1s" fill="freeze" />
</rect>
This snippet would smoothly increase the <rect>’s width from 0 to 100 over 1 second[24][25]. The fill="freeze" keeps the final state. (Note: In a vertical bar scenario, you might animate the height and adjust the y position to make it grow upward.) SMIL animations run automatically once the SVG loads, so they can be great for entrance effects or loading animations.

CSS Animations within SVG: You can embed CSS inside an SVG’s <style> tag and define @keyframes animations or transitions. When the SVG is rendered, those animations will play. The advantage of CSS animations is you can get more complex easing functions and even trigger animations on state changes. In PowerApps, the SVG is re-rendered when variables change, which can retrigger CSS animations if they are defined accordingly.

For example, the animated progress circle we mentioned uses a CSS keyframe to animate the stroke of a circle from 0% to the target percentage[26]. The SVG’s <style> includes: @keyframes progressbar { from { stroke-dasharray: 0,100; } to { stroke-dasharray: varProgress, 100 - varProgress; } } and the circle has animation: progressbar 2s; applied[27][26]. When the app updates varProgress and effectively reloads that SVG, the circle animates to the new value. This approach yields a smooth tweening of the arc with a predefined duration (2 seconds here).

You can also use CSS animation-timing-function (e.g. ease-in-out, cubic-bezier curves) to control the easing of these animations for a polished feel. Tools like EasingWizard can help you generate keyframes or bezier curves for custom easing – you can then plug those into the CSS inside your SVG to get, say, a bouncing or elastic effect as an animation plays.

Animated SVG Icons (pre-made): Some libraries (like Lottie or Animated SVG icon sets) offer icons that are already animated (e.g., a loading spinner or a heart that pulses). If they are pure SVG (or Lottie JSON via a web viewer), you could integrate them. Creattie, for instance, provides Lottie animations – while not directly SVG, they can be used in PowerApps via the HTML control or as video/GIF. However, sticking to SVG, you might find animated SVG files (which use the above techniques under the hood). Embedding those would also work in the Image control, since the image control will honor the SVG’s internal animation directives.

When adding animations, be mindful of performance – but simple SVG animations (tweens, rotations, etc.) are usually very lightweight. They run on the client side and do not require any PowerApps timer or repeated updates, which is a huge plus. The animations defined in SVG/CSS run independent of the PowerApps logic once triggered, resulting in smooth visual effects without taxing PowerApps’ processing.

Example: Dynamically Animated Bar Chart with Easing

To bring it all together, let's consider a final example: a dynamic bar chart whose bars animate into view with an easing effect. This showcases dynamic SVG generation, data binding, and animation:

Scenario: Suppose we have a list of sales figures for different products, and we want to display a horizontal bar chart of their values when the screen opens. Instead of bars just appearing at full length, we want them to slide in from zero width with a nice easing (like an "ease-out" so they decelerate).
SVG Construction: We use an Image control and set its Image property to an SVG string. We Concat through our data (e.g., a collection of products) to output a <rect> for each bar. The width of each <rect> will be based on the sales value (perhaps scaled down to fit, e.g., value * 5 pixels per unit).
Animation via CSS: In the SVG’s <style>, we define a keyframe animation for the bars. For instance:

@keyframes growWidth { from { width: 0; } to { width: VAR_WIDTH; } }
.bar { animation: growWidth 1s ease-out forwards; }
We can dynamically insert the target width (VAR_WIDTH) for each bar if needed, or more simply, use CSS transitions. Alternatively, we might not even need separate keyframes per bar – we can set each <rect> with a width="0" initially and then use a single keyframe that grows to 100% width, if we set each rect's width as a percentage of its target. However, for clarity, let's say we use individual animations.

Animation via SMIL: As another approach, we could attach an <animate> element to each rect:

<rect width="0" x="0" y="..." fill="#4caf50">
<animate attributeName="width" from="0" to="200" dur="1.2s" fill="freeze" calcMode="spline" keyTimes="0;1" keySplines="0.25 0.1 0.25 1" />
</rect>
In this example, the calcMode="spline" with keySplines="0.25 0.1 0.25 1" is an ease-in-out cubic-bezier (this is an example of using a custom easing curve similar to CSS ease-out). Each bar’s <animate> can have the to value set to that bar’s target width. The result: when the SVG loads, each bar grows from 0 to its full length in 1.2 seconds with a smooth easing. Using fill="freeze" makes sure the bars stay at their final width after the animation[25].

The dynamic part comes if these values can change (say the user filters data and the chart should update). If the width values or the number of bars is tied to variables or collections, the Image control will get a new SVG string on change, and the animations will replay for the new data. This means whenever the data updates, the bars animate to their new widths automatically – a very slick effect for data that might fluctuate.

Putting it in PowerApps: You might have something like:

"data:image/svg+xml;utf8," & EncodeUrl(
"<svg xmlns='http://www.w3.org/2000/svg' width='100%' height='200'>" &
"<style>
.bar { fill:#4caf50; }
.bar.animate { animation: grow 0.8s ease-out forwards; }
@keyframes grow { from { width:0; } to { width:100%; } }
</style>" &
Concat(colSales,
"<g transform='translate(0," & (10 + 30 * Index) & ")'>" &
"<rect class='bar' width='0' height='20'></rect>" &
"<text x='5' y='15' fill='#000'>" & ProductName & "</text>" &
"<rect class='bar animate' width='" & (Sales*5) & "' height='20'></rect>" &
"</g>"
) &
"</svg>"
)

In this pseudo-code, each data item produces two rects: one static (0 width) and one with class 'animate' and full target width. The CSS .bar.animate animation grows from 0 to 100% of that rect’s specified width. The result is each bar (the green rectangle) animates from nothing to its full length. (We also included a text label for the product name on each bar for clarity.)

While the exact implementation can vary, the takeaway is that PowerApps + SVG allows complex visuals with animations that would otherwise require external chart controls or JavaScript libraries. And all of this is done with just a single Image control in a canvas app!

By considering SVG images in your PowerApps, you unlock a world of customizable, high-resolution, and even animated visuals. We covered why SVG is worth it (quality, performance, flexibility), how to get them into PowerApps, where to find great SVG assets (don’t forget to check out Creattie and other libraries), and how to push them further with dynamic data and animations. With modern Power FX formula capabilities and responsive containers, these techniques integrate smoothly into your app designs. So go ahead – experiment with SVG icons for your buttons, create that animated progress indicator, or build a dynamic chart. Your apps will not only look sharper, but also feel more interactive and alive, all thanks to the power of SVG in PowerApps!

[1] [2] [3] [5] [6] [7] [11] [12] [13] [16] [17] [18] SVG Images in Power Apps - Matthew Devaney

https://www.matthewdevaney.com/svg-images-in-power-apps/

[4] Enhancing PowerApps with SVG Icons / Blogs / Perficient

https://blogs.perficient.com/2023/07/25/enhancing-powerapps-with-svg-icons/

[8] [9] SVG's inside HTMLtext controls : r/PowerApps

https://www.reddit.com/r/PowerApps/comments/1hyylfc/svgs_inside_htmltext_controls/

[10] [20] How to load an SVG file into power apps and then color the individual shapes in the SVG file based on data? : r/PowerApps

https://www.reddit.com/r/PowerApps/comments/1gbbky3/how_to_load_an_svg_file_into_power_apps_and_then/

[14] Download Free Icon in SVG & PNG - Creattie

https://creattie.com/icons/item/free

[15] Curated animated icons - download in Lottie, JSON, GIF & MP4

https://creattie.com/lottie-animated-icons/all

[19] Custom Icon Buttons in Power Apps with Hover Color | The Chris Kent

https://thechriskent.com/2021/05/27/custom-icon-buttons-in-power-apps-with-hover-color/

[21] [22] Dynamic Chart Inside Gallery - Finally Figured Out How to Use SVG for a Graphic in Power Apps! : r/PowerApps

https://www.reddit.com/r/PowerApps/comments/1hmqz57/dynamic_chart_inside_gallery_finally_figured_out/

[23] [26] [27] Using SVG animation in Power Apps with Image Control — ND

https://www.nanditdhania.com/blog-3-1/add-a-touch-of-magic-to-your-power-apps-with-animated-svgs-copy

[24] [25] How do I make a bar-chart in SVG have animated bars growing upwards? - Stack Overflow

https://stackoverflow.com/questions/11815207/how-do-i-make-a-bar-chart-in-svg-have-animated-bars-growing-upwards

My Journey to Lesotho – Afriski: A Snowy Adventure to Remember

2025-07-16T09:26:00.001+03:00

Fifteen hours. That’s how long it takes to drive from Cape Town to Afriski in Lesotho. We split the drive over two days: 10 hours to Bloemfontein, then another 5 hours to Afriski. Add about 3 hours for bathroom breaks and lunch, and you've got yourself a serious road trip.

As we approached the snow-capped peaks, our excitement peaked too. By 4 p.m., we were just 10 kilometers away from Afriski. Snow blanketed everything around us, turning the world into a winter postcard. Suddenly, my car lit up the dashboard like a Christmas tree. Warning lights I'd never seen before started flashing — one even showing a road with a snowflake on it, warning that the road was slippery. "Yes, I know its slippery, I'm driving and I can feel it too!" I muttered, half amused and half nervous.

Then, we saw it: a long queue of vehicles. A massive truck had gotten stuck, blocking the road. The snow was too deep and too icy; cars couldn't gain traction to pass. People were already turning back, warning us that unless we were prepared to wait indefinitely, we should do the same. But we had already crossed the border and had nowhere else to go.

So we waited.

An hour and a half passed. The sun dipped below the horizon. Darkness fell. Snow started falling again, thicker this time. The temperature plummeted. We were cold, hungry, and, worst of all, my wife was getting desperate for a bathroom.

Another two hours passed. It was now pitch black outside, and icicles were forming on the windshield. Still no movement. But then, finally, some good news: Afriski had sent a tractor to tow stranded vehicles. We could see it making its way down the line toward us. Relief washed over us—until I tried to start the car. Nothing.

Turns out, all the short intervals of turning the engine on and off to keep warm had drained the battery. Worse, the electronic gear selector was locked in Park. No power, no shifting. No shifting, no towing.

The tractor driver moved on to help others while I tried to figure it out. Normally, this is where I’d pull out my phone and Google the problem. But I had crossed the border. No roaming. No signal. No help.

So I did what every IT guy eventually does: try turning it off and on again.

I got out into the snowfall, popped the hood, and looked for the battery. Nothing. Where the hell is it? I've had this car for over a year. How could I not know where the battery is? I climbed back in, tried to warm up, and opened the user manual. No help. Then I remembered—some cars have their batteries in the back.

So I unloaded a week’s worth of luggage into the snow, digging through layers to access the rear compartment. After some trial and error, there it was: the elusive battery, tucked away in the bottom right corner. But I didn’t have a spanner to disconnect it.

Just when I thought we were completely stuck, the tractor returned. They offered to jump-start the car instead. Cables connected, engines humming—and boom, the car roared to life. I shifted out of Park. Hope had returned.

My wife was now visibly in pain, needing a bathroom more desperately than ever. We followed the tractor through a particularly treacherous stretch of road. Eventually, they waved us on, saying the rest of the road should be manageable.

We managed to move forward a few kilometers, but then traction failed. I reversed, edged forward again, only to slip once more. It was like a snowy Sisyphean task. Then, the inevitable happened.

My wife, who runs cold under normal conditions, couldn’t take it anymore. She jumped out of the car, ran into the darkness behind a snowbank, dropped her pants and all the layers beneath, and made yellow snow. It was desperation, survival, and human vulnerability all at once. I was worried this level of cold could actually hurt her, but thankfully she made it back safely.

Just then, the tractor returned like a knight in snowy armor, pulled us over the final icy patch, and led us straight into Afriski.

We made it. Exhausted, frozen, and a little humbled—but we made it. And that, dear reader, is how our snowy adventure became a story we'll tell for a lifetime.

Tipping for Takeaways: A Counter-Intuitive Trend in a Customer-Focused Era

2025-06-29T15:46:00.003+03:00

My Uber Eats Wake-Up Call

I recently had a jarring experience that highlighted a new and uncomfortable trend in the food service industry. I ordered breakfast via Uber Eats and, which is common in this case, I was prompted to add a tip at the time of ordering – before the food was even prepared or delivered. Wanting to be generous, I preemptively added a 15% tip to the order. Unfortunately, when my breakfast arrived, I discovered that during transit, half of my coffee had spilled so I was provided with a half-filled cup. The delivery itself was a flop, yet the driver had already pocketed a tip for service that turned out to be subpar.

Sure, I could theoretically go through the hassle of reversing or adjusting the tip after the fact, but the process to cancel it is cumbersome. In frustration, I found myself thinking: Did that delivery really deserve a tip? Probably not. The whole ordeal left such a bad taste that I swore off using that delivery service for a while.

This incident opened my eyes to a larger issue: Why are we being asked to tip before service is delivered? Traditionally, a tip is a reward for good service – a way to show gratitude after the service meets or exceeds expectations. Tipping used to be the final touch on a dining experience, not a down payment on one. But as I discovered, things are changing, and not necessarily for the better.

The New Norm of Upfront Tipping (and Why It Feels Wrong)

It’s not just delivery apps. Lately, I’ve noticed the same tipping expectation creeping into ordinary takeaway experiences. For example, when I phone in a takeout order or send a WhatsApp message to a local restaurant and then go to pick it up, I’m often confronted with an awkward moment at the register. As I’m about to pay for my takeout, the cashier will hand me the point-of-sale device and ask if I’d like to add a tip, usually with preset buttons for 10%, 15%, or more. This happens before I even receive my food, before I know if the order is correct, on time, or tasty. The only thing the establishment has accomplished so far is taking my money – yet I’m expected to reward them already?

Contrast this with the classic restaurant experience. When you dine in, you enjoy the ambiance, use the restaurant’s dishes, and receive attentive service throughout your meal. Only at the end, once you’ve gauged the quality of that experience, does the bill come with a subtle line inviting a gratuity. You have the freedom to tip after enjoying (or enduring) the service, calibrating the amount to match the experience. That’s how tipping is supposed to work – as a reflection of service quality, not a blind leap of faith. In fact, surveys show that for an overwhelming majority of people, the quality of service is the major factor in deciding whether and how much to tip (pewresearch.org), We tip more when service is great, and we tip less (or not at all) when it’s poor. It’s a simple, intuitive exchange.

Now, consider how counter-intuitive upfront tipping is in light of that. By asking for a tip in advance, the system ignores the very principle that tips are earned through good service. It puts customers in a bind: tip blindly or risk seeming stingy. And let’s be honest, the “ask” is hardly subtle. A device thrust in your face with pre-loaded tip percentages, while the staff looks on, can feel like a pressure tactic. I’ve even had cashiers stand there expectantly, virtually guilting me in real time as I decide whether to tap “No Tip” on the screen.

It’s an uncomfortable scene for a takeout transaction that used to be purely about paying for the food. From the customer’s perspective, this trend feels wrong. Takeout staff typically aren’t doing the kind of service a waitstaff does – they take your order, hand you your food, and you’re on your way. As one frustrated commenter aptly put it, “the front desk person does almost nothing in a take-away order… why do people think they deserve to be tipped for that?” They’re simply doing the job they’re already paid to do, with minimal interaction (reddit.com).

Historically, tipping for carry-out was not expected. In fact, a survey in 2014 found that only 14% of people said they tipped for takeout orders (pos.toasttab.com). The vast majority did not tip when picking up their own food – which makes sense, since there’s no table service involved. While things have evolved since then, it underlines that tipping in these contexts is a relatively new expectation, not some long-standing norm.

Tipflation: Tipping Creeps Into More Places

Most Americans (72%) report that tipping is expected in more places today than five years ago. (pewreaearch.org)

It’s not just you – the feeling that “everyone is asking for a tip now” is very real. The phenomenon has even earned a nickname: “tipflation”, referring to the explosion of tipping requests in situations where we never encountered them before.

Digital payment technology has accelerated this shift. Coffee shops, takeout counters, food trucks – you name it – many have added touchscreen payment systems that automatically prompt for a gratuity, even for a simple muffin or a bottled water.

When Tipping Hurts the Customer Experience

For businesses, this rise of pre-service tipping prompts presents a serious question: Is that extra 10–15% being squeezed out of customers worth the damage it may be doing to customer goodwill? We live in an era when companies preach about being “customer-centric” and delivering the best experience possible. Every little detail of the customer journey is scrutinized to maximize satisfaction. And yet, here we are introducing a practice that many customers find off-putting. It’s a baffling contradiction – almost a self-inflicted wound in the realm of customer experience.

So much of customer experience comes down to how you make your customers feel. If you make customers feel valued, respected, and comfortable, they’re likely to come back. But if your checkout process leaves them feeling awkward or pressured, you’ve ended the interaction on a sour note, even if everything prior went well. It puts the customer on the spot and can make them feel “super-uncomfortable” pressing “No Tip” with the staff’s eyes on them. In essence, it’s a “buzzkill” way to conclude an otherwise pleasant transaction.

We’ve all been in that position: perhaps the coffee was good and the cashier was friendly, but the moment of being guilted at the payment screen sticks with you more than the taste of the latte. That discomfort isn’t just a fleeting annoyance – it can shape our future choices. If a customer leaves feeling nickel-and-dimed or judged, they may think twice about coming back. In my case, the frustrating pre-tip on a botched delivery led me to stop using that app, at least for a while. I’m not alone. There are countless anecdotes of people actively avoiding certain cafés or choosing not to order takeout as often, simply to escape the constant tip pressure. As one observer quipped, being "peppered to give more for less (e.g. 20% for takeout or counter service) is annoying and detracts from the customer experience. It can also make your customers opt to spend less with you in the future.” (linkedin.com) In other words, pushy tipping policies might earn a few extra Rands today but could cost you many Rands down the line when the customer decides not to return.

From a customer-centric perspective, this trend feels like a misstep. Businesses might think they are helping their employees earn a little more, but if the customer walks away feeling worse for having interacted with you, was it really a win? A fundamental of hospitality is that the customer should leave happier than when they arrived. If the last impression you give is essentially “Pay more now (and don’t disappoint us)”, you’re not exactly sending people off with warm, fuzzy feelings. Over time, that can erode your brand reputation. No detail is too small in shaping customer loyalty, and that includes how (and when) you ask for tips.

Putting Service Before Tips: How to Fix It

Is there a better way? I believe so. If we truly care about customer experience (as we claim to), we need to rethink how tipping is integrated into takeaway and delivery transactions. Here are a few ideas and recommendations that could maintain fairness to workers without alienating customers:

Make Tipping Truly Optional and After-Service: The timing of the tip matters. Restaurants and apps could allow tips after the service is rendered – once the food is delivered or the takeout meal is in the customer’s hands and verified correct. Some food delivery platforms already offer the option to tip after delivery (or to adjust the tip within a short window). This should be the norm. If I had the chance to tip after my Uber Eats driver handed me a coffee-drenched bag, you can bet I would have adjusted my 15% accordingly. By letting the tip occur post-service, you ensure it’s a reward for quality, not a blind bet. And if something goes wrong, customers don’t feel cheated because they haven’t prepaid a gratuity.
Remove the Social Pressure: If you do present a tip prompt, do it in a way that minimizes social pressure. For example, some businesses turn the payment screen around to face the customer privately, and train staff to look away or busy themselves while the customer chooses a tip option. No hovering, no expectant stares. The message should be: “Tip if you wish – we won’t think less of you if you don’t.” it’s wise for businesses to tone down how they prompt for tips. A discreet approach can preserve the customer’s dignity and comfort.
Use Subtle Reminders or Delayed Options: One creative solution I’d love to see gain traction is shifting the tip request away from the immediate payment moment. For instance, a restaurant could print a QR code on the receipt or takeaway bag that customers can scan to leave a tip later if they feel inclined. In some regions (like here in South Africa), apps like SnapScan is popular enough and allows exactly this – you scan a code and tip whenever you want, without a person watching your every move. This way, the tip becomes a separate act of gratitude, not an automatic expectation. It also gives the customer time to experience the food and service quality. If their meal was great or the staff went above and beyond, they’re free to show appreciation after the fact. If not, no harm done. Similarly, digital delivery platforms could send a push notification after delivery: “How was your order? Tip your driver here if you’d like.” The key is giving control back to the customer and decoupling the tip from the heat of the payment moment.
Focus on Service, Not Guilt: This one’s more cultural. Businesses should double down on training employees to deliver excellent service – friendly interactions, accurate orders, timeliness – without making the tip the centerpiece of the encounter. If a customer has a pleasant pickup experience, they’re naturally more likely to leave a little something in the tip jar or come back next week. But if the first thing an employee does is shove a card machine asking for 15% before even saying “Here’s your order, hope you have a great day,” the priorities feel skewed. Encourage staff to earn the tip through great service, not to assume it. This mindset shift can go a long way. – so concentrate on delivering quality, and trust that tips (and customer loyalty) will follow.
Consider Transparent Pricing or Fair Wages: Stepping back, there’s a broader industry discussion here. One reason tipping has spiraled is because workers often depend on tips due to low wages, especially in food service. Many customers now feel like they’re subsidizing wages via tips. Perhaps it’s time for some restaurants to experiment with service-inclusive pricing – pay staff a living wage and bake the true cost of service into menu prices. This can remove the awkward tip dance entirely. A few places have tried “no tipping, higher prices” models. While it’s not an overnight fix (and it has its own challenges), it’s worth considering in the long run. The goal should be to ensure employees are taken care of without making every customer feel like they’re being shaken down for extra cash at every turn. When employees are paid decently, any tip truly becomes a token of appreciation – which is what it was meant to be in the first place. And as a bonus, well-paid, happy employees tend to provide better service, creating a virtuous cycle of genuinely good customer experiences that people want to reward.

Conclusion: Let Gratitude Follow Service

Tipping is ultimately about gratitude – a “thank you” from a customer for a job well done. By putting the thank you before the job, we’ve managed to make the whole practice feel forced and transactional, undermining the goodwill it’s supposed to foster. This new trend of tipping for takeaways (especially tipping before the service is delivered) is, in my view, a step in the wrong direction for customer experience. It’s counter-intuitive to the customer-focused values that businesses claim to uphold.

As both a professional in the customer service field and a consumer, my plea is simple: let’s restore some sanity to tipping culture. Reserve the tip for when it makes sense – after the service – and present it as an option, not a demand. If we truly value our customers, we won’t put them in the uncomfortable position of paying extra before we’ve delivered on our promise. Instead, we’ll focus on delivering great food, on time, with a smile. We’ll let the service speak for itself. If it’s good, many of us will happily tip – and do so with a smile of our own. If not, that feedback is valuable too. Either way, the customer leaves feeling like they were in control and respected, not shaken down.

In the long run, putting customers first will pay off far more than a few guilt-induced rands at the register. A satisfied customer is likely to return, tip in the future, or recommend the business to others – outcomes far more valuable than a coerced 10% on one transaction. By fixing how and when we ask for tips, we can ensure that gratitude flows naturally again, as a genuine exchange between customer and service provider. Let’s not mess this up by letting a short-sighted trend tarnish the very relationships we’re trying to build. In an age where customer experience is king, it’s time to remember the simple truth: earn the tip, don’t impose it. The loyalty and goodwill we gain will be the real tip we earn for treating customers right.

Let’s not mess this up by letting a short-sighted trend tarnish the very relationships we’re trying to build. In an age where customer experience is king, it’s time to remember the simple truth: earn the tip, don’t impose it.

Power Automate OData Filter Query 101: Filtering SharePoint Columns for Beginners

2025-06-19T15:31:00.008+03:00

When building Power Automate flows with SharePoint, you often need to get list items or files that meet specific conditions – for example, “all completed tasks created after Jan 1”. Instead of retrieving all items and then filtering in the flow (which is slow and inefficient), you can use the OData Filter Query option on the Get items (or Get files (properties only)) action. This filter query allows you to retrieve only the items you need by specifying conditions, leveraging SharePoint’s query engine for faster results [sharepains.comteamdynamix.umich.edu]. In this beginner-friendly guide, we’ll explain what OData filter queries are, why they’re useful, and provide examples for filtering various SharePoint column types. We’ll cover Yes/No, Date, Currency, Lookup, Choice (single and multi-select), Person (single and multi-select), Hyperlink, and Managed Metadata columns. For each, we’ll describe the SharePoint column setup, show the Filter Query syntax to use in Power Automate, and explain common issues (like case sensitivity, internal names, and SharePoint delegation/threshold limits). By the end, you should be comfortable crafting OData filter queries to get exactly the data you want – without unnecessary loops or delays. Let’s get started!

What is an OData Filter Query (and Why Use It)?

OData (Open Data Protocol) is a standard query language for web APIs. In SharePoint’s REST API (which Power Automate uses under the hood), queries can be shaped with OData parameters like $filter to select items matching certain criteria [teamdynamix.umich.eduteamdynamix.umich.edu]. The Filter Query field in Power Automate’s SharePoint actions is essentially the $filter portion of an OData query. By entering a filter query, you tell SharePoint to return only items that meet the conditions, saving your flow from handling extraneous data.

Why is this useful? Two big reasons: performance and simplicity. A well-crafted filter query dramatically reduces the data sent over the network and speeds up your flow [sharepains.com]. For example, pulling 5 items that meet a condition is much faster than pulling 5,000 and then filtering them in an Apply to each loop. It also avoids extra loop logic or filter array steps in your flow – the query does the work for you. This is crucial for large lists: SharePoint has a 5,000 item list view threshold, and filtering server-side helps you stay within that limit by using indexed columns and avoiding non-delegable operations (more on that shortly).

Where can you use it? Primarily in the Get items action for SharePoint lists, under Filter Query in the action’s advanced options [teamdynamix.umich.edu]. The same syntax also works in similar actions like Get files (properties only) for document libraries and any other SharePoint connector action that offers a filter query field – the logic is identical for files vs. list items. (Many other connectors, such as Dataverse, SQL Server, etc., also accept OData filters, though their field names and capabilities differ. In this post we’ll focus on SharePoint.)

Above: The Get items action in Power Automate, with the Filter Query field (advanced options) where you can enter an OData filter string [teamdynamix.umich.edu].

OData Filter Query Basics (Syntax & Tips)

An OData filter query follows a basic pattern:

<InternalFieldName> <operator> <value> [logical operator <second condition> ...]

For example: Status eq 'Completed' or Quantity le 100. Let’s break down the components and best practices:

Internal Field Name – Use the field’s internal name as seen by SharePoint, not the display name. Internal names often differ (e.g. a column labeled "Project Name" might have internal name Project_x0020_Name). You can find the internal name by going to the column settings in SharePoint – the URL will include Field=<Name> [teamdynamix.umich.edu]. Always use that exact name (including any _x0020_ codes for spaces). Using the wrong name is a common mistake that leads to “column not found” errors.
Comparison Operator – OData supports operators like eq (equals), ne (not equals), lt (less than), gt (greater than), le (≤), ge (≥). These must be in lowercase [teamdynamix.umich.edu]. For example, use eq rather than EQ. Logical operators and / or are also lowercase.
Value – The value to compare against. For text or date/time, surround the value in single quotes. For numbers and booleans, no quotes are needed (more on booleans soon). If comparing to an empty/null value, use null (no quotes). Examples: Title eq 'Project A' (Title is text), Amount gt 500 (Amount is numeric), DueDate eq null (DueDate blank). For date/time values, use ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ) and prefix with the keyword datetime – e.g. Created ge datetime'2023-01-01T00:00:00Z' [sharepoint.stackexchange.com].
Combining Multiple Conditions – You can chain conditions with and / or. Use parentheses to group conditions when mixing and/or to avoid ambiguity. For example: (Status eq 'Active' and Priority eq 'High') or (Status eq 'Active' and DueDate lt datetime'2023-12-31T23:59:00Z'). It’s a good practice to put each clause in parentheses, especially if using or, to ensure SharePoint evaluates it as intended [sharepains.com]. If all you have are and conditions, parentheses are optional but can improve readability.
Function Operations – OData provides functions for partial matches and date parts. Two commonly used string functions are:
- startswith(<Field>, '<value>') – checks if the field value begins with a substring. E.g. startswith(Title, 'Project') is true for "Project Alpha" and "Project123", but not "New Project".
- substringof('<value>', <Field>) – checks if the field value contains a substring anywhere [teamdynamix.umich.edu]. Note: the substring goes first, the field second (this trips people up). E.g. substringof('Project', Title) is true for "New Project Plan" (because Title contains "Project").
  Both functions are case-sensitive in SharePoint OData. If casing is an issue, you might need to ensure consistent casing in your data or use a workaround (SharePoint’s REST API doesn’t support a tolower() function in filters). There are also date functions like year(), month(), day() that can extract parts of dates for filtering (e.g. year(Created) eq 2025), but such functions can prevent the query from using indexes – use sparingly.
Case Sensitivity – In OData syntax, the field names and operators are case-sensitive (as noted, use exact internal name and lowercase operators). The string values in comparisons are usually case-insensitive for SharePoint text columns (SharePoint filtering of text is not case-sensitive by default in most cases). For example, Title eq 'Project A' should match "project a" in a Title field. However, functions like substringof and startswith are case-sensitive for the substring. It’s safest to match the exact case or use consistent casing when storing data.
Flow Expression Injection – You can dynamically build filter queries using flow expressions. For instance, to filter by the current date or a value from a previous step, you might use an expression inside the quotes. Example: StartDate ge '@{utcNow()}'. Be mindful of the quotes – when the flow runs, the expression will be replaced with a value. The resulting string still needs to be a valid OData query. Often you’ll see date expressions combined with the datetime'...' wrapper, like: Created ge datetime'@{formatDateTime(utcNow(),'yyyy-MM-ddTHH:00:00Z')}'. Keep the syntax exact (the entire datetime literal in single quotes).

Delegation and List Thresholds: A big “gotcha” when filtering SharePoint data is the list view threshold (5000 items). SharePoint can only efficiently filter by indexed columns beyond 5000 items. If your filter uses a column that isn’t indexed and the list is very large, the query may fail or time out. To avoid this, index the columns you plan to filter on (you can set this in List Settings > Indexing). Yes/No, Number, Date, Single-choice, and Lookup (single-value) columns can be indexed. Multi-value fields (like multi-choice, multi-person, multi-lookup, managed metadata) cannot be indexed, so filtering on those in large lists will not work once the threshold is exceeded [teamdynamix.umich.edu]. In such cases, you might need to rethink your approach (filter on another indexed field first, or retrieve and post-filter in the flow). The filter queries themselves don’t have a “delegation limit” like in Power Apps, but they rely on SharePoint’s ability to handle the query, which is where indexing and supported operations matter.

With the general mechanics covered, let’s dive into examples for each column type. We’ll illustrate how to write the filter query and mention any quirks or pitfalls specific to that field type.

Filtering a Yes/No (Boolean) Column

SharePoint Column Setup: A Yes/No column is a boolean (True/False) field (rendered as a checkbox in SharePoint). Let’s say we have a “Active” column (Yes for active items, No for inactive). Internally, SharePoint stores Yes as true and No as false.

Filter Query (Yes/No Example): To filter items where Active is Yes (true), use:

Active eq 1

To filter where Active is No: Active eq 0.

Why 1 and 0? SharePoint’s OData implementation expects boolean values as numeric 1 (true) or 0 (false) in filter queries [teamdynamix.umich.edu]. If you try Active eq true or Active eq 'Yes', it will not work. The correct approach is to use 1 for “Yes/True” and 0 for “No/False” [teamdynamix.umich.edu].

Notes & Pitfalls:

Use 1/0, not true/false: As noted, this is specific to SharePoint’s OData. Even though the JSON output returns "Active": true or false, the filter query must use Active eq 1 or eq 0. This is a common source of confusion.
Internal name check: If your column is named Active (internal name likely “Active”), that’s straightforward. If it has spaces or special chars, ensure you use the internal name. E.g. a column "Is Completed?" might have internal name IsCompleted or Is_x0020_Completed (check in settings).
Index for large lists: Yes/No columns can be indexed. If you have thousands of items and plan to filter by this field, mark it indexed in the list settings to avoid threshold issues.
Filtering for “No” (false) vs blank: A Yes/No field always has a value (false by default if not set), so eq 0 covers both explicitly unchecked and “not set” (there is no null for Yes/No). If you want items where the box is unchecked, use eq 0. There’s no need for or Is null in this case.

Filtering a Date/Time Column

SharePoint Column Setup: Date or Date & Time columns store dates (with or without time portion). For example, a “DueDate” column (Date Only) or “EventDate” (Date & Time). SharePoint stores dates in UTC behind the scenes.

Filter Query (Date Example): Suppose we want items with DueDate on or after Dec 25, 2025. We would write:

DueDate ge datetime'2025-12-25T00:00:00Z'

This uses the ge (≥) operator and the date 2025-12-25 at midnight UTC. We wrap the date/time in datetime'...' as required by SharePoint OData for date comparisons [sharepoint.stackexchange.com]. If we wanted only those due on that exact date and our field is Date-only (no time), we could check for items between 2025-12-25 00:00 and 2025-12-25 23:59 using a combination (DueDate ge datetime'2025-12-25T00:00:00Z' and DueDate lt datetime'2025-12-26T00:00:00Z'). Often, though, you’ll filter relative dates (e.g. Created in last 30 days) using expressions.

Notes & Pitfalls:

Use datetime'...' format: SharePoint requires the datetime literal around ISO date strings in OData filters [sharepoint.stackexchange.com]. If you omit it (e.g. Created ge '2025-12-25'), the query will error or not behave as expected. Always include the time and Z (UTC) if using datetime in the query.
Time zones: The filter value should be in UTC (the Z at the end denotes Zulu/UTC time). SharePoint stores date/time in UTC. If your column is Date Only, the time is typically treated as 00:00:00 UTC on that date. Be mindful that ge datetime'2025-12-25T00:00:00Z' will include items from the entirety of Dec 25 in your local time if your time zone is behind/ahead UTC (because it’s inclusive from that midnight UTC). For most cases, that’s fine; just remember SharePoint’s comparison is in UTC.
Filtering “today” or dynamic dates: You can use flow expressions to insert dynamic dates. For example, to get items created yesterday, one approach is:
Created ge datetime'@{formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')}T00:00:00Z' and Created lt datetime'@{formatDateTime(utcNow(),'yyyy-MM-dd')}T00:00:00Z'
This looks complex, but essentially plugs in “yesterday 00:00:00” and “today 00:00:00” as the range (so everything from yesterday) [stackoverflow.com]. The key is formatting the date strings exactly and wrapping with datetime'.
Equality vs range: If you try Created eq datetime'2025-12-25T00:00:00Z', it will only match items created exactly at that moment (probably none). To get a specific date, use a range as shown or the day() function (if small lists). For a Date-Only field, you could also compare to the date string without time (some have reported DueDate eq '2025-12-25' working for Date-only fields, but the safest is to use the full datetime format or a range).
Indexing: Date columns are good candidates for indexing if you frequently query by date (e.g. filtering by Created or Modified date).
Delegation in formulas: If building the filter string with expressions, test the output to ensure it’s a valid ISO date. A common error is a mis-formatted date string or forgetting part of the syntax, resulting in an “Invalid filter” error. It helps to compose the date string in a Compose action and inspect it for correctness if you run into issues.

Filtering a Currency Column

SharePoint Column Setup: A Currency column stores numeric values (with a specific currency format, e.g. $ or €). For filtering purposes, it behaves like a Number column (the currency symbol is not stored in the data, just the number and currency locale). Example: “Budget” column storing project budget amounts.

Filter Query (Currency Example): If we want items with Budget over 5,000, we can use:

Budget gt 5000

This will return items where the Budget value is greater than 5000. Similarly, Budget eq 0 would find items with a 0 value, etc.

Notes & Pitfalls:

Treat as number: Even though it’s “Currency”, you do not include currency symbols or formatting in the query – just use the numeric value (no quotes). For example, use 5000, not '$5,000' (that would be a string and would not work).
Decimals: If your currency has cents/decimal, you can use decimals in the query. For example Price eq 19.99 or Price lt 100.5. Use a dot . for decimal separator.
Commas: Don’t use commas in the number (e.g. use 10000 not 10,000). Commas would be interpreted incorrectly or break the query.
Internal name: Watch out if your column name contains a currency symbol or spaces. For instance, a column named “Cost ($)” might have an internal name like Cost_x0020__x0028__x0024__x0029_ (SharePoint encodes special characters). Always verify the internal name. In our Budget example, if the display name is “Budget” then internal is likely also Budget.
Comparison operators: You can use any numeric operator on currency: eq, ne, lt, le, gt, ge. You can also use startswith or substringof on a currency field, but that’s unusual (it would treat the number as a string, e.g. startswith(Budget, '5') finds budgets starting with digit 5, meaning 5 or Fifty-something – probably not very useful!).
Null vs 0: If the currency field is optional and not filled in, those items have no value (null). To find items where Budget is blank, use Budget eq null. That is different from Budget eq 0 (which finds explicitly entered zero values).
Indexing: Currency (being a number column under the hood) can be indexed. For large lists, index it if you plan to query on it frequently.
Locale considerations: The filter query is not locale-aware – you must input the value as an invariant number. So if you’re in a locale that normally uses comma for decimals, still use a dot in the OData query.

Filtering a Lookup Column (Single-Value Lookup)

SharePoint Column Setup: A Lookup column links to an item in another list. For example, a “Category” lookup that points to a Category list. In SharePoint, a single-value lookup stores the ID of the related item (and displays a secondary value, typically the Title of that item). Internally, SharePoint actually exposes two pieces: CategoryId (the foreign key ID) and Category (a navigation reference to the item, which can be expanded to get the linked item’s fields like Title).

Filter Query (Lookup Example): There are two ways to filter a lookup:

By ID (best performance): Use the lookup’s internal name with Id appended. For example, to get items where Category points to the item with ID 3 in the Categories list:
```
CategoryId eq 3
```
This directly filters by the stored lookup ID. It’s simple and efficient (numeric comparison). Use this if you know the ID of the lookup target you want.
By the lookup value (text): Use the navigation property with a subfield, typically Title. For example, to get items whose Category’s Title is "Marketing":
```
Category/Title eq 'Marketing'
```
This instructs SharePoint to filter by the Title of the related item [teamdynamix.umich.edu]. Under the covers, it’s doing an $expand on Category and then filtering. This is convenient if you don’t know the ID but know the name. Make sure to use the internal name of the field you’re comparing – usually Title or Name of the target list’s item. (If the lookup is pointing to a list where the primary column is something else, use that. E.g. a lookup to a document library might use File/Name or to a custom list with a "Name" field you’d use that.)

Notes & Pitfalls:

Internal names and “Id” field: The internal name of your lookup column is whatever you see in the URL on list settings (e.g. Category). To filter by ID, append Id to that name – this is a special convention in SharePoint’s OData. You won’t see “CategoryId” in the list settings, but it exists for filtering. Using CategoryId eq 3 works because SharePoint has a hidden numeric field for the lookup ID [learn.microsoft.com]. Do not put quotes around the number (3 in this case).
Value vs ID tradeoff: Filtering by ID is exact and fast. Filtering by Title is user-friendly but can be problematic if the lookup value is not unique or if values change. If multiple categories have the same Title, Category/Title eq 'Marketing' will return items in any of those categories. If a category name changes, your filter might break or return nothing (since the items’ linked ID stays the same but the Title in the category list changed). So use Title filtering with caution.
Indexing and threshold: Single-value lookup columns can be indexed. If you have a large list and need to filter by a lookup, index the lookup column on the source list. However, note that SharePoint’s list view threshold has a secondary limit: a query can reference at most 8 lookup-type fields in a filter/query (lookup, person, choice, etc. count towards this in certain ways). Simple filters on one lookup are fine, but be wary of very complex queries hitting that limit. For large lists (>5000 items), prefer filtering by ID. SharePoint can utilize the index on the lookup ID field efficiently. Filtering by Category/Title might not be supported once the list is beyond threshold (and it could time out or fail if not indexed) [learn.microsoft.com]. If you suspect issues, try switching to ID filtering.
Null (no selection): To find items where the lookup is blank (no value selected), you can use CategoryId eq null. That will return items with no linked category.
Multiple conditions on lookup fields: If you want to filter on the lookup’s properties beyond just one field, note that you can only directly filter on the one field via the navigation (Title, or perhaps another field if you expanded it). For example, you cannot do Category/SomeOtherField eq 'X' unless the lookup column was configured to show that field (SharePoint only automatically exposes certain key fields). For more advanced lookup queries, you might need to use an HTTP request or filter by the ID then handle other conditions in the flow.
Example: Let’s say Category is a lookup to a Departments list that has Department Name (Title) and maybe a Code. If you want all items in Department with Code "HR", you can’t directly filter Category/Code eq 'HR' unless you set up the lookup to show Code (which SharePoint doesn’t do out of box – it only shows Title by default). A workaround: ensure the Title contains the code or create a calculated/combined field to filter on.
Pitfall – lookup values with special characters: If the Title has quotes or special characters, you need to escape them in the query. e.g. If a Category is Bob's Burgers, you’d write Category/Title eq 'Bob''s Burgers' (double single-quote to escape an apostrophe). This applies to any OData string.

Filtering a Choice Column (Single-Select)

SharePoint Column Setup: A single-select Choice column provides a dropdown of fixed options, but only one can be selected. For example, “Status” with choices Open, In Progress, Closed. In SharePoint, the value is stored as text (one of the allowed values).

Filter Query (Single Choice Example): To get items where Status is “Closed”, you’d write:

Status eq 'Closed'

It’s that straightforward – provide the choice value in quotes. If you need items where Status is not a certain value, use ne (e.g. Status ne 'Closed' for everything not Closed).

Notes & Pitfalls:

Exact match of text: The value in the query has to exactly match the choice text (case and spelling) as it exists in the list. Choice comparisons in SharePoint are case-insensitive (since underlying it’s just text), but to avoid confusion, match the case exactly. If your choice has a trailing space or other hidden character (rare, but can happen if someone added a space when creating the choice), that would matter – so double-check the choice spelling.
Internal name vs display name: Use the internal name of the column. If your choice column is “Project Status” (display name), the internal might be ProjectStatus or Project_x0020_Status. Use that in the filter (ProjectStatus eq 'Open'). If unsure, check the list settings URL for Field. Using the display name with spaces will cause an error.
Choices with special characters: If a choice value contains a comma, semicolon, etc., you do not need to escape those (only quotes need escaping). Just put the whole value in single quotes. Example: if a choice is "On Hold/Blocked", you’d do Status eq 'On Hold/Blocked'. The slash or spaces are fine inside the quotes.
Changing choice values: If someone renames a choice in the list settings, items that had the old value will now show the new value (SharePoint updates them). So your filter query should use the current value. If you filter by a choice that no longer exists in the allowed set, SharePoint will just return no items (or error if the value is really not valid text? Usually it just yields nothing).
Delegation/indexing: Choice columns are not lookup fields per se, and in SharePoint they can be indexed. If you have a large list, it’s a good idea to index important choice columns to enable server-side filtering beyond 5000 items. Filtering by a non-indexed choice on a huge list might hit the threshold (though if the first filter in query is on a non-indexed field, SharePoint will complain; sometimes combining with an indexed filter can offload some of it – but best to index the choice field itself).
Null or empty: A choice column can technically be empty if no default and user hasn’t set it (unless it’s required). To filter items where a choice column is blank, use Status eq null.
Multiple choice values (OR logic): If you need to get items that are either “Open” or “In Progress”, you can use or:
(Status eq 'Open') or (Status eq 'In Progress').
Note each condition in parentheses for clarity.
No wildcard in eq: There’s no wildcard or partial match in an eq query. You can’t do Status eq 'Clos*' or such. You’d need to use a function like startswith or substringof on a text field. For choices specifically, since the set is limited, it’s usually not needed – you know the exact values. If you ever needed to filter where choice contains something (maybe if choices are phrases), you could treat it as text: e.g. substringof('Closed', Status) – but that would also match "Closed - needs review" vs "Closed", etc. Typically, stick with eq/ne for choices.

Filtering a Choice Column (Multi-Select)

SharePoint Column Setup: A multi-select Choice column allows selecting multiple values from a set. SharePoint stores all selected options in one field, separated by ;# in the internal data (in modern API outputs, they may come as an array of values in JSON, but in OData query context it’s treated as a single concatenated string of choices).

Filter Query (Multi-Choice Example): OData does not have an “includes” operator, so filtering multi-select fields is tricky. You cannot directly ask for “contains this option” as a discrete condition. However, a common workaround is to use the substringof function to search the string of values. For example, imagine a multi-choice “Locations” field where an item could have “NY” and “LA” selected. To find items that have “NY” as one of the locations, you can try:

substringof('NY', Locations)

This will return true if the text "NY" appears anywhere in the Locations value string [reddit.comreddit.com]. In practice, that should catch items where "NY" is one of the choices (because SharePoint would store it like "NY;#LA" or "LA;#NY" internally). Another approach some use is an eq against the full string if you know the exact combination, but that is rarely useful unless you expect one specific combination of selections.

Notes & Pitfalls:

No native “includes” support: According to Microsoft, multi-valued fields can’t be directly queried by OData filter (there’s no equivalent to the CAML <Contains> for multivalue) [learn.microsoft.com]. The substringof hack is essentially treating the field as one long text. It works in many simple cases but it’s not officially guaranteed for every scenario.
Case sensitivity: substringof is case-sensitive. Ensure you match the case of the choice value exactly as stored.
False positives: Be careful – substringof('NY', Locations) will also match "ANY" or "Sydney" if those were part of the string. To mitigate, you might include the delimiter. E.g., if "NY" is a standalone choice, the stored string would have "NY;#" around it (if other values follow). You could search for 'NY;#' as a substring to be more specific. But if "NY" could be the last value, it might not have ;# after it. This is why this approach is a bit unreliable if choice values are substrings of each other or of other words. If your choices are distinctly different words or abbreviations, it’s usually fine.
Performance and delegation: This approach will likely not be delegable on large lists. SharePoint can’t use an index with substringof because it has to scan each item’s values. If your list is over 5000 items, a substring filter might just fail or return a partial set. There’s no official documentation on the cutoff, but treat it as non-indexed text filtering.
Alternative approaches: If you consistently need to query multi-choice fields and performance is an issue, consider maintaining a separate hidden text field (perhaps with a flow or SharePoint workflow) that concatenates the choices (e.g. "|NY|LA|"), then filter on that with a simple contains logic. Or break out the choices into multiple boolean columns if feasible (one column per key choice that’s true/false). These are design trade-offs – often we just filter after retrieving.
Filtering in flow instead: If you can’t get the OData filter to do what you want for multi-choice, you might retrieve items (maybe filtered by some other criteria if possible to narrow it) and then use the Filter array action in Power Automate to check if the value exists in the array of choices. This is an afterthought because it happens in memory in the flow, but it may be necessary for complex scenarios.
Example scenario: Suppose a list of events with a multi-select “Audience” choice (options like Youth, Adult, Senior). To find events for “Youth”, substringof('Youth', Audience) might work. But if one option was “Youth Sports” and another “Youth Education”, and you wanted specifically those with “Youth Sports”, substringof would also catch “Youth Education”. There’s unfortunately no straightforward way to do an exact contains for one multi-choice option via OData. You’d have to rely on the uniqueness of the strings or combine conditions to exclude undesired matches (which can get messy).
Testing multi-choice queries: It’s a good practice to test your filter on a small sample list or in the browser via the REST API to ensure it returns what you expect. Multi-choice filtering is not as foolproof as single-value filtering.

Filtering a Person (People Picker) Column – Single Person

SharePoint Column Setup: A Person or Group column (single-select) references a user (or SharePoint group). It’s essentially a special lookup to the hidden User Information list. In a list item, a Person field has an associated User ID (the SharePoint ID of that user in the site collection) and is often expanded to display the person’s Display Name (which appears in the column), plus other details like email, job title, etc., if requested.

For example, “AssignedTo” might be a single-person column for a Task’s assignee.

Filter Query (Person Example): The most reliable way to filter by a person field is to use one of the user’s properties. Commonly used: Email or Display Name. Email is usually unique and consistent, so let’s use that. To find tasks assigned to the user with email john.doe@company.com:

AssignedTo/EMail eq 'john.doe@company.com'

Here AssignedTo is the person field’s internal name, /EMail accesses the user’s email property, and we compare to the email string [tomriha.com]. Note: EMail has a capital “M” – that is the exact internal name of the email field in SharePoint’s user reference (it’s not a typo). Alternatively, we could filter by the display name (Title property). For example, AssignedTo/Title eq 'John Doe' (or 'Doe, John' depending on how names are displayed in your tenant) [teamdynamix.umich.edu]. However, display names can be non-unique (two John Smiths) and can change (if someone updates their profile to add middle initial, etc.), so email tends to be safer.

You can also filter by the user’s SharePoint ID using the pattern from lookup: AssignedToId eq 23 (if you know user 23 is John Doe). But User IDs vary by site and aren’t easy to obtain directly in flow without an extra lookup, so we usually stick to email or name.

Notes & Pitfalls:

Use a subfield (Email, Title, etc.): You cannot just do AssignedTo eq 'John Doe' or AssignedTo eq 'john.doe@company.com'. The person field is complex; you must specify which property to filter on. The most common are AssignedTo/EMail and AssignedTo/Title. Another property is AssignedTo/Id (the user’s ID in the User Info list), but if you have that ID, you might as well use AssignedToId eq ... as noted. There’s also AssignedTo/Department, AssignedTo/JobTitle etc., if the person column is configured to bring those in (or even if not, SharePoint’s REST might still allow querying them if the profile has them) [teamdynamix.umich.edu]. For instance, if you wanted all items where AssignedTo’s department is "Finance", you could do AssignedTo/Department eq 'Finance' [teamdynamix.umich.edu]. This will only work if the Department field is populated in Azure AD/M365 profile for those users. Keep in mind, not all user profile properties are accessible via this route, but commonly Department, JobTitle, etc., are.
Internal name of person field: If the field is "Assigned To" (with a space), internal might be AssignedTo or Assigned_x0020_To. Check the actual internal name. In many cases, SharePoint will strip spaces (AssignedTo).
Formatting the email string: Use the exact email address string. It’s case-insensitive (email addresses aren’t case sensitive). To be safe, use the same casing as in Azure AD (often all lower-case).
Display Name format: If using /Title, remember SharePoint person Title is the full name as displayed in the site. Many organizations use "First Last", but some (like in the example from Michigan Medicine) use "Last, First" [teamdynamix.umich.edu]. Check how it appears in your list. You must match it exactly. E.g., AssignedTo/Title eq 'Doe, John' including the comma if that’s how it shows. This is another reason email is easier.
Multiple person fields with same name: If your list has multiple person fields (like RequestedBy, ApprovedBy), be careful to use the correct one in the filter and not to confuse their values.
Current user filtering: A common need is “items assigned to the current user”. You might think of using a placeholder, but OData in SharePoint doesn’t support a direct current user token in filter [learn.microsoft.com]. The typical pattern is to use the user’s email from the flow context. For example, in a flow you could do: AssignedTo/EMail eq '@{user().email}' if running in a user context, or get the user’s email from a trigger or another action.
Indexing: Person columns count as lookup columns. You can index a single-value person column, which is recommended if you filter on it and have lots of items. This helps avoid the 5000-item threshold issue.
Delegation limits: As with lookups, queries on person fields might fail on large lists if not indexed. Also, if you query by a property like Department which isn’t indexed (the person field only indexes the user ID perhaps), SharePoint might struggle if the list is huge. But typically filtering by AssignedTo/EMail uses the underlying user ID index effectively (not guaranteed, but likely).
SharePoint groups: If the column allows SharePoint groups or if someone picks a group as the value, note that AssignedTo/Title would be the group name, and AssignedTo/EMail might be blank (groups don’t have an email). So if your person field is People and Groups, filtering by email might not catch group assignments. In those cases, filtering by Title (group name) could work. Example: Approver/Title eq 'Marketing Team Members' (name of a SharePoint group).
Pitfall – special characters in names: If someone’s display name contains a single quote, e.g., O'Connor, Jane, you’d need to escape it as O''Connor, Jane in the query string (similar to the earlier example for lookup/choice).
Advanced – filtering by multiple persons: If you want items assigned to either Alice or Bob, you can do: (AssignedTo/EMail eq 'alice@co.com') or (AssignedTo/EMail eq 'bob@co.com').
Example uses:
- Find item by exact person: as above.
- Find item where person’s property matches something else: e.g. tasks where AssignedTo’s Job Title is "Manager": AssignedTo/JobTitle eq 'Manager' (this assumes user profiles have Job Title field).
- These advanced queries are powerful but remember, they rely on data in the user profile at the time of query.

Filtering a Person Column – Multiple Selections

SharePoint Column Setup: A Person/Group column can be configured to allow multiple people. In SharePoint, multiple persons are stored similarly to multi-lookups: essentially an array of user entities (or a string of multiple names/IDs in older format). For example, an “Approvers” field where you can select several people.

Filter Query (Multi-Person Example): Filtering multi-person is challenging for the same reasons as multi-choice and multi-lookup. There’s no direct OData way to say “include this user”. However, SharePoint’s OData does allow a similar trick as lookup: you can try using the /EMail or /Title on the person field, which may evaluate true if any of the people match. For example, to find items where John Doe is one of the Approvers:

Approvers/EMail eq 'john.doe@company.com'

In many cases, this works – SharePoint will return the item if any of the Approvers has that email [community.powerplatform.comteamdynamix.umich.edu]. You could also try Approvers/Title eq 'John Doe' (with the same caveats about display name format as above).

Behind the scenes, it’s not well documented how this query is processed for multi-valued fields. The official documentation says multi-value lookups (which multi-person essentially is) cannot be directly queried [learn.microsoft.com]. But practically, many have found that using the /EMail or /Title syntax returns the desired items if the list is under threshold. It likely performs a scan and checks each person.

Notes & Pitfalls:

Not officially supported on large lists: The /EMail eq 'x' filter on a multi-person column will not use an index (you cannot index multi-person columns). If the list is large, SharePoint might refuse to run the query (it might give an error like “The query cannot be completed because it includes lookup columns and the total exceeds the threshold”). In smaller lists it should work fine; in very large lists it may fail or only return partial data without warning. Treat this as a convenience for small/medium lists, but not a robust solution at scale [teamdynamix.umich.edu].
Group membership not resolved: If a SharePoint group is in the multi-person field and you filter by a user’s email, that will not return items where the user is inside that group. OData filter doesn’t expand group membership. It only matches the user accounts directly in the field. Keep that in mind if your column allows groups.
Multiple matches: If the field has multiple people and more than one match your query (say you query by Department: Approvers/Department eq 'Finance' and an item has two people from Finance), that item still only appears once in results (it doesn’t duplicate).
Alternative approach for multi-person: Similar to multi-choice, if you often need to query “does this item include person X?”, an alternative is to maintain a hidden text field that concatenates all emails or IDs, and filter on that with substring. For example, a flow could maintain ApproversEmails = "john.doe@company.com;jane.smith@company.com". Then filter: substringof('john.doe@company.com', ApproversEmails). This is a workaround to simulate an “includes” search. It has the usual caveats of substring (case, partial matches).
Filter in flow approach: If OData filtering isn’t viable (say the list is too large or you need an exact match on one person in the multi), you might retrieve all items filtered by something else (or all if necessary, though that can be heavy) and then use a Filter array to keep items where the person array contains the target user. In Power Automate, the expression could be something like:
@contains([2,4,6,8], item('ID'))
(This example conceptually checks the array of ID's and includes if the ID exists.

CAML query via HTTP: As an advanced solution, you can always revert to SharePoint CAML query (via the SharePoint REST API call in an HTTP action) to leverage an <Includes> condition for multi-value fields. That’s beyond the scope of this OData-focused article, but it’s mentioned as a possible route if absolutely needed [teamdynamix.umich.edu].
Pitfall – multiple criteria on multi-person: If you try something like Approvers/Title eq 'John Doe' and Approvers/Title eq 'Jane Smith', that will likely never return anything – a single iteration of the field can’t be two values at once. If you want items where both John and Jane are in the Approvers, you have to use a different approach (OData filter can’t handle that logic directly on multi, since it doesn’t support an “AND includes” for multivalue). You’d need to fetch items with John, then filter those for Jane in a second step, for instance.
Summary: Use Field/EMail eq 'user@domain' for a quick filter on multi-person fields, but be cautious with scale. Always test with known items to ensure the query behaves as expected.

Filtering a Hyperlink (URL) Column

SharePoint Column Setup: A Hyperlink or Picture column in SharePoint consists of two parts: the URL and a Description. In list views, SharePoint might display the description as the hyperlink text (if a description is provided) or the URL itself if no description. Internally, the field is of type SP.FieldUrlValue, which has sub-properties Url and Description.

Filter Query (Hyperlink Example): Unfortunately, OData filtering on hyperlink fields is not supported [sharepoint.stackexchange.com]. There is no direct way to filter items by the URL or the description via the Get items Filter Query. For example, you might hope to do DocumentationLink/Url eq 'http://contoso.com/manual.pdf', but SharePoint will return an error or simply not filter correctly. The SharePoint REST API cannot filter on the Url or Description subfields using OData.

So, there isn’t a valid Field eq value syntax to demonstrate here – any attempt will result in “unsupported query”. If you absolutely need to query by a hyperlink, see the notes below for workarounds.

Notes & Pitfalls:

No OData support: Microsoft’s documentation and community consensus confirm that you cannot filter by hyperlink columns with the REST API filter. It’s a limitation [sharepoint.stackexchange.com]. This is because the field is complex and not treated as a simple queryable property.
Possible workaround via CAML: One workaround is using a CAML query (Collaborative Application Markup Language) inside an HTTP request. For example, you could use a SharePoint HTTP action with the CAML <Where><Eq><FieldRef Name="DocumentationLink"/><Value Type="URL">http://contoso.com/manual.pdf</Value></Eq></Where></Query> to filter by URL [sharepoint.stackexchange.com]. When combined with the REST API (_api/web/lists/GetByTitle('X')/GetItems(query)), this can retrieve filtered results. However, this is an advanced technique requiring manual crafting of CAML, and it must be done via the HTTP action (not the standard Get items).
Filter after retrieval: If you cannot use CAML, another approach is to retrieve items without filtering (or with some other filter to reduce scope) and then use a Filter array in Power Automate to check the hyperlink field. The flow can examine the FieldName object which typically has {'Url': 'http://...', 'Description': '...'} in the JSON. You could filter where item.DocumentationLink.Url contains or equals a certain string.
Partial matches: If you wanted to filter by those whose URL contains a domain or path (e.g., all links to contoso.com), you definitely can’t do that server-side with OData. Your best bet is pulling items and filtering in memory.
Description filtering: Similar story – you can’t do OData filter for the Description of a hyperlink field. If that’s needed, you’d again have to do an in-flow filter or CAML query.
Null/blank: One thing you can do with OData is check if the hyperlink field is empty. For example, DocumentationLink eq null will likely return items with no link (since the field value would be null). This works because checking for null doesn’t require looking into the Url property specifically – if there’s no hyperlink, the whole field is null. Conversely DocumentationLink ne null gives items where a link is present. This can be useful (and is supported).
Pitfall – trying to filter URL by text: A very common attempt is something like DocumentationLink eq 'http://contoso.com/manual.pdf'. This will not work (the API doesn’t magically apply that to the URL subfield).
Conclusion: When it comes to hyperlink fields, your strategy should be either to filter on a different field if possible, or handle it in the flow. If you must do it at the source, be prepared to use the HTTP action with CAML queries (which is beyond beginner level and has its own complexities).

Filtering a Managed Metadata (Tag) Column (Single Value)

SharePoint Column Setup: Managed Metadata columns (also known as taxonomy or tag columns) allow you to tag items with terms from a Term Store. Each value is a term with a unique ID (GUID) and a textual Label. In a single-value managed metadata column, each item can have at most one term selected. The data stored is a special taxonomy field type, and SharePoint also maintains hidden supporting columns (like a text field with “Term|GUID” and a hidden list that maps terms, etc.). Importantly, the SharePoint REST API doesn’t treat the term label or ID like a normal field for filtering.

Filter Query (Managed Metadata Example): Direct filtering on a taxonomy field is not possible with OData in Get items. If your column is “CategoryTag” and you try CategoryTag eq 'Financial' or any variant, it will not work (the field is complex, and SharePoint will reject the query).

However, there is a known workaround using a hidden column called TaxCatchAll. SharePoint’s Taxonomy feature populates a hidden multi-value field TaxCatchAll on each item with references to all terms used (across any taxonomy fields on that item). This field has sub-properties like Term (the term’s text) and ID (a lookup ID to a hidden Taxonomy list). We can leverage this to filter items that have a given term. For example, to find items where the CategoryTag is “Finance” (or any taxonomy field on the item contains "Finance"):

TaxCatchAll/Term eq 'Finance'

or 

TaxCatchAll/IdForTerm eq '[GUID of Finance]'

This will return items that have the term Finance applied [teamdynamix.umich.eduteamdynamix.umich.edu]. We’re essentially asking SharePoint: in the TaxCatchAll bag of terms for the item, is there one whose Term (text) equals "Finance".

Notes & Pitfalls:

Broad vs Specific: TaxCatchAll/Term eq 'Finance' doesn’t specifically target the CategoryTag column – it targets any term on the item with that label. If the item has another taxonomy column that also could have a “Finance” term, it would still be returned. In many cases, you might only have one taxonomy column, so it’s fine. If you have multiple, this query might over-include, in this case, using TaxCatchAll/IdForTerm will be the better option.
Single vs multi taxonomy: For single-value taxonomy fields, each item has at most one term in that field, but TaxCatchAll is still multi-valued (it holds an array of terms from all taxonomy fields). For multi-value taxonomy (covered next section), an item can have multiple terms from one field, but from TaxCatchAll perspective it’s all just additional entries in the same bag. So the approach using TaxCatchAll works for both single and multi – it’s basically “does the item have this term anywhere in its taxonomy fields”.
Term text vs GUID: We used the term’s text (label) in the query. That is often sufficient and more human-readable. But beware of duplicates: if two different terms in the term store have the same name (even in different term sets), TaxCatchAll/Term eq 'Finance' will fetch items tagged with either one. If you need to distinguish terms with the same label, you might need to filter by the Term’s ID (GUID). However, the TaxCatchAll data doesn’t expose the GUID directly, only an ID which is a lookup ID in a hidden list. Not trivial to use in OData. In such cases, using an HTTP request with CAML might be easier, where you can specify the value’s GUID in the query.
CAML/HTTP alternative: SharePoint’s CAML query can filter taxonomy fields by their GUID or by the term label within a specific field. If you absolutely need precision (e.g., “CategoryTag contains Finance term specifically”), you could consider an HTTP action with a CAML like:
<Where><Eq><FieldRef Name='CategoryTag'/><Value Type='Text'>Finance</Value></Eq></Where>
But actually, because taxonomy fields store a “Label|GUID” in a hidden note field, a CAML might need to use <Contains> on that hidden note. It gets complicated and often not worth it unless absolutely necessary.
Indexing and thresholds: Managed Metadata columns are treated like lookup columns (each term is essentially a lookup to the term store’s data). SharePoint does not allow indexing on a taxonomy field. This means any filter on taxonomy fields is non-indexed. If your list is beyond 5000 items, a direct filter like TaxCatchAll/Term eq 'Finance' may fail once the list hits the threshold (because it’s effectively filtering on a multi-value lookup without an index). If you expect a large list, consider filtering by some other criteria first (like a date or a location that narrows it down) and maybe combine with the term filter if possible. Or use the HTTP + CAML approach, which might handle larger sets (CAML can query by indexed columns first then filter by others).
Performance: Even on smaller lists, the TaxCatchAll approach might be slower than a normal filter, because it has to join with the hidden taxonomy data. But it’s usually fine for moderate sizes.
Security: Managed metadata filters via TaxCatchAll should only return items the user (or flow’s connection) has access to, just like normal filters.
Multiple terms in one item (single field): If an item has the term "Finance" and "Budget" in a multi-value field, the query Term eq 'Finance' will still return that item (because one of its terms is Finance). That’s expected. If you needed items that have both Finance and another specific term together, that’s beyond what OData can do directly – you’d need to filter one term and then post-filter for the other in flow, or something of that sort.
Pitfall – using the field name directly: Some might try CategoryTag/Label eq 'Finance' or CategoryTag eq 'Finance'. This will not work; the field doesn’t expose a property for label or ID via the OData filter. Always go through TaxCatchAll or an HTTP query.
Example: Let’s say you have a “Topic” column (taxonomy) and you want all items tagged with “Safety”. Use TaxCatchAll/Term eq 'Safety'. That will bring items where any taxonomy field has "Safety". If you have only the Topic field that uses such terms, you’re good. If you also have another taxonomy field “DepartmentTags” that could also have a "Safety" term (maybe in a different term set), those items will also come. If that’s a concern, and if at least the term sets are distinct enough that the term labels don’t overlap, you might be okay. If not, you may need to refine the approach (like filter by TermSet via the lookup ID, which might be possible by filtering TaxCatchAll/ID – but identifying the correct ID is complex since it refers to an entry in the hidden TaxonomyHiddenList).
No partial matches on term: TaxCatchAll/Term uses eq, so it’s exact match of the term text. There is no startswith or contains for terms in OData filter. If you needed something like “terms that contain 'Finance' in the name”, you’d have to retrieve and filter in flow.
Summary: For a beginner, the key takeaway is: You generally cannot filter directly on a Managed Metadata column. The practical solution for basic needs is to use TaxCatchAll/Term eq 'YourTermName' to get items tagged with that term [teamdynamix.umich.edu]. Just remember its limitations. If your scenario is too complex for this, it might be beyond “beginner” territory and require more advanced queries or design changes.

Filtering a Managed Metadata Column (Multi-Select)

SharePoint Column Setup: A multi-select Managed Metadata column allows multiple terms (tags) to be assigned to an item from the term set. This behaves conceptually like a multi-choice, but with terms. In an item’s data, it could have an array of terms, each with a label and GUID. SharePoint will list all those terms in the TaxCatchAll for the item.

Filter Query (Managed Metadata Multi Example): The approach is the same as for single-value: use TaxCatchAll. If we want items that have (at least) the term “IT Services” in a multi-tags field, we do:

TaxCatchAll/Term eq 'IT Services'

This will return any item that has "IT Services" as one of its tags (or any term with that exact text) [teamdynamix.umich.edu]. It does not matter if the column allows one or many – the query doesn’t change. It finds the term in the item’s term collection.

Notes & Pitfalls:

Inherits all the single-value notes: All the caveats mentioned for single-value taxonomy apply here as well: no direct field filtering, uses TaxCatchAll, watch out for duplicate term names, no indexing, etc. In fact, TaxCatchAll was designed to handle multi-value taxonomy, so it’s exactly what we leverage.
Multiple terms matching vs all terms: The query Term eq 'X' checks for at least one occurrence. If you need items that have all of a set of terms (e.g., items tagged with both "IT Services" and "HR"), OData can’t do an “AND” on two occurrences of TaxCatchAll easily because it would interpret it as needing one term that equals X and another term that equals Y in the same item. If you try something like (TaxCatchAll/Term eq 'IT Services') and (TaxCatchAll/Term eq 'HR'), the query will likely return nothing, because no single term in the catch-all can be both "IT Services" and "HR" simultaneously. The filter doesn’t understand it should look at two different terms. To achieve an “AND” of tags, you’d again have to retrieve items with one tag and then filter for the second tag in flow, or use a different strategy (maybe an alternate field that tracks combined tags, etc.). Generally, filtering by multiple required tags is advanced.
OR of terms: On the other hand, if you want items that have either "IT Services" or "HR", you can do (TaxCatchAll/Term eq 'IT Services') or (TaxCatchAll/Term eq 'HR'). This should return items that have either term (or both). It basically checks if there exists a term equal to IT Services or exists a term equal to HR. This is a valid approach for an “inclusive OR” of terms.
Performance of multiple or: Combining multiple OR conditions on TaxCatchAll might get slow if there are many terms. But if just a couple, it should be fine.
Term subsets: If you wanted to filter by any term under a certain parent in the term hierarchy – e.g., any term under "Technology" – OData filter won’t do that (you’d have to retrieve all and filter by term path maybe in code, or query the term store separately). That’s beyond OData scope.
Check for empty taxonomy field: If you want items where the taxonomy field has no value (i.e., no terms selected), you can filter on the column’s value being null. E.g. CategoryTag eq null might work (I say "might" because taxonomy fields often come through as an object – but if it’s empty it’s likely null). Alternatively, TaxCatchAll/Term eq ' ' (an impossible term) wouldn’t really help. Stick to CategoryTag eq null in the OData filter; that usually returns items with no tag in that field.
Examples: If a list item’s multi-tags have ["IT Services", "Networking"], the query Term eq 'IT Services' will catch it. The query Term eq 'Networking' will also catch it. Both together with OR will catch it (duplicate, but results are unique items anyway). An AND of both would miss it because no single term entry equals both (as explained).
Pitfall – partial term names: Just to reiterate, you cannot do partial matches like substringof('Service', TaxCatchAll/Term) in the Get items filter – it doesn’t support using functions on the TaxCatchAll multi-value like that (and likely would error out). So you need the full term name.
When taxonomy filter fails: If you attempt the TaxCatchAll approach on a list over 5000 items and get an error or empty result, you likely hit the threshold. There’s not an easy fix except to reduce scope or use other filters in combination. One trick: if your term column itself is configured as indexed in the term store (this is separate from list indexing – term store has something called tagging or pinned terms?), it doesn’t matter, SharePoint still doesn’t index at list item level for query. So no relief there. You could consider maintaining a shadow choice column that replicates the tags (if your term set is somewhat static and not too large). But again, that’s a workaround outside of pure OData.
Wrap-up: Use the TaxCatchAll technique for simple “has this term” queries, but manage expectations for more complex taxonomy queries.

Example: Combining Multiple Filter Conditions

So far we’ve looked at filtering by one column at a time. In real-world scenarios, you often need to filter by multiple criteria simultaneously – for instance, “Title contains X and Status is Y and Created after Z”. OData filter queries support combining conditions with and / or as discussed in the basics. Let’s build a full example:

Scenario: We have a SharePoint list of projects with columns: Title (Text), Status (Choice), StartDate (Date), and Approved (Yes/No). We want a view of projects where Title contains “Alpha”, Status is “Active”, StartDate is after Jan 1, 2023, and Approved is Yes.

Filter Query: We can write this as:

substringof('Alpha', Title) and Status eq 'Active' and StartDate ge datetime'2023-01-01T00:00:00Z' and Approved eq 1

Breaking it down:

substringof('Alpha', Title) – Title contains "Alpha" (anywhere in the title)
Status eq 'Active' – Status equals "Active"
StartDate ge datetime'2023-01-01T00:00:00Z' – StartDate on or after Jan 1, 2023 (we used ge with midnight of that date)
Approved eq 1 – Approved is Yes (remember 1 = true for Yes/No)

All conditions are combined with and, meaning an item must meet all four to be returned.

We could also add parentheses for clarity, but since it’s all ANDs, it’s not strictly necessary. It might look like: (substringof('Alpha', Title)) and (Status eq 'Active') and (StartDate ge datetime'2023-01-01T00:00:00Z') and (Approved eq 1) – which is equivalent.

Using this in Power Automate: You would paste this string into the Filter Query field of Get items. Ensure there are no extra quotes around the whole thing (the field itself should just contain the query, not "query" in quotes). Also, be careful with the single quotes inside the query when writing in Power Automate: You often need to use single quotes around values. In this example, the query already uses single quotes correctly. If you build it dynamically, don’t accidentally double quote them.

Things to note:

We utilized a text function substringof alongside simple comparisons. This is allowed.
If we needed an OR condition (e.g., Status is Active or Active (Delayed) perhaps), we’d use or, but then grouping becomes important. For example: (Status eq 'Active') or (Status eq 'Active (Delayed)') and Approved eq 1. Due to operator precedence, that query would be interpreted as everything with Status 'Active' or (Status 'Active (Delayed)' and Approved is Yes). If you intended the Approved Yes to apply to both, you should group: ((Status eq 'Active') or (Status eq 'Active (Delayed)')) and Approved eq 1. Always use parentheses to clarify intended logic.
Delegation considerations: If any part of this combined filter is non-delegable (for instance, substringof on Title in SharePoint is not indexed, but SharePoint does allow that filter up to 5000 items; it’s not strictly “delegation” since this is server-side already. Let’s just say if Title isn’t indexed and the list is huge, this might falter). If you had an indexed field in there (Status could be indexed if choice, StartDate could be indexed if date), SharePoint might still not execute if the first part of the filter it sees is substringof. Unfortunately, SharePoint’s query engine doesn’t always smartly use the indexed part when using functions on another part. A trick is to reorder conditions to put an indexed condition first (it sometimes helps). E.g., StartDate ge datetime'2023-01-01T00:00:00Z' and substringof('Alpha', Title) and Status eq 'Active' and Approved eq 1. It’s not guaranteed, but it might short-circuit some scanning. In any case, test on large lists.
Testing the combined filter: It can be helpful to first test simpler subsets (e.g. confirm substringof('Alpha', Title) returns expected, then add Status eq 'Active', etc.) to isolate if something is off. Common errors in combined filters are usually typos or missing quotes/parentheses.
Flow debugging: If the Filter Query is invalid, the Get items action will fail with a bad request error usually telling you “Invalid filter clause”. The error might point to the problematic segment. Double-check internal names and syntax in that case.

Applicability to “Get files” and others: The above example would work similarly in a Get files (properties only) action if those were columns on a document library (Title in a library might be the name or a separate Title field, Status and Approved could be custom columns, etc.). Just remember to adjust field names as needed. For file system, the filter might be on FileLeafRef (the internal name for file name) for example. But the syntax and idea remain OData compliant.

In summary, combining conditions lets you create powerful queries to slice your data in one go. Always use proper parentheses and be mindful of internal names. With a bit of practice, you can represent quite complex logic directly in the Filter Query, making your flow far more efficient than pulling everything and filtering later [sharepains.comteamdynamix.umich.edu].

Notes and Common Pitfalls

Before we conclude, here’s a summary of common issues and tips when using OData filter queries in Power Automate:

Internal Name vs Display Name: This bears repeating – always use the internal column name in the query [teamdynamix.umich.edu]. If you get no results or an error, double-check the internal name (especially if the column was renamed or has spaces/special chars). The internal name can be found in the list’s URL when editing the column settings.
Quotes and Data Types: Use single quotes around strings and date literals. Do not use quotes around numbers or booleans. If a value itself contains a quote, escape it by doubling ('O''Reilly' for O'Reilly). If you forget quotes around a text/date value, you’ll get an error “Incorrect type” or “Unexpected token”.
Case Sensitivity: OData operators and functions must be lowercase (eq, and, substringof, etc.). Also, function names like startswith and substringof are all lower case [teamdynamix.umich.edu]. Using StartsWith or SubstringOf will fail.
Boolean Values: Remember for SharePoint: true/false are not used in filter; use 1/0 for Yes/No columns [teamdynamix.umich.edu]. (In some other connectors like Dataverse, you might use true/false, but not in SharePoint’s case).
Null Checks: To filter blanks, use Field eq null or Field ne null. Don’t put null in quotes.
Multiple Choice/Lookup/People: These multi-valued fields don’t support direct equals comparison for a single value [learn.microsoft.com]. Use workarounds like substringof for multi-choice text or the /Email or /Title approach for multi-people, understanding the limitations as discussed.
Delegation and List Threshold: If your filter query isn’t returning items you know exist, consider the list size and indexing. It could be that SharePoint is refusing to execute the query due to threshold. Check if the field is indexed. If not, try indexing it (if possible) or filtering on an indexed field in addition. Or break the query into two (e.g., filter by an indexed column in OData, then further filter in the flow for the finer condition).
Test incrementally: If you have a complex filter that isn’t working, build it up piece by piece. Test each part in isolation (maybe in a separate Get items or using the REST API in a browser) to ensure each condition works, then combine them.
Special Columns: A few columns have unique internal names or behaviors:
- Title field (default title) internal name is Title (easy).
- The ID field (item ID) internal name is ID (all caps). You can filter by ID, e.g., ID eq 100.
- For document libraries: the Name (file name) is stored in the FileLeafRef field (internal). So to filter files by name: FileLeafRef eq 'Report.pdf'.
- The file’s Title (which is a separate field, often blank unless set) is just Title.
- “Modified” date internal is Modified, “Created” is Created (simple enough). But “Modified By” (a person field) is Editor internally, and “Created By” is Author internally.
- If you use those in filter: e.g., Author/EMail eq 'bob@co.com' for created by Bob.
No wildcards: There’s no wildcard * or ? in OData filters. Use substringof or startswith for text contains or prefix matching.
Max characters: The Filter Query field in Power Automate can hold quite a long string, but extremely long queries might hit a URL length limit on the SharePoint API (because behind scenes it’s constructing a REST URL). This usually isn’t an issue unless you try to filter on something like 50 values with ORs. If you find you’re approaching a URL length issue, consider alternative designs (maybe break into multiple queries or use a different method).
Encoding: Power Automate should handle encoding of special URL characters for you (like spaces become %20 automatically in the call). If you manually put %20 in your filter string it’s not necessary – just use the raw characters as described (spaces, etc., within quotes).
Error messages: An “Invalid filter clause” error usually means syntax error or invalid field name or value type. An “Unsupported or invalid filter” might mean you used an unsupported operation (like filtering on a hyperlink or multi-lookup). A “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold” means your query tried to use too many lookup fields on a large list – reduce complexity or number of lookup columns referenced.
Other actions: Remember, you can use the same filter syntax in Get files (properties only) for libraries, in Get items for Office365 Groups (though their fields differ), and in other connectors like Outlook messages (fields differ and not all functions are available). Always refer to that connector’s documentation for specifics. For SharePoint, Microsoft’s docs on OData filters are the go-to reference [learn.microsoft.com].

By keeping these tips in mind, you’ll avoid most of the common frustrations with OData filter queries in Power Automate.

References

Microsoft Learn – Using OData Filters in SharePoint REST: Official documentation on OData query operations in SharePoint REST API, including supported $filter operators and examples [learn.microsoft.comlearn.microsoft.com]. This covers how SharePoint handles filters for lookup fields, users, etc., and the limitations (e.g. no filtering multi-value lookups directly).
TeamDynamix Knowledge Base – “How to use OData query filters in the SharePoint Get Items action for Power Automate”: Gabriel Mongefranco’s detailed tutorial with examples for people fields, lookup fields, taxonomy, yes/no, etc. It explains using TaxCatchAll for tags and cautions about multi-lookup filtering [teamdynamix.umich.eduteamdynamix.umich.edu]. A great resource for advanced SharePoint filtering scenarios.
Tom Riha – OData Filter Query Cheat Sheet: A handy PDF cheat sheet that lists filter query syntax for various SharePoint column types (e.g., Yes/No as Field eq 1, Person as Person/Email eq 'value', Lookup as FieldId eq 123) [tomriha.comtomriha.com]. Good for quick reference when you forget the exact syntax.
SharePains by Pieter Veenstra – “SharePoint OData filter query in Get items actions in Power Automate”: A blog post illustrating the benefits of server-side filtering to optimize flows [sharepains.com]. Includes tips like using parentheses in combined filters and examples of common filter strings. Helps reinforce best practices to avoid the “wrong way” of filtering after retrieval.
SharePoint StackExchange – Filtering Hyperlink Columns: Community Q&A confirming that direct OData filtering on Hyperlink (URL) fields is not possible [sharepoint.stackexchange.com]. Provides a CAML query workaround for those who need to filter by URL via the REST API. Useful to understand platform limitations.
Power Automate Community Forum – Multi-Select Filtering Discussions: Various threads (on Reddit and Power Users forum) discuss the challenge of filtering multi-select choice or people fields. For instance, using substringof for multi-choice [reddit.com] and why an eq query might not work. These discussions reinforce why workarounds are needed and what their trade-offs are.

By mastering OData filter queries, you can make your Power Automate flows far more efficient and maintainable. Instead of pulling massive datasets and looping, you let SharePoint do the heavy lifting. The syntax can be tricky at first, but with resources like this guide (and the references above), you’ll soon be crafting filter strings like a pro. Happy automating! [sharepains.comteamdynamix.umich.edu]

Citations

SharePoint OData Filter query in Get items action in Power Automate flow in 2025

https://sharepains.com/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

sharepoint online - Filter Created in SP REST API - SharePoint Stack Exchange

https://sharepoint.stackexchange.com/questions/255543/filter-created-in-sp-rest-api/255555#255555

SharePoint OData Filter query in Get items action in Power Automate flow in 2025

https://sharepains.com/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

In Power Automate flow, how to Get Items from SharePoint List that were created yesterday? - Stack Overflow

https://stackoverflow.com/questions/76927029/in-power-automate-flow-how-to-get-items-from-sharepoint-list-that-were-created

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Use OData query operations in SharePoint REST requests | Microsoft Learn

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

Use OData query operations in SharePoint REST requests | Microsoft Learn

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

Get Items filter query multi-choice column : r/MicrosoftFlow

https://www.reddit.com/r/MicrosoftFlow/comments/14r39mm/get_items_filter_query_multichoice_column/

Get Items filter query multi-choice column : r/MicrosoftFlow

https://www.reddit.com/r/MicrosoftFlow/comments/14r39mm/get_items_filter_query_multichoice_column/

https://tomriha.com/wp-content/uploads/2021/03/FilterQuery-Cheatsheet.pdf

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Use OData query operations in SharePoint REST requests | Microsoft Learn

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

OData Filter Query on Person Field - Power Platform Community

https://community.powerplatform.com/forums/thread/details/?threadid=0ee73ab2-c2ae-4a50-89e0-b2179720ee69

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Filter Hyperlink field url using SharePoint REST API - SharePoint Stack Exchange

https://sharepoint.stackexchange.com/questions/224828/filter-hyperlink-field-url-using-sharepoint-rest-api

Filter Hyperlink field url using SharePoint REST API - SharePoint Stack Exchange

https://sharepoint.stackexchange.com/questions/224828/filter-hyperlink-field-url-using-sharepoint-rest-api

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

Article - How to use OData query filt...

https://teamdynamix.umich.edu/TDClient/210/DepressionCenter/KB/ArticleDet?ID=10904

SharePoint OData Filter query in Get items action in Power Automate flow in 2025

https://sharepains.com/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/

Use OData query operations in SharePoint REST requests | Microsoft Learn

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

Use OData query operations in SharePoint REST requests | Microsoft Learn

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

https://tomriha.com/wp-content/uploads/2021/03/FilterQuery-Cheatsheet.pdf

All Sources

Using SharePoint’s validateUpdateListItem in Power Automate: A Beginner’s Guide

2025-06-19T12:05:00.002+03:00

When working with SharePoint Online and Power Automate (Flow), you might reach a point where the standard “Update item” action isn’t enough. Perhaps you need to update system-managed fields (like Modified or Created By), or you want to update an item without triggering other flows or incrementing version history. Enter the SharePoint REST API’s validateUpdateListItem endpoint – a powerful method to update list items with fine control. In this beginner-friendly guide, we’ll walk through how to use validateUpdateListItem via the Send an HTTP request to SharePoint action in Power Automate. We’ll cover how to set up the HTTP request, the JSON body format (with examples for various column types), and explain the benefits of this approach.

What is `validateUpdateListItem` and Why Use It?

The validateUpdateListItem is a SharePoint REST API method that validates and updates list item fields in one call. It’s often used for “system updates,” meaning changes that don’t trigger flows/alerts and can bypass certain restrictions. For example, a normal item update will increment the item’s version and can trigger any flow listening on item modifications. In contrast, using validateUpdateListItem with the right parameters can perform a silent update (no new version, no flow trigger) [sharepains.comsharepains.com]. This is useful to avoid infinite update loops or to write back a status flag without causing another flow run [sharepains.com].

Benefits of validateUpdateListItem:

Bypass Triggering Flows: Updates done as a “system update” won’t trigger Flow triggers or alerts, preventing feedback loops [sharepains.com].
No Version Bump (Optional): By setting a flag, you can update an item without increasing its version count [robertschouten.com]. This is similar to SharePoint’s UpdateOverwriteVersion in server object model [robertschouten.com]. (We’ll see how to do this with bNewDocumentUpdate parameter.)
Update System Fields: It allows setting fields like Modified, Editor (Modified By), Created, Author (Created By) by passing their internal names and values in the JSON. This is something the standard “Update item” action can’t do directly.
Easier Field Value Format: You often supply values “as if” entered in SharePoint’s UI, rather than constructing complex field objects [sharepains.com]. This makes updating complex field types (people, taxonomy, etc.) easier than some other APIs.

Now, let’s dive into how to call this endpoint from Power Automate.

Setting Up the HTTP Request in Power Automate

To use validateUpdateListItem in Power Automate, we’ll use the Send an HTTP request to SharePoint action. Follow these steps:

Add the HTTP action: In your flow, add a new action and select SharePoint – Send an HTTP request to SharePoint.
Site Address: Choose the SharePoint site URL where your target list or library resides (e.g. https://contoso.sharepoint.com/sites/project-x).
Method: Set the HTTP method to POST (since we are sending data to update the item).
URI: Enter the REST API endpoint for your list item. The format is:
```
_api/web/Lists/GetByTitle('<ListName>')/items(<ItemID>)/validateUpdateListItem
```
Replace <ListName> with the exact list name (or use GetById(listGuid) if preferred) and <ItemID> with the ID of the item you want to update. For example:
_api/web/Lists/GetByTitle('Projects')/items(42)/validateUpdateListItem.
(Note: You can also use GetList(@list)/items(ID)/validateUpdateListItem?@list='<list server relative URL>' if dealing with libraries, but the above format is simplest.)
Headers: Add the following HTTP headers:
- Accept: application/json; odata=nometadata
- Content-Type: application/json; odata=nometadata
  These headers ensure SharePoint returns JSON and that our request body is treated as JSON. (You can also use odata=verbose or minimalmetadata – just be consistent in both accept and content-type.)
- Authorization: (Not explicitly needed – the action uses your connection’s credentials automatically.)
- IF-MATCH and X-HTTP-Method: Not required here – validateUpdateListItem handles concurrency internally without needing an eTag.
Body: This is where we specify what fields to update and their values, in JSON format. We will detail this in the next section. At a high level, the JSON body must include a formValues array of field updates, and optionally the bNewDocumentUpdate flag.

At this point, your HTTP action setup should look similar to the screenshot below.

Figure: Example configuration of the Power Automate HTTP request to SharePoint, pointing to the validateUpdateListItem endpoint (Site Address, POST method, URI with list name and item ID, and JSON body). The body includes a formValues array of field names/values and a bNewDocumentUpdate flag.

Understanding the JSON Body Structure

The body for validateUpdateListItem must be a JSON object with at least a formValues property. This formValues is an array of objects, each with two required properties: "FieldName" and "FieldValue" [sharepains.com]. For example, to update a Title field you could have:

{
  "formValues": [
    {
      "FieldName": "Title",
      "FieldValue": "New Title Value"
    }
  ],
  "bNewDocumentUpdate": false
}

Let’s break down the components:

FieldName: This should be the internal name of the column you want to update. Often, the internal name is the same as the display name if it has no spaces or special characters. If your column’s display name has spaces or special characters, use the internal name (e.g. "Created_x0020_By" for “Created By”, or "Project_x0020_Name" for “Project Name”). You can find internal names in list settings or via developer tools.
FieldValue: The value to set the field to, as a string. The format of this value depends on the field’s type (detailed in the next section). Generally, you provide what you would type into the SharePoint UI for that field. For example, for a Choice field, use one of the choice text options; for a Yes/No, use a numeric representation of True/False, etc. We will cover specific formats for each type.
bNewDocumentUpdate: (Optional) A boolean flag (true/false) that, when true, tells SharePoint to do an “overwrite” update that does not create a new version [robertschouten.com]. Essentially, if bNewDocumentUpdate: true, SharePoint will perform the update without bumping the version number (similar to a system update). Use this if you want a silent update that doesn’t clutter version history. Important: To fully suppress a version increment, you also must include the Editor field in your formValues (see “Person (Editor) Field” below for why) sharepains.com]. If you omit Editor, a new version may still be created even if this flag is true. For normal updates where versioning or triggers aren’t a concern, you can set this to false or omit it (false is default).

Putting it together, a basic example might look like:

{
  "formValues": [
    { "FieldName": "Title", "FieldValue": "Updated Title" },
    { "FieldName": "Status", "FieldValue": "Completed" }
  ],
  "bNewDocumentUpdate": true
}

This would update a Title column and a Status choice column on the item, and do so as a system update (no new version, assuming Editor is also included or not needed to change).

Next, we’ll explore how to format the FieldValue for various SharePoint column types. This is crucial – each field type expects the value in a specific format.

Updating Different Column Types with `validateUpdateListItem`

Each SharePoint field (column) type may require a slightly different FieldValue format in the JSON. Below, we’ll go through common field types listed in the question and show how to update them via validateUpdateListItem. For each, we assume you have a column of that type on your list, and we’ll describe what the JSON needs to look like.

Yes/No (Boolean) Columns

Column type: Yes/No in SharePoint corresponds to a boolean (True/False) value. In list forms, it appears as a checkbox or toggle (checked = Yes/True, unchecked = No/False).

FieldName: Use the internal name of the Yes/No field. For example, if the column is called "Approved", the internal name might be "Approved" (unless it was renamed).

FieldValue format: Use "1" for Yes/True or "0" for No/False. These should be provided as strings (since the API expects string input). For example:

{ "FieldName": "Approved", "FieldValue": "1" }

This would set the Approved column to “Yes”. Conversely, "FieldValue": "0" sets it to “No”. Do not use literal true/false or "Yes"/"No" strings – the API expects numeric string representation (1/0) for booleans [gist.github.com].

Common pitfall: If you use "true"/"false" or other variations, SharePoint may not understand it and your request could fail or produce no change. Always stick to "1" or "0" for Yes/No fields.

Date and Time Columns

Column type: This can be a Date column (Date only) or DateTime column (Date with Time). SharePoint stores dates in a specific format and will parse the string you send according to regional settings if not in ISO format.

FieldName: Use the internal name of the date field (e.g. StartDate, Deadline, etc.).

FieldValue format: It’s best to use an unambiguous date/time format. The recommended approach is to use a ISO 8601-like format, e.g. "YYYY-MM-DD HH:MM:SS". A full ISO timestamp with “T” and “Z” (UTC designator) can sometimes be accepted, but often SharePoint expects a specific pattern without the “T”. For example, "2025-06-19 13:30:00" represents 13:30 on 19 June 2025. This format is generally independent of locale [stackoverflow.com] and avoids errors. If your column is date-only, you can provide just the date portion ("YYYY-MM-DD"). If it’s date-time and your SharePoint site is set to a 12-hour format, you might need to include AM/PM (e.g. "2025-06-19 01:30:00 PM"). In 24-hour format locales, "2025-06-19 13:30:00" works universally.

For example:

{ "FieldName": "StartDate", "FieldValue": "2025-06-19 09:00:00" }

This sets the StartDate to June 19, 2025 9:00 AM.

In one real example, a SharePoint flow updated a date field by sending "2024-01-05 12:30.44" [sharepains.com] (which was the value as shown in the SharePoint UI for that date/time). While that worked, it’s safer to stick to the standard YYYY-MM-DD HH:MM:SS format to avoid any confusion with regional date formats.

Common pitfalls:

Regional settings can affect parsing. If you use a format like "MM/dd/yyyy" vs "yyyy-MM-dd", some sites might expect one or the other. By using the ISO-style year-first format, you reduce ambiguity.
Ensure the time portion is included if the field expects time; if your value doesn’t include time and the field does, it might default to 12:00 AM or throw an error depending on context.
If you include a “Z” (UTC) or timezone offset, SharePoint might still interpret the time relative to the site timezone or reject it. Thus, using the site’s local format (or a neutral format as above) is often easier.

Currency Columns

Column type: Currency columns store a monetary value. Under the hood, SharePoint treats currency as a number (with fixed decimal places) plus it knows the currency locale (e.g. USD, EUR) configured in the column settings.

FieldName: Use the internal name (e.g. Budget, Cost). If the column name has spaces like "Project Cost", internal might be Project_x0020_Cost.

FieldValue format: Provide the numeric value (as a string) without any currency symbols. For example, to set a currency field to $1,234.50, you would use "1234.50". The currency symbol is determined by the column’s locale settings, not by the input value. If your number is whole, you can just send "100" (which would be $100.00 if the currency is USD, for instance). For example:

{ "FieldName": "ProjectCost", "FieldValue": "1234.5" }

This sets the ProjectCost to 1234.50 in whatever currency the column is configured (the UI will format it accordingly). You can include two decimal places explicitly (e.g. "1234.50") but SharePoint will handle the rounding based on the column’s configured decimal precision.

Common pitfalls:

Do not include currency symbols like $ or £ in the FieldValue – use only numbers and decimal point. Including symbols or commas (thousand separators) will likely cause a format error. For instance, use "10000" not "10,000" and not "$10000".
The value should be in the currency’s minor unit. For example, for USD 1.00 use "1.00" (if 2 decimal places are used) or just "1" and SharePoint will format to $1.00.
If your currency column uses 4 decimal places (for certain currencies), ensure you provide the appropriate precision in the string.

Lookup Columns

Column type: Lookup columns reference an item from another list. A single-value lookup stores one referenced item; a multi-value lookup can store multiple.

FieldName: Use the internal name of the lookup field. (E.g. if the column is “Category” that looks up to a Categories list, FieldName might be Category.)

FieldValue format (Single-value Lookup): Provide the ID of the target item (in the lookup target list) as a string. For example, if you want to set the lookup to point to the item with ID 7 in the source list, use "7". The associated text (like the Title of the looked-up item) is not needed here – SharePoint will resolve it from the ID. Example:

{ "FieldName": "Category", "FieldValue": "7" }

This would set the Category lookup to the item with ID 7 in the source list.

FieldValue format (Multi-value Lookup): Provide all the target item IDs in a single string, separated by the special delimiter ;#. The pattern is each ID followed by ;#, concatenated. For example, to set a multi-lookup field to items with IDs 2, 3, and 4, you would send:

{ "FieldName": "Categories", "FieldValue": "2;#;#3;#;#4;#" }

Breaking that down: "2;#;#3;#;#4;#" – here each ID is followed by ;#, and the IDs are separated by an extra ;#. This format may look odd, but it’s how SharePoint expects multi-lookup values [robertschouten.compnp.github.io]. Essentially, for each ID, you append ;# after it, and then join them all into one string. In the above, it results in 2;#;#3;#;#4;#. (If you had IDs 2, 3, 4, 5, it would be "2;#;#3;#;#4;#;#5;#" and so on.)

Alternate format: Some sources indicate you can also supply the IDs separated by just ;# without doubles (e.g. "2;#3;#4"), but the reliable approach per documentation is the doubled delimiter format with trailing ;#. If in doubt, use the format as illustrated.

Common pitfalls:

Don’t use titles or text: Only IDs should be used in the FieldValue. If you try to put the lookup target’s text value, it will not find the item.
Ensure the ID strings are integers (no leading zeros or any other characters).
The ;# delimiter must be exact. Missing or extra delimiters will cause the update to fail or the field to not update correctly.
For multi-lookup, constructing the string can be tricky in Power Automate expressions. You might build an array of IDs and then join them with the pattern, or simply hardcode if static. (For a dynamic list of IDs, you could use an expression to join like join(body('Select')?['ID'], ';#;#') & ';#' to generate the string.)

Choice Columns (Single and Multi-choice)

Column type: Choice columns allow one or multiple selections from a predefined set of options. A single-choice column stores one value; a multi-choice stores one or more.

FieldName: Use the internal name of the choice field (e.g. Status, PriorityLevel, etc.).

FieldValue format (Single-choice): Use the exact text of the choice option you want to set. It must match one of the allowed choice values defined in the column settings. The comparison is case-insensitive to the actual choice text, but it’s good practice to match the case exactly. Example:

{ "FieldName": "Status", "FieldValue": "Completed" }

If “Completed” is one of the choices for the Status column, this will set it to that value [sharepains.com] (in the earlier example JSON, "DocumentType": "Letter" was used as a choice value [sharepains.com]).

FieldValue format (Multi-choice): Provide the chosen options as a single string, separated by ;#. For example, if a Tags field (multi-choice) allows values like "Red", "Blue", "Green", and you want to select Red and Green, you would use:

{ "FieldName": "Tags", "FieldValue": "Red;#Green" }

Each choice is separated by ;#. Unlike multi-lookup, you do not need an extra delimiter at the beginning or end for multi-choice. Just join the choices with ;# in between [gist.github.com]. If you have three choices, e.g. "Red", "Green", "Blue", you might send "Red;#Green;#Blue".

Common pitfalls:

The choice text must match exactly a valid option. If it doesn’t, SharePoint will throw an error (e.g. “Invalid data for field…”). Make sure there are no extra spaces or typos.
For multi-choice, do not include an extra ;# at the very end (some older documentation showed a trailing ;#, but it’s typically not required for multi-choice). For example, use "Option A;#Option B", not "Option A;#Option B;#".
Choice fields (especially multi) might have commas in their values. Don’t confuse the comma with the delimiter; only ;# works as a delimiter in this context. If an option itself contains “;#” (highly unlikely since SharePoint wouldn’t allow that in a choice text), it would be problematic. But normal words are fine.

Person or Group Columns (Single and Multiple)

Column type: Person/Group columns allow selection of a user or group from the SharePoint people picker. Internally, these are a special kind of lookup to the hidden User Information List. We must provide a claims-based identifier for the user or users.

FieldName: Use the internal name of the person field (e.g. AssignedTo, Owner). For the out-of-box Created By (Author) and Modified By (Editor) fields, the internal names are Author and Editor respectively.

FieldValue format (Single-person): Provide a JSON string that represents an array of one object with the user’s claim. That sounds complex, but effectively it’s:

"[{'Key':'i:0#.f|membership|user@domain.com'}]"

Yes, the FieldValue itself is a string containing JSON. Let’s break it down:

The value starts with [ and ends with ] – this denotes an array.
Inside, for each user, we have an object {'Key':'<claims-identifier>'}.
For SharePoint Online (Azure AD), the claim identifier format is typically i:0#.f|membership|user@domain.com. So if the user’s login/email is alice@contoso.com, the Key becomes "i:0#.f|membership|alice@contoso.com".

So for example, to set a Manager field (single-person) to Alice’s account:

{
  "FieldName": "Manager",
  "FieldValue": "[{'Key':'i:0#.f|membership|alice@contoso.com'}]"
}

This string is exactly what SharePoint needs to resolve the user. It’s essentially the same data you’d get if you were using the SharePoint people picker and selecting that user.

If you are on SharePoint on-premises (or older auth), the claim might look different (e.g. i:0#.w|domain\\username), but for SharePoint Online, the |membership|user@tenant format is correct [gist.github.com].

FieldValue format (Multi-person): Similar approach, but include multiple objects in the JSON array. For example, to set a Approvers multi-person field to Alice and Bob:

{
  "FieldName": "Approvers",
  "FieldValue": "[{'Key':'i:0#.f|membership|alice@contoso.com'},{'Key':'i:0#.f|membership|bob@contoso.com'}]"
}

Notice the FieldValue string contains two objects in the array, separated by a comma. No extra commas at the ends, and the entire thing is a string in JSON context. The Power Automate action will pass this string as is. SharePoint will parse it and assign those two users.

Special note – Editor/Author: If you want to do a system update without changing Modified By, you would include the Editor field and set it to the current modified-by’s claim, effectively re-setting it to the same value [sharepains.com]. For example, if you’re doing bNewDocumentUpdate: true, be sure to add:

{ "FieldName": "Editor", "FieldValue": "[{'Key':'i:0#.f|membership|john.doe@tenant.com'}]" }

(using whatever the current editor’s login is). Similarly, to set Created By, use Author field with the claim JSON. This is how some flows maintain Author/Editor so those don’t change on an update [learn.microsoft.comlearn.microsoft.com]. You typically need high permission (like site owner or at least item-level permissions that allow editing those fields) to update Author/Editor.

Common pitfalls:

JSON string formatting: Because the FieldValue itself contains quotes and braces, be careful to escape them properly if constructing in Power Automate. The examples above use single quotes inside the string for simplicity. You can also use double quotes inside and escape them. For instance: "[{\"Key\":\"i:0#.f|membership|alice@contoso.com\"}]" is the fully escaped version. If typing in the HTTP action as raw JSON, using single quotes for the internal JSON is a convenient workaround (SharePoint is fine with single or double quotes for the Key string).
Ensure the user exists in the site’s User Information List (i.e., they have been granted access or added to the site). If the email or login is not recognized, the API may return an error or “Multiple entries matched” error. Using the exact claim ensures uniqueness [learn.microsoft.comlearn.microsoft.com].
For group values, use a similar approach – the claim for a SharePoint group might be something like c:0o.c|federateddirectoryclaimprovider|<groupid> (for O365 groups) or sharepointgroup:Group Name in older formats. However, updating SharePoint groups via this method is less common; usually it’s user accounts.
If you only put the email without the claims prefix, e.g. '[{"Key":"alice@contoso.com"}]', SharePoint will try to resolve it, but it could fail or pick the wrong user if there are multiple claim providers. The safe route is always include the full claim as shown.

Hyperlink or Picture Columns

Column type: “Hyperlink or Picture” columns in SharePoint contain a URL and an optional description. In SharePoint list views, a hyperlink field might display as a clickable link (with description text), and a picture field displays an image.

FieldName: Use the internal name of the hyperlink field (e.g. Website, ImageLink, etc.).

FieldValue format: Provide the URL, followed by a comma and a space, then the description. The format is:

<URL>, <Description>

If you leave the description blank, SharePoint will use the URL as the display text. For example:

{ "FieldName": "Website", "FieldValue": "https://www.bing.com, Bing" }

This sets the Website column to the URL https://www.bing.com with the friendly description “Bing” [robertschouten.com]. In the list, it will show as a hyperlink “Bing” linking to the Bing website. If you wanted to not have a description, you could just provide "https://www.bing.com," with a trailing comma (or possibly just the URL alone). Including the comma is a clear way to indicate an empty description.

For a Picture column (which is essentially the same type under the hood), you would also use the same format: the image URL, then comma, then description/alternate text.

Common pitfalls:

Don’t forget the comma. The comma separates the URL and the description. If you omit it, SharePoint might try to treat the whole string as the URL (and fail if it’s not a valid URL format or just ignore the description).
If your URL or description contains a comma ,, that could confuse the parser. Generally, URLs don’t contain commas, but descriptions might. If your description has a comma, unfortunately this format becomes ambiguous. It’s safest to avoid commas in the description or you would need to experiment (it might not be possible to escape the comma in this context).
Ensure the URL is absolute (e.g. starts with http:// or https:// or mailto: etc., or is a server-relative link like /sites/xyz/doclib/file.docx). A relative URL might not resolve correctly.
For images, if you provide a link to an image file, SharePoint will just store that URL. The actual image is not uploaded by this action (you’d need a separate step if you want to upload an image and then reference it).

Managed Metadata (Taxonomy) Columns – Single and Multi-value

Column type: Managed Metadata columns (also known as taxonomy or term columns) allow you to tag items with terms from a term store. They can be single-value or allow multiple terms. These are one of the trickier field types to update via REST traditionally, but validateUpdateListItem makes it simpler by using a special string format.

FieldName: Use the internal name of the term field. If your column is "Department" (and connected to a term set of departments), the internal name might also be Department (unless changed).

FieldValue format (Single-value Metadata): The format is:

Label|GUID

followed by a trailing semicolon ;. Here:

Label is the term’s label (the text as seen by users).
GUID is the unique identifier of the term (the Term ID in the term store).

You should combine them with a pipe character | and then end with ;. For example, suppose you have a term “Finance” with GUID 12345678-90ab-cdef-1234-567890abcdef. To set the metadata field to “Finance”, you would use:

{ "FieldName": "Department", "FieldValue": "Finance|12345678-90ab-cdef-1234-567890abcdef;" }

The trailing semicolon is required even for a single value [gist.github.comrobertschouten.com]. It denotes the end of the term entry.

What about that mysterious -1 we sometimes see in examples? In some documentation, you might see formats like "Label|-1|GUID;". The -1 represents the WssId (a SharePoint internal ID for the term in the site collection’s hidden list) or a LCID. When -1 is used, it typically means “let SharePoint figure out the WssId”. In our context, you can actually omit the -1| portion – SharePoint will handle it if you just provide Label and GUID. However, some references include it. For simplicity, we’ll continue without it in our examples, as it usually works with just Label|GUID. If you find it not working, you can try including -1| right before the GUID. For instance: "Finance|-1|1234-...;" – both approaches are known to work [gist.github.com].

FieldValue format (Multi-value Metadata): For multiple terms, you concatenate each term in the string, separated by semicolons, and each term entry ends with a semicolon. For example, if you have a multi-value metadata field (let’s call it Tags) and you want to set two terms: “HR” (GUID aaaa...) and “Payroll” (GUID bbbb...), you would send:

{
  "FieldName": "Tags",
  "FieldValue": "HR|aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb;Payroll|bbbbbbbb-2222-3333-4444-cccccccccccc;"
}

So it’s Label1|GUID1;Label2|GUID2; – each term separated by ; and each has its trailing semicolon [gist.github.com]. Essentially, for N terms, you’ll have N semicolons in the string (one after each term’s GUID).

SharePoint will parse that and assign the two terms to the field. If any term is not found (e.g. typo in label or wrong GUID), that specific term might fail validation.

Common pitfalls:

The GUID must be the Term’s ID, not the Term Set ID or anything else. If you select a term in the SharePoint UI and use the developer tools or a query, you can obtain its GUID. Alternatively, if you only know the term label and it’s unique, SharePoint might resolve by label, but it’s not guaranteed or advisable. Including the GUID is the surest way.
Label is case-insensitive as far as matching the term, but it’s good to use the exact capitalization as the term. If the label has a “|” or “;” character in it (rare, but possible), that could break the format – hopefully your term labels don’t include those characters.
The format with -1|GUID or just GUID – both have been used in examples. Using -1 as the second token is essentially telling SharePoint “I don’t have a valid WssId, please look up the term by GUID and assign it”. SharePoint will then replace -1 with the correct WssId internally. Most of the time, you can skip the -1| and just do Label|GUID;. If you have issues, try the -1| in the middle. For example, the gist of field formats shows an example: "Department 2|-1|220a3627-4cd3-453d-ac54-34e71483bb8a;" [gist.github.com] – here Department 2 is the label, followed by -1| then the GUID, then semicolon. The -1 is not an ID of the term; it’s just a placeholder indicating no ID (so SharePoint should create/lookup one).
For multi-value, ensure the string is one continuous string. In some documentation, they show it on multiple lines for readability, but it actually should be one line. There should be no extra semicolon at the very end beyond each term’s terminator (though a trailing semicolon at the very end is fine and typically present).

Other Field Types (Quick Notes)

The question didn’t explicitly ask for others, but for completeness, a few other common types:

Text (Single line) and Note (Multiline text): These are straightforward – just put the text in FieldValue. E.g. "Description": "This is a sample text.". Multiline text can include line breaks \n if needed.
Number: Similar to currency but without currency symbol. Just provide the number as a string (or number). E.g. "FieldValue": "42.5" for a number column.
Yes/No (already covered) – use "1"/"0".
Calculated: You generally cannot set a calculated column (it’s computed).
GUID: Provide a GUID string if needed (rare). E.g. "FieldValue": "3cf2375d-5c8e-4efc-95a3-73a8b4e82102" for a GUID field.
Attachments: You can’t directly add attachments via this; you would use a different API to attach files. But you can set the Attachments field to 0 or 1 possibly (though it’s read-only indicating if attachments exist).

Now that we’ve seen all the formats, let’s piece it all together with how to incorporate this into the Power Automate flow and consider when to use this method.

Using the HTTP Action in a Flow – End-to-End Example

To illustrate, let’s say we have a SharePoint Online list called Projects with the following columns we want to update via validateUpdateListItem:

Title (Single line text)
Approved (Yes/No)
Start Date (Date)
Budget (Currency)
Category (Lookup to a Categories list)
Status (Choice)
Tags (Choice multi-select)
Project Manager (Person)
Related Teams (Person multi-select)
Website (Hyperlink)
Department (Managed Metadata single-select)

We want to update item ID 42 in this list.

Our HTTP request action would be configured as:

Method: POST
URI: _api/web/Lists/GetByTitle('Projects')/items(42)/validateUpdateListItem
Headers: (Accept and Content-Type as discussed)
Body: something like below (formatted for readability):

{
  "formValues": [
    { "FieldName": "Title", "FieldValue": "Project Apollo" },
    { "FieldName": "Approved", "FieldValue": "1" },
    { "FieldName": "StartDate", "FieldValue": "2025-07-01 00:00:00" },
    { "FieldName": "Budget", "FieldValue": "250000" },
    { "FieldName": "Category", "FieldValue": "3" },
    { "FieldName": "Status", "FieldValue": "Active" },
    { "FieldName": "Tags", "FieldValue": "High Priority;#Internal" },
    { "FieldName": "ProjectManager", "FieldValue": "[{'Key':'i:0#.f|membership|alice@contoso.com'}]" },
    { "FieldName": "RelatedTeams", "FieldValue": "[{'Key':'i:0#.f|membership|teamlead@contoso.com'},{'Key':'i:0#.f|membership|engineer@contoso.com'}]" },
    { "FieldName": "Website", "FieldValue": "https://contoso.com/projects/apollo, Apollo Internal Site" },
    { "FieldName": "Department", "FieldValue": "Research|d85a5c3f-447a-4d5c-9df4-4baf12345678;" },
    { "FieldName": "Editor", "FieldValue": "[{'Key':'i:0#.f|membership|<your login here>'}]" }
  ],
  "bNewDocumentUpdate": true
}

A few things to note in this example body:

We included Editor with the current user’s claim and set bNewDocumentUpdate: true. This means the update will not create a new version [sharepains.com] and by setting Editor to the same value, SharePoint will treat it as if the last modified by didn’t change, preventing a version bump [sharepains.com]. Essentially this achieves a system update (silent update).
We set Approved to "1" (Yes), StartDate in a standard format, Budget as a number, Category as an ID, Status/Tags with appropriate strings, and the complex fields with their required JSON strings.
If any value is invalid (say we put an incorrect GUID or a choice that doesn’t exist), validateUpdateListItem will return a response indicating which field failed and why. A successful response will return a JSON payload listing each field updated and ErrorMessage:null for each [robertschouten.comrobertschouten.com]. In Power Automate, you can capture the output and parse it if needed.

Comparing `validateUpdateListItem` with the Standard "Update item" Action

In conclusion, why go through the trouble of using validateUpdateListItem instead of the simpler built-in Update item action? Here’s a quick comparison:

Standard Update item:
- Pros: Very easy to use for basic updates. You just fill in the fields in the action and it updates them. No need to know internal names or REST endpoints.
- Cons: It cannot modify system fields like Modified or Modified By. It always counts as a user update (incrementing version and triggering flows). It doesn’t easily handle certain complex field types (for example, multi-select person or taxonomy) unless you supply data in the exact format anyway (and some fields like Managed Metadata can’t be set via Update item in Power Automate at all). Also, you cannot do “no version” updates with it – every call is a normal edit.
validateUpdateListItem via HTTP:
- Pros: Enables system updates – you can update an item without changing Modified By/date and without creating a new version (when used with bNewDocumentUpdate:true and setting Editor) [robertschouten.comsharepains.com]. This is perfect for scenarios like updating a status flag in the same flow that was triggered by an update (avoiding infinite loops). It also allows setting values that are otherwise read-only (like you could copy an old Modified date into the Modified field to essentially undo a timestamp change). It supports updating fields that Power Automate’s UI doesn’t support well (people fields require just an email in UI, but you might want to set multiple people, etc., which the HTTP method handles by the formats we used). Basically, it gives you full flexibility of SharePoint’s API.
- Cons: It’s more complex to set up. You have to form the JSON correctly, know internal field names, and handle the claims/ID formats. A mistake in the format can cause the update to fail. Debugging might require looking at the HTTP response. It’s also an “HTTP request”, which some business users shy away from, but it’s not too difficult once you have examples.

When to prefer validateUpdateListItem:

When you need to perform silent updates that shouldn’t retrigger your flow or bump version history (e.g., writing back a “Processed” flag on the item that triggered the flow) [sharepains.comtomriha.com].
When you need to update fields like Managed Metadata, Multi-person, or Hyperlink in one go – especially if the standard action doesn’t support them easily.
When you need to update Modified By/Modified (system columns) or do special things like “system update” for record-keeping reasons (perhaps to avoid showing a service account as the last editor). For instance, some solutions capture the original Modified date and user, then use this method to reapply them after making changes, so it’s as if nothing changed those fields [sharepoint.stackexchange.com].
If you want a single action to update many fields at once (the HTTP request can set multiple fields in one call, whereas you might otherwise use multiple actions or a single Update item action which may be fine but can’t do some of the above points).

When to use the normal Update item: If your update is straightforward (e.g., just updating a text or choice field) and you don’t need the special capabilities, the standard action is simpler and less error-prone. Use validateUpdateListItem only when you have a specific need that it addresses (or if you’re comfortable with it and prefer its flexibility).

Finally, always test your flow with sample data. If a field fails to update, inspect the HTTP response. The validateUpdateListItem returns a detailed result per field with an error message if something went wrong [robertschouten.com]. This can help you debug (for example, an invalid choice value or a wrong term GUID will produce an error message in the response for that field). You can see those by capturing the output of the HTTP action (it’s a JSON with a "value" array of results).

Conclusion: Using the SharePoint REST API’s validateUpdateListItem in Power Automate opens up advanced scenarios like system updates and updating complex field types. It requires a bit more JSON crafting, but with the examples provided above, you can update Yes/No, Date, Currency, Lookup, Choice, Person, Hyperlink, and Managed Metadata fields with ease. This approach is especially powerful for SharePoint Online where keeping flows efficient and avoiding unnecessary versioning is important. Once you get the hang of it, validateUpdateListItem can be a reliable tool in your Power Automate arsenal for SharePoint integration.

References

Pieter Veenstra – System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint (SharePains blog, 2024) – Explanation of using validateUpdateListItem for silent updates, importance of Editor field, and example JSON - sharepains.comsharepains.com.
Robert Schouten – Update file metadata with REST API using ValidateUpdateListItem method (2018) – Overview of the endpoint’s benefits and formats for taxonomy, person, hyperlink fields - robertschouten.comrobertschouten.com.
Phillip Harding (GitHub Gist) – SharePoint REST API Add/ValidateUpdateListItems – A cheat-sheet of FieldName/FieldValue formats for various field types (Yes/No, Choice, Lookup, User, etc.) - gist.github.comgist.github.com.
CLI for Microsoft 365 – spo file add documentation – Examples of updating metadata (person, date, URL, choice, yes/no, lookup, currency) via validateUpdateListItem under the hood - pnp.github.iopnp.github.io.
Stack Overflow – Update 'date' column using 'ValidateUpdateListItem' endpoint – Discussion on date format issues and recommendation of a locale-independent format (YYYY-MM-DD hh:mm:ss) for date/time values - stackoverflow.com.
SharePoint StackExchange – Various Q&A on using ValidateUpdateListItem for updating Author/Editor without changing Modified datesharepoint.stackexchange.com and handling person field claimslearn.microsoft.comlearn.microsoft.com. These reinforce how to format special fields in the JSON.

Citations

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

sharepoint - Update 'date' column using 'ValidateUpdateListItem' endpoint - Stack Overflow

https://stackoverflow.com/questions/71842491/update-date-column-using-validateupdatelistitem-endpoint

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

spo file add - CLI for Microsoft 365

https://pnp.github.io/cli-microsoft365/cmd/spo/file/file-add/

System Updates in SharePoint from Power Automate using the ValidateUpdateListItem endpoint

https://sharepains.com/2024/01/05/system-update-sharepoint-power-automate/

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

Sharepoint REST ValidateUpdateListItem return error response. - Microsoft Q&A

https://learn.microsoft.com/en-us/answers/questions/1018412/sharepoint-rest-validateupdatelistitem-return-erro

Sharepoint REST ValidateUpdateListItem return error response. - Microsoft Q&A

https://learn.microsoft.com/en-us/answers/questions/1018412/sharepoint-rest-validateupdatelistitem-return-erro

Sharepoint REST ValidateUpdateListItem return error response. - Microsoft Q&A

https://learn.microsoft.com/en-us/answers/questions/1018412/sharepoint-rest-validateupdatelistitem-return-erro

Sharepoint REST ValidateUpdateListItem return error response. - Microsoft Q&A

https://learn.microsoft.com/en-us/answers/questions/1018412/sharepoint-rest-validateupdatelistitem-return-erro

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

Update SharePoint column without new item version in Power Automate

https://tomriha.com/update-sharepoint-column-without-new-item-version-in-power-automate/

Can i update a list item without affecting the Modified By and ...

https://sharepoint.stackexchange.com/questions/314160/can-i-update-a-list-item-without-affecting-the-modified-by-and-modified-date

Update file metadata with REST API using ValidateUpdateListItem method – ROBERT SCHOUTEN

https://robertschouten.com/2018/04/30/update-file-metadata-with-rest-api-using-validateupdatelistitem-method/

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

SharePoint REST API Add/ValidateUpdateListItems · GitHub

https://gist.github.com/phillipharding/30714d4ee245bfc0cba5699b6bb4193e

spo file add - CLI for Microsoft 365

https://pnp.github.io/cli-microsoft365/cmd/spo/file/file-add/

All Sources

PowerApps Patch 101: Patching SharePoint Columns for Beginners

2025-06-19T09:49:00.002+03:00

Power Apps’ Patch function is a powerful tool that lets you create or update records in a data source through formulas [learn.microsoft.com]. In a SharePoint context, Patch enables you to save form inputs or custom values back to a SharePoint list without using the standard form submit. This is useful for building dynamic apps with custom logic (e.g. updating multiple lists at once, or partially updating a record) [learn.microsoft.com]. We typically use SharePoint as the data source for such examples because it’s readily available in most Microsoft 365 plans, whereas Dataverse (the premium alternative) requires additional licensing [powerapps911.com]. In short, SharePoint lists are a convenient starting point for new app makers, and Patch gives you flexibility beyond the built-in form controls.

In this beginner-friendly guide, we’ll introduce the Patch function syntax using modern Power Fx, then walk through patching various SharePoint column types. We’ll cover Date, Currency, Lookup, Choice (single and multi-select), Person (single and multi-select), Hyperlink, Yes/No, and Managed Metadata (single and multi-select) columns. For each, we’ll describe the SharePoint column setup, show the Patch formula in Power Fx, and note common pitfalls or errors. By the end, you should feel more confident using Patch to build dynamic Power Apps that work with SharePoint data. Let’s get started!

Patch Function Basics (Syntax and Usage)

In Power Fx (the formula language for Power Apps), the Patch function has a simple pattern:

powerfx

Patch(DataSource, BaseRecord, { Field1: NewValue1, Field2: NewValue2, ... })

DataSource is your connected data source (e.g. a SharePoint list).
BaseRecord specifies which record to modify. For updating an existing item, you provide a reference to that item (such as a record or an expression that finds it). For creating a new item, you use Defaults(DataSource) as the base record [rezadorrani.com].
The last parameter is one or more ChangeRecord objects: essentially a {Field: Value} mapping that defines which columns to update and their new values [rezadorrani.com]. Any fields not mentioned remain unchanged.

Examples:

Create a new record: Patch(Projects, Defaults(Projects), { Title: "New Project", Status: "Active" }) – this creates a new item in the Projects list with Title and Status set, leaving other fields at their defaults.
Update an existing record: Patch(Projects, LookUp(Projects, ID=5), { Status: "Completed" }) – this finds the item with ID 5 in Projects and updates its Status to “Completed”. You could also reference a record from a Gallery’s selection (e.g. Gallery1.Selected) in place of the LookUp for convenience.

Why use Patch? Patch is best when you need fine control or to update multiple sources at once. For simple forms, using an Edit form and SubmitForm is easier; but Patch shines for custom behaviors (e.g. saving different data on different button clicks, or updating part of a record without affecting the rest [learn.microsoft.com]). Patch also allows writing to multiple records at once (advanced scenario) and can be combined with formulas like ForAll to batch-update tables. In this post, we focus on single-record patches for various field types.

A note on OData types: Some SharePoint columns (like People, Choices, Lookups, etc.) are complex types. Under the hood, Patch needs special record structures (including an @odata.type and specific subfields) to update these [powerappsguide.com]. Manually constructing these can be intimidating for beginners. The good news is that if you use controls bound to the SharePoint data (via the Choices function), Power Apps will handle those structures for you. We’ll demonstrate both the manual format and the easier method using controls for complex columns.

With the basics covered, let’s dive into patching each type of SharePoint column.

Patching a Date Column

SharePoint Column Setup: A Date column in SharePoint can be configured as “Date Only” or “Date & Time”. For example, a “DueDate” field might store just a date (no time). Power Apps will treat SharePoint dates as DateTime values. If the column is Date Only, the time portion is ignored (or set to midnight). It’s important to pass a proper date value to Patch – either a Date/Time value or something that can be interpreted as one.

Patch Formula (Date Example): Suppose we want to patch the DueDate field of an existing item to December 25, 2025. We can do either of the following:

powerfx

// Using a literal Date value:
Patch(Tasks, LookUp(Tasks, ID=1),
      { DueDate: Date(2025, 12, 25) } );

// Using a Date Picker control value:
Patch(Tasks, LookUp(Tasks, ID=1),
      { DueDate: DatePicker1.SelectedDate } );

Both approaches will set the “DueDate” of item ID 1 in the Tasks list to 25-Dec-2025 [matthewdevaney.commatthewdevaney.com]. If the SharePoint column includes time, you could use a DateTime value (e.g. Now() for current date & time, or DateAdd(DatePicker1.SelectedDate, TimePicker1.SelectedTime) if time is taken separately).

Notes & Pitfalls:

Ensure the value patched is recognized as a Date/Time. If you’re taking input from a text box, convert it using DateValue or DateTimeValue with an appropriate format. For example, DateValue("12/25/2025") or using ISO format "2025-12-25" to avoid locale confusion.
If you omit the time for a DateTime column, SharePoint will default it to midnight UTC. Be mindful of time zone differences; using UTC or adjusting with TimeZoneOffset might be necessary for exact times, but for beginners it’s usually not an issue if using Date pickers (they provide Date values without time, which SharePoint stores relative to midnight).
Common error: Patching a date as text (without conversion) will result in a type mismatch error. Always provide a Date type. If you see a “network error” on Patch, check that your data types match the SharePoint column types.

Patching a Currency Column

SharePoint Column Setup: A Currency column in SharePoint is essentially a number field with currency formatting. It has a specific currency locale (e.g. USD, EUR) and decimal precision configured. Let’s say we have a “Budget” field of type Currency in a Projects list.

Patch Formula (Currency Example): Patching a currency is just like patching a Number – provide a numeric value:

powerfx

// If using a Text Input for amount, convert text to number
Patch(Projects, Defaults(Projects),
      { Budget: Value(txtAmount.Text) } );

In this example, a new Project record is created with its Budget set to the numeric value of txtAmount.Text. If you have the number already (say from a slider or calculation), you can use it directly (e.g. {Budget: 50000} for 50,000) [powerapps911.com]. SharePoint will format it with the currency symbol (configured in the column settings) once patched.

Notes & Pitfalls:

Data type: The value must be a number (decimal or integer). If you have a text input, wrap it with the Value() function as shown above [powerapps911.com]. Forgetting to do so is a common mistake – Patch will error if you try to send a text to a numeric column.
Currency format: You cannot change the currency type (e.g. from USD to EUR) via Patch; that’s a list setting. Patch only sets the numeric amount. The SharePoint list will automatically display it with the defined currency.
Large values: Currency in SharePoint has a fixed 4 decimal precision. Very large numbers are generally fine (up to around 10^13 in value). If you attempt an out-of-range value, SharePoint will reject it – but that’s rare.

Patching a Lookup Column

SharePoint Column Setup: A Lookup column links to an item in another list. For example, you might have an “Employee” lookup in a Tasks list that points to an Employees list (perhaps storing the Employee’s Name as the display). SharePoint stores lookup values as a combination of an item ID and a display text (the primary column of the target list, e.g. Title). In Power Apps, a lookup field is represented as a record with at least an Id field (the linked item’s ID) and a Value field (the display text) [rezadorrani.com].

Patch Formula (Lookup Example): To patch a lookup, you need to provide an object with the target item’s Id (and optionally its Value). For example, to set a Task’s Employee lookup to point to the Employee with ID 7:

powerfx

Patch(Tasks, Defaults(Tasks), 
      { Employee: { Id: 7, Value: "John Doe" } } );

This creates a new Task item with its Employee field referencing the item ID 7 from the Employees list [rezadorrani.com]. Alternatively, if you have the record of the employee (say from a dropdown or a lookup formula), you can provide that. A more dynamic approach is to use a dropdown bound to the lookup Choices and use its selection:

powerfx

// Assume drpEmployee is a Dropdown with Items = Choices(Tasks.Employee)
Patch(Tasks, Defaults(Tasks),
      { Employee: drpEmployee.Selected } );

By setting the Items property to Choices(Tasks.Employee), Power Apps knows to populate the dropdown with actual records of the related Employees. drpEmployee.Selected will already be a record with the proper Id/Value shape, so you don’t have to manually build it [powerapps911.compowerapps911.com].

Notes & Pitfalls:

Id is key: The Id must correspond to a valid item in the lookup’s source list. If you provide an incorrect Id, Patch will fail (and no new item will be created in the target; the lookup won’t accept arbitrary values).
Value field: Including the Value (the text) is recommended, though SharePoint primarily uses the Id. In many cases, you can actually patch just {Id:7} for the lookup and it will resolve, but providing Value (even an empty string) can avoid ambiguity [powerappsguide.com]. Power Apps often includes an @odata.type automatically as well (e.g. "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference" behind the scenes) [powerappsguide.com], but if you use the Choices() method, you don’t have to worry about that.
Clearing a lookup: If you need to blank out a lookup (and the column allows no value), one trick is to patch an Id of -1 and Value of empty string [powerappsguide.com]. This is a special SharePoint convention to clear lookup fields. For example: { Employee: { Id: -1, Value: "" } } would clear the Employee field.
Multiple lookups: If your lookup column is configured to allow multiple values, the Patch needs a table of lookup records. The structure is similar but wrapped in a Table(). For instance, to patch two lookup values: { MultiLookupField: Table( {Id:1, Value:"Item1"}, {Id:2, Value:"Item2"} ) } [rezadorrani.com]. Using a multi-select ComboBox with Items = Choices(List.MultiLookupField) and SelectedItems in the Patch is an easier way in that scenario (Power Apps will supply the table of selected records automatically).

Patching a Choice Column (Single-Select)

SharePoint Column Setup: A Choice column (single-select) provides a dropdown of fixed options (e.g. Status could be a choice of “Open, In Progress, Closed”). In Power Apps, a single-select choice field is treated similarly to a lookup behind the scenes – it has a Value field containing the selected option text [rezadorrani.com]. (The connector also treats it as a “reference” type, but you typically just provide the value.)

Patch Formula (Single Choice Example): To patch a single-choice field, provide a record with a Value. For example, if Status is a choice field:

powerfx

Patch(Issues, ThisItem, 
      { Status: { Value: "Closed" } } );

This will set the Status choice to “Closed” [rezadorrani.com]. If you are using a dropdown tied to the choice field, you can simplify it:

powerfx

// drpStatus is a dropdown with Items = Choices(Issues.Status)
Patch(Issues, ThisItem,
      { Status: drpStatus.Selected } );

In this case, drpStatus.Selected returns a record like { Value: "Closed" } for the chosen option, so it matches what Patch needs [powerapps911.com].

Notes & Pitfalls:

Value must match an option: Unlike lookups, SharePoint can accept values not in the predefined choices (it will still save the text) [powerapps911.com], but it’s not recommended unless the column allows fill-in choices. Stick to the defined options to avoid confusion. If you patch {Value: "NewOption"} that wasn’t in the list, the item might show that value but the choice field in list settings won’t automatically update to include it.
No need for Id: Choice options don’t have item IDs like lookups; just the text value suffices (with the record wrapper). If you attempt to patch just the plain text (e.g. {Status: "Closed"}), it will error – you need the {Value: "Closed"} wrapper unless using a bound control.
Case sensitivity: Choice matching is typically case-insensitive, but it’s best to use the exact casing as defined. “closed” might still patch to “Closed” but to avoid any unpredictable behavior use the exact string.
Clearing a choice: If the choice field is not required and you want to blank it out, you might try {Status: Blank()}. However, since it expects a record, you should supply {Status: { Value: "" }} (empty string) or use the bound control approach by selecting no value (if the control allows). An empty Value record will clear it, provided the field is not required.

Patching a Choice Column (Multi-Select)

SharePoint Column Setup: A multi-select Choice column lets users select multiple values from a set of options (SharePoint displays these as a list of selected values). In Power Apps, this field is represented as a table of records, each record having a Value for one choice [rezadorrani.com].

Patch Formula (Multi-Choice Example): Patching requires constructing a table of choice values. For example, suppose Tags is a multi-select choice column and we want to set it to “Red” and “Blue”:

powerfx

Patch(Issues, Defaults(Issues),
      { Tags: Table( {Value: "Red"}, {Value: "Blue"} ) } );

This will create a new issue with Tags containing those two choices [rezadorrani.compowerapps911.com]. If you have a ComboBox for this field, the formula is simpler:

powerfx

// cmbTags is a ComboBox with Items = Choices(Issues.Tags) and multiple selection enabled
Patch(Issues, Defaults(Issues),
      { Tags: cmbTags.SelectedItems } );

Here, cmbTags.SelectedItems already returns a table of {Value: "..."} records for the selected options [matthewdevaney.com]. This approach offloads the heavy lifting to the control.

Notes & Pitfalls:

Table structure: The Patch must receive a table of choice records. If you accidentally pass a single record or a single text value, you’ll get an error. Even if selecting one item, it should be in a table (e.g. Table({Value:"Red"})).
Empty selection: To clear a multi-choice field, provide an empty table. For example, {Tags: Table()} will remove all choices (ensure the column allows empty). Using cmbTags.SelectedItems when nothing is selected also yields an empty table to patch.
Limit on options: SharePoint multi-choice fields have an upper limit (usually 255 characters total or a certain count of options). This isn’t usually hit unless you have dozens of options selected. Just be aware if you ever try to patch an extremely large set of choices, SharePoint might reject it.
Common mistake: Forgetting to use Table() for multiple values. If you do { Tags: {Value:"Red"}, {Value:"Blue"} } by accident (missing the wrapping Table function), you’ll have a syntax error. Always wrap multiple records in a Table() or use a collection/SelectedItems.

Patching a Person (People Picker) Column – Single Person

SharePoint Column Setup: A Person or Group column in SharePoint references a user (from Azure AD or SharePoint site) or a SharePoint group. It’s essentially a special lookup to the hidden User Information list. In a canvas app, a Person field appears as a record with various fields like Claims, DisplayName, Email, etc. The Claims is a key identifier string that SharePoint uses to identify the user (it includes the user’s principal info). Typically, to patch a person field, you supply a record with at least the Claims (and often Email, DisplayName, etc., though SharePoint can fill some of those if Claims is provided) [powerapps911.com].

Patch Formula (Single Person Example): Imagine a “AssignedTo” field that is a single-person column. To patch it to a specific user, say “[email protected]”:

powerfx

Patch(Tasks, LookUp(Tasks, ID=1), 
      { AssignedTo: {
            Claims: "i:0#.f|membership|john.doe@contoso.com",
            DisplayName: "John Doe",
            Email: "john.doe@contoso.com",
            Department: "", JobTitle: "", Picture: ""
        } } );

This updates item ID 1’s AssignedTo to John Doe [rezadorrani.com]. The Claims string format "i:0#.f|membership|user@domain.com" is the typical claim for a user in SharePoint Online (using Azure AD authentication). In the above, we provided the user’s email in both Claims and Email, and a DisplayName. In fact, only the Claims field is technically required for SharePoint to resolve the user [powerapps911.com], but it’s a good practice to include Email and DisplayName for clarity. (SharePoint will match the claim to the actual user account and populate the name in the list interface regardless.)

Easier method: Manually building that record is error-prone. A simpler way is to use a People Picker control (ComboBox) tied to the Person field. For example, if you include the field in an Edit form, the data card value for AssignedTo will be a combobox (cmbAssignedTo). If you set up a combobox yourself, use Items = Office365Users.SearchUser() or Items = Choices(Tasks.AssignedTo) (Choices on a person field returns the list of site users). Then:

powerfx

Patch(Tasks, LookUp(Tasks, ID=1),
      { AssignedTo: cmbAssignedTo.Selected } );

If cmbAssignedTo is single-select (or a Dropdown bound to Choices(Tasks.AssignedTo)), Selected will be a user record with the correct structure (including Claims, etc.) [matthewdevaney.com]. This way, you avoid manually concatenating the claims.

Notes & Pitfalls:

Ensure the user exists: The email or claim must correspond to an existing user in the tenant or allowed visitors. If you try to patch a claim that SharePoint can’t resolve to a user, it will error. Using the Office365Users connector to find the user by email and then patching the result is a reliable pattern.
Claims format: For Azure AD accounts in SharePoint Online, the claim usually starts with "i:0#.f|membership|" followed by the UPN (email). If your tenant uses a different auth provider or you’re on-prem, the claims identity could differ (e.g. "i:0#.w|domain\\username" for Windows auth). This typically only matters if you manually build the string. Using User().Email as shown in our formula (with Concatenate in Reza’s example [rezadorrani.com]) or a selected user record avoids guessing the format.
Single vs multiple: A single-person field must receive a single record (not a table). Conversely, a multi-person field (see next section) expects a table of such records. If you accidentally pass a single record to a multi-person field or vice versa, Patch will throw an error about type mismatch.
Clearing the person field: To remove a person, you can patch Blank() or a blank record. Often easiest is to patch AssignedTo: Blank() if the field is not required. Alternatively, {AssignedTo: { Claims:"", DisplayName:"", Email:"" }} might also clear it (the exact method can vary; Blank() is simplest when allowed).

Patching a Person Column – Multiple Selections

SharePoint Column Setup: A Person/Group column can be configured to allow multiple people. In SharePoint, multiple persons are stored similarly to multi-choice: an array of people entities. In Power Apps, you will handle it as a table of user records (each record with Claims, Email, etc., like the single-person case) [powerappsguide.compowerappsguide.com].

Patch Formula (Multi-Person Example): Let’s say “TeamMembers” is a multi-select person column in a Projects list. To patch two users (e.g. John and Jane) into it:

powerfx

Patch(Projects, Defaults(Projects),
      { TeamMembers: Table(
            {
               Claims: "i:0#.f|membership|john.doe@contoso.com",
               DisplayName: "John Doe", Email: "john.doe@contoso.com",
               Department: "", JobTitle: "", Picture: ""
            },
            {
               Claims: "i:0#.f|membership|jane.smith@contoso.com",
               DisplayName: "Jane Smith", Email: "jane.smith@contoso.com",
               Department: "", JobTitle: "", Picture: ""
            }
      ) } );

This creates a new Project item with John and Jane in the TeamMembers field [matthewdevaney.commatthewdevaney.com]. As with single, only the Claims are strictly required for each, but we included names and emails for completeness.

Easier method: Use a ComboBox that allows multiple selection. For example, set cmbTeam.Items = Choices(Projects.TeamMembers) (this will list possible users – it might list all users or a subset like users of the site). Ensure cmbTeam.AllowMultipleSelection = true. Then simply:

powerfx

Patch(Projects, Defaults(Projects),
      { TeamMembers: cmbTeam.SelectedItems } );

If John and Jane are selected in the combo box, cmbTeam.SelectedItems will be a table of user records (each with Claims, DisplayName, etc.) that Patch can directly use [matthewdevaney.com]. This is much simpler and less error-prone.

Notes & Pitfalls:

Table of records: Just like multi-choice, you must send a table. A single record or a single value won’t work. Conversely, for a single-person field, don’t send a table.
Ordering and duplicates: The order of people in the table typically doesn’t matter; SharePoint will store them in some default order (often alphabetically by name). Avoid duplicate entries in the table – if the same person appears twice, SharePoint might either deduplicate or throw an error.
Adding/removing individuals: If you want to append or remove a person without disturbing others, you’ll need to retrieve the current value (e.g. use Collect or manipulate the SelectedItems of a combo). Patch on its own replaces the entire field value. For example, to add a person, you could do: {TeamMembers: Table( <Existing team members...>, {Claims:"…newperson…", ...} ) }. But typically, you would manage this through the UI by using a combo box selection.
User not found error: If one of the entries isn’t recognized as a valid user, the whole Patch fails. Double-check spelling of emails or ensure the user has access to the site (they might need to be added in Azure AD or as a guest).

Patching a Hyperlink (URL) Column

SharePoint Column Setup: A Hyperlink column in SharePoint consists of two parts: URL and Description. In list views, SharePoint either displays the description text as a clickable link or (if the column is set to “Picture” format) displays the URL’s target image. When connecting to Power Apps, a hyperlink field is typically treated as a string (especially in newer connectors). In older implementations, one could patch a record like {URL: "...", Description: "..."}. However, currently the recommended approach is to patch just the URL as text [powerapps911.compowerapps911.com].

Patch Formula (Hyperlink Example): If Website is a Hyperlink field (with an optional description) and you want to set it to https://contoso.com with description “Contoso Site”, you might expect to do {URL:"https://contoso.com", Description:"Contoso Site"}. But the SharePoint connector will actually take a simple text for the URL:

powerfx

Patch(Projects, Defaults(Projects),
      { Website: "https://contoso.com" } );

This will set the hyperlink value to “https://contoso.com”. The description part cannot be set via Patch – by default, SharePoint will display the URL as the clickable text, or if the column was previously set with a description, that description may remain unchanged if you only patch the URL. Essentially, Power Apps only writes to the URL component with Patch [powerapps911.com].

If you want to allow user input for both URL and Description, one workaround is to combine them into a single string like "URL, Description" and patch that. However, the easier method is to use an Edit form for that field or a Power Automate flow. For simplicity, assume we only care about the URL in this example.

Notes & Pitfalls:

Description is read-only in Patch: As mentioned, you cannot directly set the Description through Patch (there is no supported structure in the current connector to do so). So whatever string you patch is taken as the URL. The Description will either remain what it was or mirror the URL. In many cases, SharePoint will set the Description equal to the URL if none is provided. So don’t be surprised if the link text is the URL after patching.
Picture format limitation: If the hyperlink column is configured to show as an image (picture), patching via Power Apps is not supported [powerapps911.com]. You would need to switch the column to hyperlink format (or use a different approach like an attachment or image column). In picture mode, SharePoint expects an image URL and possibly handles it differently, which the Patch function can’t fulfill. The PowerApps911 reference states that you cannot patch a hyperlink column when it’s set to picture mode [powerapps911.com]. The workaround is to change the column display to standard hyperlink if you need to Patch it.
Validation: Ensure the string is a valid URL (starts with http:// or https:// or other supported scheme). SharePoint might reject obviously malformed URLs. If the user omits the scheme (e.g. types “www.contoso.com”), you might need to prepend “https://” in your patch formula.
Alternate approach for description: If a description is crucial, one approach is to concatenate URL and description in a single string separated by comma in a Single Line Text field and then use SharePoint column formatting or a flow to split them. But that’s beyond this scope – generally, consider using the hyperlink column primarily for URL storage when patching from Power Apps.

Patching a Yes/No (Boolean) Column

SharePoint Column Setup: A Yes/No column is a boolean value (stored as true/false in SharePoint, displayed as Yes/No). In Power Apps, it appears as a boolean (true/false) value. Patching it is straightforward with a boolean literal or a control that provides boolean.

Patch Formula (Yes/No Example): For a field “Approved” (Yes/No) that we want to set to Yes (true):

powerfx

Patch(Requests, ThisItem, { Approved: true } );

This marks the Approved column as “Yes” [powerapps911.com]. Conversely, use false for “No”. If using a toggle or checkbox control, you can patch its .Value:

powerfx

Patch(Requests, ThisItem, { Approved: chkApproved.Value } );

Here, chkApproved.Value is either true or false depending on the user’s selection (for a Check box, Value is true when checked; for a Toggle, use Toggle1.Value) [powerapps911.com].

Notes & Pitfalls:

Boolean type: Use actual boolean values (true/false). You might see some sources using 1 or 0 to represent true/false [rezadorrani.com]. In fact, Patch will accept 1 as true (Yes) and false as No in some cases [rezadorrani.com], but for clarity stick to explicit booleans. Do not use the strings "Yes"/"No" – that will cause a type mismatch.
Default values: If the SharePoint column has a default (Yes or No), and you are creating a new item with Patch without specifying that field, SharePoint will apply its default (unlike some fields where a missing value is just blank, booleans will default to either yes or no per the list setting).
Nullability: SharePoint Yes/No fields cannot be null – they are always either Yes or No. In Power Apps, if you treat it as a required field, make sure to provide true or false. If you use a checkbox that is not defaulted, its Value might start as false (No). Just be mindful that leaving it “unset” in UI still results in a value (false if unchecked).
Common pitfall: If you accidentally patch a Yes/No field with a text value like "true"/"false" (as strings) or "Yes", it will error. Ensure no quotes around true/false in your formula.

Patching a Managed Metadata (Tag) Column (Single Value)

SharePoint Column Setup: Managed Metadata columns connect to the Term Store, allowing you to tag items with taxonomy terms. These are one of the more complex field types to patch because the record requires multiple pieces of information about the term. A single-value Managed Metadata field in Power Apps is represented as a record with fields including TermGuid, Label, WssId, Path, and Value [rezadorrani.com]. Essentially, you must tell SharePoint which term to apply by its GUID and provide the text label.

For example, suppose we have a “Department” Managed Metadata column that points to a term set of departments. We want to patch the term “Finance” which has a known Term GUID.

Patch Formula (Managed Metadata Example):

powerfx

Patch(Docs, Defaults(Docs),
      { Department: {
           '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy",
           TermGuid: "b8c3899a-5d9e-4f7c-8ced-8c2f761b9e75",
           Label: "Finance",
           WssId: -1,
           Path: "Finance",
           Value: "Finance"
      } } );

This will create a new item in Docs with the Department metadata set to the term Finance identified by that GUID [community.powerplatform.comcommunity.powerplatform.com]. Let’s break down the fields:

TermGuid: The unique identifier of the term in the term store (you often have to get this from SharePoint’s term store or via a query).
Label: The term’s name as you want it displayed.
Path: The full hierarchical path of the term (for nested terms). In this case “Finance” has no parent so Path could be just "Finance". Often this can be an empty string if not needed.
WssId: An internal ID used by SharePoint for caching terms in the site collection; setting it to -1 or 0 indicates “not cached yet, resolve by TermGuid” [powerappsguide.com]. Common practice is to use -1 or 0 when patching; SharePoint will assign the correct WssId.
Value: Historically used similarly to Label (sometimes redundant). Including it as blank or same as Label is fine. The key ones are TermGuid and Label (and providing all expected fields to avoid errors) [powerapps911.com].

The @odata.type is also provided to tell the connector which data type this is (taxonomy type). If you use the Choices function or connect the field to a control, this might be handled for you.

Easier method: If your app can get the term information through a control, that helps. Unfortunately, Power Apps does not have a built-in Choices() for taxonomy that returns all terms (unless the terms are used in that list already). One approach is to use a ComboBox and a custom source (for example, a SharePoint Online connector call or a predefined list of terms, or use the DataCard from an edit form). If you have the term GUID and label available (perhaps via a dropdown of term names tied to some data source), you can construct the record as above. There isn’t a straightforward out-of-the-box combo bound to Choices(List.Column) for taxonomy, because it doesn’t enumerate terms. Often, people use a separate hidden list or manual entry of terms to drive selection.

However, if you do use the default SharePoint form integration or a custom form with the taxonomy control, you can patch using that control’s selected item. For example, if cmbDept is a combo box inside a form data card for Department:

powerfx

Patch(Docs, Defaults(Docs),
      { Department: cmbDept.Selected } );

In a SharePoint-integrated app, cmbDept.Selected might already be a record with the needed term fields (TermGuid, Label, etc.) as provided by the form’s data source. Always verify what structure Selected has by testing or looking at its properties.

Notes & Pitfalls:

All fields required: When manually patching, provide all the fields (TermGuid, Label, etc.) for the term [community.powerplatform.comcommunity.powerplatform.com]. If any is missing, Patch will likely fail. For example, a known pitfall is to omit WssId – this can cause a network error. Use WssId: -1 (or 0 as some use) if you don’t have one. It signals SharePoint to resolve the term.
Getting TermGuid: You usually need to fetch the GUID of the term. This might involve using the SharePoint REST API or a manual lookup. For a beginner, if you’re just testing, you can get the GUID by looking at term store settings or by temporarily using a SharePoint form to set a value and then reading it in Power Apps. Community blogs often show using a hidden data card to grab DataCardValue.Selected.TermGuid [community.powerplatform.com] – which is exactly how one would do it inside an edit form scenario.
Multi-language labels: If your term store has multiple language labels, the Label you patch should match one of the labels. Typically it’s the default language label.
Single vs multi: For a single-value metadata column, supply a record. Let’s discuss multiple next.

Patching a Managed Metadata Column (Multi-Select)

SharePoint Column Setup: A Managed Metadata column can allow multiple tags to be assigned. This behaves like a multi-select choice in concept, but with terms. You will need to patch a table of term records in this case [powerapps911.compowerapps911.com].

Patch Formula (Multi-Taxonomy Example): Suppose “Categories” is a multi-select Managed Metadata field, and we want to tag an item with two terms: Finance (GUID aaaa...) and Healthcare (GUID bbbb...):

powerfx

Patch(Docs, LookUp(Docs, ID=1),
      { Categories: Table(
            {
               '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy",
               TermGuid: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa",
               Label: "Finance",
               WssId: -1, Path: "Finance", Value: "Finance"
            },
            {
               '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy",
               TermGuid: "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb",
               Label: "Healthcare",
               WssId: -1, Path: "Healthcare", Value: "Healthcare"
            }
      ) } );

This updates item ID 1’s Categories field to contain the Finance and Healthcare tags [powerapps911.compowerapps911.com]. As you can see, it’s verbose – each term needs the full record as in the single case.

Easier method: Similar to multi-choice or multi-person, using a ComboBox that allows multiple selection can simplify things if you have a way to feed the terms into it. If you set cmbCategories.Items to a list of term labels or a custom data source of terms (e.g. from a SharePoint list that mirrors the term set), you could then collect the GUID and label on selection. But out of the box, Power Apps doesn’t directly pull term store data without a connector or custom approach. Another approach is to use the SharePointIntegration in customized forms (when customizing a SharePoint list form with Power Apps, the taxonomy control is provided). In that case, the combobox inside the data card will manage the terms. You could patch using cmbCategories.SelectedItems which would be a table of term records already formatted. This is advanced, though.

For beginners, if you need multi-terms, consider patching one term at a time (e.g. if user selects multiple, you can build the table as above in your formula). Or use an edit form which handles it automatically.

Notes & Pitfalls:

Performance: Building a big table of terms with GUIDs in the formula can be cumbersome. If the set of terms is large, consider an alternative like using Power Automate to handle taxonomy updates or limiting the number of selectable terms in the app.
Term GUID accuracy: Double-check each GUID. If one is wrong or doesn’t exist in that term set, the whole patch will fail. If a term has a duplicate label in a different part of the hierarchy, the GUID ensures the correct one is picked.
Mixing with existing tags: If you want to add a tag while keeping existing ones, you would need to retrieve the current value (which comes as a table of records) and then Collect the new one into it, then patch the combined table. If you just patch a new table, it overwrites the old tags.
Clearing tags: To remove all tags, patch an empty table: {Categories: Table()}. To remove one out of many, patch the new list of terms excluding that term’s record.
Not supported in some contexts: In older PowerApps versions, taxonomy fields were not delegable or readily accessible via Choices(). This might still be the case, so testing is important. Some makers use a hidden data card in an Edit form to leverage its combo box for term selection and then patch using that value, as a workaround for the lack of direct term querying.

Conclusion

The Power Apps Patch function is an essential tool for any app maker who wants to go beyond the limitations of standard forms. As we’ve seen, with SharePoint as a data source, Patch can handle everything from simple text and number updates to complex field types like lookups, people pickers, and managed metadata. Mastering the syntax for each column type unlocks the ability to create highly dynamic apps – for example, apps that update several lists at once, or save data in custom ways with a single button click. By using Patch, you can provide a smoother user experience (no page reloads, more control over what happens on save) and implement business logic (conditional updates, default values, etc.) in your canvas apps.

SharePoint is a great starting point because it’s readily available and easy to use. We also noted that Dataverse is the more powerful, scalable option as apps grow – but it requires premium licensing [powerapps911.com]. As your app scales up or your organization’s needs get more complex (relationships, security roles, large data volume), you might explore moving to Dataverse or SQL. The good news is that the skills you learned here with Patch and Power Fx will carry over. In Dataverse, for example, you also use Patch (though many complex field types are handled more uniformly in Dataverse).

For now, with SharePoint lists and the Patch function, you can build robust custom solutions within the standard licensing. Practice patching different field types in a test app – perhaps create a sample list with columns of each type and try the examples above. Always remember to match the data types, use the Choices() function and proper controls to simplify complex patches, and handle any errors (use IfError or check the return value of Patch) to make your app user-friendly.

Happy patching, and enjoy the flexibility it brings to your Power Apps development! If you found this guide helpful, you’re well on your way to building apps that save data exactly how and when you want. Keep experimenting with Patch, and soon it’ll become second nature.

References

Microsoft Learn – Patch function documentation: Official reference for Patch syntax and usage in Power Fxlearn.microsoft.comlearn.microsoft.com.
Power Apps & SharePoint Complex Fields – Reza Dorrani’s Blog: “Power Apps Patch function with SharePoint” – outlines Patch syntax for SharePoint columns like Choice, People, Lookup, etc., including required record structuresrezadorrani.comrezadorrani.com.
PowerApps911 – Patching Complex SharePoint Columns: Shane Young’s tutorial on patching choice, multi-choice, people, multi-people, lookup, and managed metadata fields (both with static values and using controls)powerapps911.compowerapps911.com. Great best practices on using Dropdowns/ComboBoxes with Choices() to simplify Patch.
Power Apps Community Blog – Managed Metadata Patch: “Saving to SharePoint Managed Metadata columns using Patch” by Darogael – shows how to construct the record for taxonomy fields with TermGuid, Label, etc.community.powerplatform.comcommunity.powerplatform.com.
Power Apps Guide by Tim Leung – Patching complex data types: Summarizes the Patch syntax for the 6 most complex SharePoint field types (lookup, single choice, multi-choice, yes/no, person, multi-person) with examplespowerappsguide.compowerappsguide.com. A handy reference for understanding the OData type requirements and structures.
Matthew Devaney – Patch Function Examples: A collection of examples for every SharePoint column type. Useful for seeing real examples with screenshots of before/after in the SharePoint listmatthewdevaney.commatthewdevaney.com. Matthew’s site also provides a PDF guide which is great for offline reference.
PowerApps Official Forum Q&A: Various community answers and threads (on Power Users and Stack Exchange) discussing common Patch errors (e.g., “Network error when using Patch” which often means invalid data or missing required fields). These can be useful if you run into specific issues not covered above.
Dataverse vs SharePoint for Power Apps – Overview: Blog post “Getting Started with Dataverse: A SharePoint User's Guide” on PowerApps911 highlights key differences (e.g., Dataverse requires premium licenses, whereas SharePoint is included in M365)powerapps911.com. Good to read when you’re considering scaling up from SharePoint in the future.

I have seen the future in IT.

2018-03-16T09:29:00.001+02:00

Due to some anomaly with the space-time continuum, I was sent to the future! I saw some key upcoming disruptions and was sent back to warn you all, so we can prepare for these disruptions now, before its too late. Oh, while I was there, I accidentally killed someone called Sarah Conner, hope that does not bite us in the @$$ later!

Everything that we find normal is GONE, and we better adapt or die! The Microsoft Tech Summit I recently attended confirmed a few big changes in the not-so-far future that we better prepare for NOW.

1. The Intranet is no more.

Do you still use your intranet? No, like really use it? The intranet was a platform for collaboration, but these days, people use it to access their leave form, expense claim form, maybe the corporate telephone directory, see pics from the corporate function or to see the canteens menu and, well, that's about it. We don't use it for collaborating, people are now collaborating via tools like MS Teams.

What makes tools like MS Teams good, is well, its just like SharePoint (YES! that's a good thing!). what I mean to say is that it is also a development platform. So, while its a great starting point for forming communication channels within teams, you can also add some customisation and do just about anything. You can develop some bots to automate you leave application and expense claim on a chat-based platform that feels more natural to your users. It has great integration support with a large host of applications, it even has connectors that allows applications to send notifications directly to your channel with very useful, customisable card style views.

MS Teams now gives you whatever you wanted in the intranet in an interface that is intuitive and natural.

The Intranet had a good run but we have now outgrown it. It will take a while for Corporate Communication to realize this, so this change will start off slow, but once the penny drops, Intranets will be like Netscape.

2. Azure Functions, the missing link to everything!

The SharePoint Framework is Microsoft s way of allowing us to customise SharePoint, its great and its getting better everyday. But its a client side solution and there are things that we just wont be able to be via the framework alone.

But wait, the framework can now call Azure Functions, and this baby is not as restrictive as the SharePoint Framework. Azure Functions are serverless. "functions as a service" solutions that can work in the language of your choice, you can run it via triggers or webhooks and its a great solution for complex tasks like automation, enforcing governance, site provisioning or integration that just would have been impossible, or at least, very difficult otherwise.

So Azure Functions will soon be known as the missing link that everyone didn't know, they always wanted.

3. Skynet is becoming self-aware

AI is getting good, really good, scary good! Its now easy to use and its a great value add that worth considering.

While AI alone isn't that impressive. IoT is betting big, this means that data collection of things that was impossible to collect data on before and now coming in fast. AI is at its best when it has lots of data to work with, and Big-data is becoming even bigger-data!

This type of data leads to advanced analytics and is a great component of block-chain applications. In fact, the environment is now perfect for decentralised applications to flourish and it wont take long for us to apply block-chain tech to applications that are now dependent on centralised organisations or teams like banks, lawyers, HR, accountants, government, etc. It could be used to enforce contracts, pay salaries, calculate BEE status, manage benefits or training. Its provides a trusted transactions that needs no central control.

We will soon create token systems for things of value like diversity in company, water consumption, carbon footprint. Block-chain tech, together with data collected from IoT, will allocate tokens when warranted which can be used to confirm certain characteristics without the need of central organisations to confirm.

In the not-too-far future, all information will be centralised and contextualised and personalised and available in an intuitive and natural way. We dont look for stuff anymore, we just get the right stuff, when we need it most.

Money will lose its value as we find trad-able "value" in other things. And people will lose jobs once Blockchain's "smart contracts" take over as the preferred way to handle tasks by being transparent and reliable.

So the world is changing, if you are still doing things the way it was done 3 years ago, then you are doing things the old way and if you don't adapt now, it might be too late.

Get into ChatBots NOW, its what your customers want, but don't know it yet

2017-09-23T11:15:00.000+02:00

OK, lets cover the basics. What are ChatBots? Its a computer program designed to simulate conversation with human users, typically via messenger apps or chat windows. I’m sure most of you already got that, but I wanted to make sure that we all on the same page here.

So why should we focus on ChatBots? No one is asking for them, right? Wrong. Companies are looking for ways to engage with their customers in a way that their customers are already familiar with. Customers don’t want to download an extra app for the companies they engage with so in order to engage with your customers in a manner that is convenient to them, you need to use a medium that they are already familiar with.

You may have noticed that chat tools are becoming our clients more natural and preferred way to communicate. In fact, towards the end of 2016, Facebook announced that they host over 30 000 ChatBots. These bots can check the weather, order food, organise travel and even play games. China uses WeChat and they have over 10 million ChatBot accounts and lets face it, we are getting familiar with ChatBots thanks to Siri, Google Now and Cortana.

So companies are starting to see ChatBots as the perfect medium to engage with their clients, they are also figuring out that ChatBots are well suited for routine customer queries like:

Self Service
FAQ’s
Onboarding
Completing Forms
Workflow Approval

So ChatBots should be considered when:

Customer requests are in a specific area and the solution is well known and predictable
Guided steps and input parameters
High volume requests

Just try to avoid using ChatBots in situations where your customers are already stressed.

So you get it right? There's a need for ChatBots, and once your customers figure out that ChatBots are an option, there will be a huge demand for ChatBot development. So where do you, as a developer, get started with ChatBot development?

Thankfully, Microsoft has made ChatBot development easy via:

Microsoft Azure – Bot Services
Cognitive Services
Louise (Microsoft’s language, understanding and intelligence service)

Here are a few tools that can help you:

Microsoft Bot Developer Portal (dev.botframework.com)
Bot Builder SDK (github.com/Microsoft/BotBuilder)
Bot Emulator (download.botframework.com/botconnector/emulator)
Visual Studio Bot Project Template (aka.ms/bf-bc-vstemplate)

Before you begin, you should know that the following platforms are supported:

Bing (the bot will appear in search results)
Cortana (can be deployed as self service to organisation)
Skype and Skype for Business
Web
Email
Facebook
GroupMe
Kik
Slack
Telegram
Twilio
Microsoft Teams bots support 1:1 chats (not group chats yet)

You will notice that I didn't mention WhatsApp. Yes, WhatsApp does NOT allow ChatBots to use that platform. Infact, WhatsApp are actively blocking accounts once its discovered to be a ChatBot.

This is unfortunate because its the biggest and most preferred chatting tool in South Africa, and I am sure in many other parts of the world, and I dont see this changing anytime soon since FaceBook owns WhatsApp, and they are pushing for Messenger (another chat tool owned by FaceBook) to be the preferred chat tool.

If you want to build a ChatBot solution in 10 minutes with no code. Check out QnA maker, https://qnamaker.ai/, a service provided by Microsoft that converts a FAQ page to a ChatBot service within minutes.

SharePoint Lists Views + JS Links + Data Tables = Awesome Threesome

2017-09-17T15:37:00.001+02:00

SharePoint Lists Views

SharePoint lists are the primary building blocks when setting up a SharePoint site. Pretty much any system is designed to takes and give information and SharePoint relies on lists to make that happen.

So list are important, it stores the information you want to share, it sources workflows and forms and it defines how things relate to each other. We rely on List Views to interact with these lists which is great, but these views are created by SharePoint and gives us limited control in how we can configure the interface, which is a shame because sometimes the lists are just too important to rely of the basic configurations.
Thankfully, Microsoft gave us JS links, an ability to influence the way the views are rendered via JavaScript.

JS Links

This opens a great new world of possibilities, we can now use JavaScript to read the data from the list view, check if certain conditions are met, and if these conditions are met, we can change the information that needs to be displayed.
For example, if you have a SharePoint list with a column that stores a RAG indicator, i.e. Red, Amber or Green indicator, you can, via JS Links, check the indicator value and display an image with the appropriate colour instead of the boring text.

And that’s the big advantage of JS Links, by being able to change the design of the list view, you can provide a much better user experience. Better user experience leads to better adoption, less room for errors and ultimately, greater success.

When it comes to JavaScript, there’s awesome open source libraries available (you can find them here: https://www.javascripting.com/). You may know the more popular ones like jQuery, React.js and Angular.js. I want to draw your attention to a library called Data Tables, a highly customisable tool that adds advanced interaction controls to HTML tables.

Data Tables

Data Tables, https://www.datatables.net/, are awesome, with a single line of code, you point this JavaScript library to a HTML table and it auto-magically provides search capabilities, sorting, styling and paging, it also stores everything in memory so the paging, searching and sorting works very fast, even with large dataset.
So if you are looking for alternatives to the standard List Views, then the Data Tables JavaScript library is a great option. The views provided by SharePoint are still tops and should be used most of the time, but there are scenarios where I would see Data Tables as the better alternative, these scenarios are:

For consuming, not necessary creating information – the greater range of styling options available via Data Tables makes this tool a great option for presenting information
Enhanced User Experience – getting into the code of what is displayed gives you better control when defining the interface

Working with large amount of data (but not too large) – with the data stored in memory, less database calls are needed when paging, searching or sorting making it a great choice for large, but not massive lists will take a long time to load into memory so there is a threshold to consider. I am assuming that 500 items is a good number for Data Tables, but will set the scene for some threshold testing.

This alternative, where you use Data Tables to display information from a SharePoint List View via JS Links is something that I and now calling the “Awesome Threesome”. (Apologies if this is not the type of Awesome Threesome you were Googling!)

So how do we get this Awesome Threesome?

1. Setting up the SharePoint List

First, let’s setup a SharePoint list. I want to see how Data Tables work with large lists, so I Googled and found an Excel file (https://community.tableau.com/servlet/JiveServlet/downloadBody/1236-102-1-1149/Sample%20-%20Superstore%20Sales%20(Excel).xls) with 21 columns and 8 400 rows of data, definitely overkill, since best practice dictates that SharePoint lists should not exceed 5 000 rows, but this will help us determine what threshold is acceptable and I want to see how Excel and SharePoint will handle this volume of data.

To export this data to SharePoint, format the data as a table, select a cell in the table, select Design (if you don’t see the Design option, then you did not format the data as a Table), Select Export and then Export Table to SharePoint List (Confused? See image below).

Specify the SharePoint URL and List details and “Ta Daa!” the list exported to SharePoint successfully. (For this article, I used SharePoint Online via my O365 subscription and I defined a new list called “DataTableDemo”).

For those interested, uploading 8 400 rows to SharePoint Online took 12 minutes to complete, that’s pretty good.
Excel provided a nice progress bar for the first 75%, then it looked like it crashed with a “Not Responding” status. I ignored that message and checked the SharePoint site. I could see the new library and I could see the number of items increasing every time I refreshed the page, so it was still working. It finally stopped at 8 399 rows (I counted the header row which makes 8 400) and Excel got out of the “Not Responding” Status and in its place showed a wonderful “Export Successful” message with a link to the new list.

Given the volume of data I was exporting and where I was exporting it to, the way Excel handled it was impressive. Well done Microsoft!

Its good practice to create a new view rather than modifying the default “All Items” view. So I created a new, standard view called DataTableView with all the defaults selected. I will be working with this view going forward.

2. Setting up the JS Link

Now we need to configure the list to reference our JavaScript Code via JS Link
Firstly, SharePoint needs to be configured to accept Custom Script. To do so, navigate to the SharePoint Admin Centre and set the "Custom Script" option to "Allow".
Also, Modern views do not support JS Links, so you need to change the list settings from Modern Experience to Classic Experience. To do so, navigate to your list, then go to List Settings > Advanced Settings and select Classic Experience in the List and Libraries Experience section.
The system needs around 24 hours to fully apply all these changes. So log off and come back tomorrow to see if everything is working. This is important, I spent whole night trying to figure out why some options were not available, only to discover that I needed to wait it out.
You will know that all is configured correctly when you can navigate to your List view, edit the page, edit the web part that is displaying the list, and see the JS Link textbox under the Miscellaneous section.

Since we are here, lets add a reference to the JS file that we will use. Full paths will not work (SharePoint trims it to a relative path and does a poor job at that), so you need to reference a file in the site collection with the following syntax “~sitecollection/[Library]/[filename].js”
Now, open your site via SharePoint Designer and navigate to the Site Assets folder, create a blank file with a .JS extension.
I named my file DataTableJS.js. (so in the JS Link text Box, I entered ~sitecollection/SiteAssets/DataTableJS.js).
Muawiyah Shannak provides a great set of JS Link samples: The articles and code can be found here, https://code.msdn.microsoft.com/office/Client-side-rendering-JS-2ed3538a, and It’s an excellent resource that provides JS Link sample code from basic tasks to pretty sweet advanced stuff.
I will be focusing on the code that he used to transform a SharePoint list View into an Accordion style view (Sample 6). But instead of transforming the list into an Accordion, I’m going to transform it into a Data Table. Its pretty much the same concept, and I see Muawiyah’s code helping me the same way I help my wife: He will get me 95% of the way there, with the remaining 5% I have to do on my own. (Yes, I help my wife code ;).

3. Setting up the Data Tables

Muawiyah Shannak samples are clear and complete and I encourage you to go over there first to better understand how code via JS Link works. I won’t cover that, but I will talk about the adjustments I made to his Accordion view code (https://code.msdn.microsoft.com/office/Client-side-rendering-code-ccdb2a0e).
First, here are a few things you need to do before you start writing code.
1. Know that web browsers cache .js files. So when you are editing your file and you want to see the results in the browser, refreshing the browser is not good enough. You must always do a “Clear Cache” refresh by pressing Ctrl+Shift+F5 or Ctrl+Shift+R.
2. The columns that you want to display MUST be selected for display in the View Settings section. If it’s not in the View, it’s not available for the Data Table to use and you will be presented with an “Undefined” error. I have selected a few columns of different types so we can see how they are handled.

3. In extension to point 2 above, de-select all the columns you will not use in the Data Table, otherwise you are giving SharePoint unnecessary work and if you don’t treat SharePoint well, he won’t treat you well.
4. The View must have an Item Limit that is high enough to send ALL the rows you want to display in the Data Table, not just the rows you want to display on the first page. The DataTable will handle the paging for you, but it needs all the data up front to store that into memory. I set the Item Limit for my DataTableView to 500 for now and will check acceptable thresholds at a later stage, most likely in a later blog post.

5. Know that Data Table library will only work on valid, well formatted HTML tables. These tables need a header (thead) and a single body (tbody), an optional footer (tfoot) can also be used. Here’s the outline that we need to build:

<table id="table_id" class="display">
<thead>
   <tr>
    <th>Column 1</th>
    <th>Column 2</th>
   </tr>
</thead>
<tbody>
   <tr>
    <td>Row 1 Data 1</td>
    <td>Row 1 Data 2</td>
   </tr>
   <tr>
    <td>Row 2 Data 1</td>
    <td>Row 2 Data 2</td>
   </tr>
</tbody>
</table>

So, with all the prep work done, let’s look at the code.

1. This code links the Data Table JS library to the correct HTML table. The table been referenced has an id of “DataTableMain” (I defined this id in section 3 below).
This is the standard code that you use when using Data Tables (as explained here: https://datatables.net/examples/basic_init/zero_configuration.html).
I kept this area separate because DataTables are highly configurable and if these examples: https://datatables.net/examples/index, inspires you take your DataTable to the next level, this is where you will do it.
2. This is the full line (cut off from the screenshot above):
“
(window.jQuery || document.write('<script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script><script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"><script type="text/javascript">'+dataTableBinding+'</script>'));
“
This code references jQuery (a pre-requisite for the Data Table library), the Data Tables library, the Data Table Stylesheet and the code created in point 1 above.
Note that SharePoint Online will block all http references, so if you are using a CDN reference, make sure it’s a secured https reference.
3. This is the full line (cut off from the screenshot above):
“
dataTableContext.Templates.Header = "<table id=\"DataTableMain\" class=\"display\"><thead><tr><th>Order Priority</th><th>Order Quantity</th><th>Order Date</th><th>Customer Name</th><th>Product Name</th><th>Modified By</th><th>Action</th></tr></thead><tbody>";
”
Here we are replacing the header and footer components with our own code.
The code that we are applying is building the main shell for a valid, well-formed HTML table with an id of “DataTableMain” (so we can reference it to the Data Table library, as done in point 1 above) and a class of “display” (so we can use the default stylesheet available)
4. This is where we populate the HTML table with the data available via the SharePoint View.
This code is in a function that runs for each item in the View (so in this case, it will run 500 times).
SharePoint may see columns names differently to how we see it, and in code, we must reference the columns the way SharePoint sees it.
To see how SharePoint has named these columns “for internal use”, go to the List Settings section, select a column, as if you want to edit it, look for the “Field” parameter in the URL QueryString, and you will see the column name the way SharePoint recognises it.

So in the screenshot above, the Order Priority column is recognised as “Title” within SharePoint, so that's what I looked for when I populated the dataOrderPriority variable. Do this check for all your columns, you may be surprised on how SharePoint referenced them.
For the Order Date column (a date column), I provided code that shows you how to change the format of the date. The format that I used is probably the best one to use if you want to use the sorting feature for that column.
I included the Modified By column, so I could provide code on how to handle a People column. I extracted the name and email address so I can create a mailto link in section 5 below.

5. This is the full line (cut off from the screenshot above):
“
return "<tr><td>"+dataOrderPriority +"</td><td>"+dataOrderQty +"</td><td>"+dataOrderDate +"</td><td>"+dataCustomerName +"</td><td>"+dataProductName +"</td><td><a href='mailto:"+dataModifiedByEmail+"'>"+dataModifiedByName +"</a></td><td><a href='EditForm.aspx?id="+dataID +"'><image src='/SiteAssets/edit.png' alt='Edit'></a></td></tr>";
”
The code exits the function with a table row that is appended to the HTML table that we are building.
In the code, you will see that I am adding link that will allow the user to edit the list item. I added this because the standard way to edit a list item, i.e. via the Ribbon Bar, stopped working when we replaced the header and item portion of the display template with our own code.
SharePoint no longer knows when an item is selected, so it won’t enable the edit button in the ribbon. I’m sure we can write code via JavaScript that will “educate” SharePoint on what is selected, but I haven’t figured out how SharePoint is doing this, and since the edit button that I am providing is an acceptable workaround (it even has a cool icon image that I stored in the Site Asset library), I lost the motivation to study the default code. But if anyone knows how to do this, please mention in the comments.
And that’s it, the animation below shows the result.

I think that this is a pretty powerful alternative that gives me great control on what I want my end users to see and how I want them to see it. The interaction with the Data Table with 500 items is very fast (so I can see that it can handle a lot more)
With only 40 odd lines of code, I believe that this option is a true winner that I am sure that you will see and start using this as well.

Transposing a single column of data in Excel with a set number of columns

2017-09-08T16:39:00.000+02:00

I was faced with an interesting challenge, I was tasked to provide a list of all the sites available via our O365 subscription, but I was not given sufficient access rights to navigate to the administration portal to pull this list.

In fact, this is the only view I had available to me:

Selecting, copying and pasting the content into Excel seems like the obvious next step, but alas, I was faced with one column of data with all the information that appeared in my view, i.e. the site title, the news title, the news synopsis the author and the modified date. All I want was the site title.

After cleaning up the Excel a bit, I noticed that the information that I wanted appeared in every 4^th row. Meaning that if I could somehow transpose this single column of data into a grid with 4 columns, I will easily get the information I want. Unfortunately, this type of transpose function does not exist in Excel.

After a little googling and tweaking I finally derived a formula that will transpose the data for me. The formula is:

=INDEX($B$3:$B$50;ROWS(D$3:D3)*4-4+COLUMNS($D3:D3))

Where B3 is the 1^st cell in the column of data that you want to transpose and B50 is the last cell. D3 is the first cell of your transposed data and the number 4 is the number of columns you want in the transposed grid. Adjust the formula to match your data and you are ready to transpose.

Copy the formula to cell D3, Drag the formula across 4 cells and then down until there’s no more data to transpose.

And that’s it, copy the row that you need and take a break, you may have saved yourself hours.

Office 365 is changing the rules for Intranets

2017-09-08T15:07:00.000+02:00

Before Office 365 (O365), when we were asked to build an intranet, we built a SharePoint solution with an inviting home page and some sub sites that promotes team collaboration. Maybe we added in a few interactive features and "Ta-daaa", your intranet was done.

Now, its the game is different, and many of the players don't even realise this.

An intranet that is using Office 365 is more than just a pre-defined set of pages that has a starting point and set navigation plan that should be used by all. Its use is now dictated by the users, and if done right, no 2 users will experience their Intranet in the same way.

We all know that O365 has this new, beautiful SharePoint Communications sites, for example, this site can help promote Corporate-Level communication and is typically seen as an ideal Home Page to an intranet, but why force that as the Home Page to everyone? Everyone is different, with different objectives when using the Intranet, why not make the users chose a homepage that best suite their interest?

If they plan to use the intranet to access there web tools quickly, then they could use the O365 Home Page which provides quick access to all their Office tools. Alternatively, they could use Delve, a O365 tool that provides great insight on what relevant people are doing. Maybe the SharePoint Site Selection page should is a preferred starting point, so they can easily view the sites of the projects that they are currently working on. The rule of having a central home page is gone, and we need to focus on an approach that is more user-centric.

This change in the Intranet rules does not apply to the home page alone. Why force everyone to use Team Sites to collaborate? The end users now have a great selection of tools available to them. Tools like Groups, Teams and Yammer are all available via O365 to promote collaboration through discussions but each tool appeals to different audiences (i.e. some users prefer to engage via email, others via chat) so the one size fits all approach is now the wrong way to handle this.

Also, previously, if we wanted our Intranet to stream video content from a secure environment, we needed to invest in a dedicated Video Streaming Server, NOW with O365's video service, its easier that ever to load video content. meaning that users are now using their cell phone, record a quick video that promotes some news or corporate interest and quickly sharing it to all via the Intranet.

Now, thanks to O365 and other cloud services, an intranet is more than just a website, just like a car is more than just an engine, the rules of this game is changing, its less rigid and more user-focused. People are only figuring this out now (if at all), so stop building intranets the way you did 5 years ago, and figure out how the new playing field is removing the restrictions we had before and change your approach before its too late.

SharePoint Saturday Cape Town Presentation

2012-09-08T16:37:00.003+02:00

Thank you all who attended my session, as promised, please find my presentation slides below.

For those who missed it, my talk was on SharePoint and Maturity, I talked about the importance of the maturity assessment, and what maturity assessments models are available.

This presentation is focused around SharePoint, but I am sure the information here can assist you with other types of maturity assessments.

(You can download it here : http://www.slideshare.net/nadirkamdar/oh-grow-up-a-look-at-share-point-and-maturity-2012-final)

SharePoint 15, the BETA review

2012-07-22T13:36:00.000+02:00

I remember the excitement when SharePoint 2010 came out, us developers and architects saw this as the solution that will resolve ALL the flaws found in SharePoint 2007. The SharePoint 2010 demos were, well, to narrow it down to one word, seductive! We were convinced that this would be the best product to sell, deploy, use and support.

But SharePoint 2010 is not perfect. Sure, it kicks 2007’s butt! But some of the issues identified from SharePoint 2007’s time was carried over to SharePoint 2010 and some new issues were “invented” when added functionality and complexity was added.

SharePoint 2010’s approach in providing us with a web solution is already dated by about 5 years. Remember that SharePoint may have been available to the public in 2010, but the magicians in Microsoft started designing, planning and building the solution from 2008 at the latest. That’s when Silverlight still had a future, Social Networking has no place in the business world and the explosion of mobile and tablet devise has not even started, in fact, the iPad wasn’t even invented then!

Therefore, times have changed, and so should your web-based solution. Based on online videos and demonstrations performed by my team, I’m going to provide a beta review on certain areas of SharePoint 15, these areas has been identified as potential problem areas in SharePoint 2010. So I am very interested to see how well they were tackled in the new SharePoint.

I don’t want to rush through each area – so I will publish this post while its incomplete and place the titles of the areas I plan to review, and will update the post when I have done the analysis – add comments if you want me to add more areas.

General Appearance and Usability

SharePoint 2010 looks great when compared to SharePoint 2007. But its “busy” when compared to todays standards, when we deploy solutions to our clients, our most successful deployments involves simplifying the interface so its easier to understand and use.

Let’s back this up with an example. Below is an image of a team site created in SharePoint 2010.

The first thing I see is many things many of my users will not necessary use:

· That large image that means nothing to anyone
· The “Welcome” message Place Holder.
· The “Like” and “Tags and Notes” button

These components are not needed for the team site, yet they are there by default. This clutters the site too much and it confuses the end users. These components are usually removed or ignored.

I won’t start with the navigation right now, (saving that for a later section of this blog). I will add that the “Getting Started” section is a great idea to assist users in getting started with this site, but it’s not intuitive or user friendly and is typically ignored or removed.

Let us check out the SharePoint 15 team site.

(Video walkthrough of SharePoint 15 is available here : http://www.sharepoint-videos.com/walkthrough-of-sharepoint-2013-team-site/)

I am seeing less clutter, useless images and text are removed, important “Getting Started” links has a greater prominence and is more inviting, thanks to Metro Style navigation.

This is a much more inviting, intuitive and user-friendly interface. It even supports drag and drop and a new feature that hides the navigation so the focus is on the content only.

I see good use of “Share” and “Follow”, which is more professional than “I Like it”, and has a more subtle presence that in the previous version. I don’t know right now, but I hope those buttons can be easily removed if requested by my client.

They seem to have tackled all the issues I have identified in SharePoint 2010. 5 out of 5 for this section!

Changing he Look and Feel

[SECTION UPDATED on 29th July 2012]

Every single SharePoint project I worked on has one consistent requirement, that’s to make the SharePoint site not look like a SharePoint site.

Creating a site that is inviting and represents the corporate brand is important, buts its not an easy thing to do, since you need tools like SharePoint Designer to do a decent job.

Microsoft’s best practice approach involves limiting your design to themes, and unfortunately, that approach was very limiting, and more often than not, the designers are forced to open up designer and tinker with the master pages to get that better design. This approach is a workaround that bypasses all the limitations of themes and gives you amazing looking sites but it also affects the stability of the solution. Nevertheless, a visually appealing and inviting site that represents the corporate brand is important – so this has to be done.

What I am looking for in SharePoint 2013, is an easier way to change the appearance, with fewer limitations and no risk of destabilising the environment.

Did SharePoint 2013 deliver?

Well, this, http://www.sharepoint-videos.com/changing-the-look-and-feel-of-sharepoint-2013-team-site/, impressive video shows how easy it is to change the style of the site. You don’t need SharePoint Designer, it has great preview facilities, you seem to have a greater range of templates to get started and also a good range of options when modifying the styles. Seems like they did an excellent job in improving the ability to change the appearance of SharePoint.

Looking at SharePoint related press releases from Microsoft (http://sharepoint.microsoft.com/blog/Pages/BlogPost.aspx?pID=1012 – near the end of the article). It is clear that Microsoft is recommending us to stick to the tools mentioned above to modify the look. Anything done beyond these tools (like tinkering with the site templates in SharePoint Designer) will affect the complexity, performance, upgradability and stability of the solution.

So its still (and now officially) NOT a good idea to move beyond the tools provided to change the look, but is the tools provided enough?

SharePoint is cleaner, simpler and faster by default, so there are fewer reasons to move beyond the tools provided. But the visual element sells the products and gives the client that warm fuzzy feeling when its done with flare, so in order to impress, I see the developers forced to push the limitations and bend the rules to make that client say “WOW”! Sadly, at the cost of performance and stability.

I would have preferred Microsoft to not prohibit us from going beyond the tools, but rather supporting it with guidelines, templates and additional, more advanced tools made for skilled designers.

Navigation

[Section Updated on 3 August 2012]

The navigation provided by SharePoint has always led to difficult conversations with the client.

Navigation menus is important, it needs to have a logical structure that allows the user to navigate to their desired area, and what SharePoint lacks was the flexibility to adjust the menus to fit the structure the end users will find logical.

One problem was SharePoint was adding their own headings like “sites” and “libraries” to the current navigation; these are SharePoint terms that are not familiar to the end users, thus confusing them. Users also expect different types of navigation like fly out menus or tree views, these options were not available in SharePoint (well, the tree view is, but it’s not user friendly, flexible or attractive at all, so as far as I am concerned, it’s an unusable feature).

Other points worth mentioning was friendly URL’s were not used (making it more difficult for search engines to pick up) and navigation did not go past two level unless you edited the code in SharePoint Designer.

So, what I am looking for is greater flexibility in controlling the Navigation in SharePoint 2013. Did they deliver?

This post, https://www.nothingbutsharepoint.com/sites/eusp/Pages/SharePoint-2013-Navigation-using-Managed-Metadata-Term-Store.aspx, covers SharePoint 2013 and navigation pretty well. I see that term sets are now been used to source the menus.

That’s great, in the past when SharePoint navigation sources were not sufficient, we had to create site map files, which gave us the results we needed but really made the support and maintenance of the solution more complex.

So the flexibility of controlling the menus content has improved, friendly URL’s are now available and term sets allow for multiple levels of navigation, and also I see that the confusing terms like “sites” and “Libraries” are no longer there by default. This is fantastic.

The only thing I have not found was the ability to change the menu type to a tree view or fly out menu out of the box, but I am not too worried about that, that’s an easy fix with a little coding and 3rd party products, it was the menu source that gave me the biggest problems.

Mobile Access

[Section Updated on 12 August 2012]

The one big thing that SharePoint 2010 lacked was good mobile support. Sure SharePoint works on a tablet, but not 100% (and for some features, like BI reports, not at all), and today, you expect 100%.

I'm not expecting much when it comes to mobile, all I am asking for is good HTML5 and CSS3 support. With that in place, I’m expecting a good future proof environment (well, until the next SharePoint release at least) what will work on all devices worth mentioning.

Looks like SharePoint delivers, the screenshots below shows good support for HTML5 with the classic options for devices that do not support HTML5 and of course the standard full screen option.

SharePoint 2013 exceeds my expectation by providing push notification support. That’s when a mobile device registers with SharePoint and then receives notifications via Microsoft Push notification services and other similar services, very nice.

My expectations are exceeded even further with location-aware services, where the SharePoint content can be more location aware and can use services like Bing Maps to display information relative to your location.

Also, SharePoint 2013 has improved the ability for apps to interact with the content in SharePoint, allowing for a greater range of mobile apps to be created.

So, I am seeing a lot of new features made specifically for mobile devices, and I also see exciting times for developers who now have more options available when it comes to mobile development, its really hard to find any fault here.

Custom Development

[Section Updated on 26 August 2012]

I have mixed feelings on this one, custom development is very important when deploying SharePoint solutions, but I always tried to sell it as a last resort and then place a lot of control measures to the custom approach to help maintain an acceptable degree of stability on the environment.

In short, I don’t like custom development, it seems to eventually be the root cause to more problems, it dangerously creates a SharePoint environment that does not behave like a SharePoint environment that leads to unpredictable results in scenarios that you may have missed during testing.

What I am looking for when reviewing this component is a set of tools and processes that will give my developers a better “guarantee” that the custom component …
• can be easily created
• Will work as intended
• Can be easily deployed
• Will not affect the stability of the environment

Also, right now, when we create a functionality or feature, be it custom or OOB, we have to deploy it to the Testing server and, once approved, deploy it to the Production server. To do this in a way that that agrees with company policies – we build installation scripts and installation manuals, this document and script creates all the necessary sites and sub sites, the content types, term sets, workflows, configurations, workflows.

It also applies all the necessary configurations. Building this script and manual is takes a lot of time, it’s difficult to build, needs to be frequently tested and it’s difficult to troubleshoot and update, and we do this because there isn’t a good installation package type feature available from SharePoint that can package ALL the necessary components for easy deployment to different environments.

It looks like SharePoint 2013 did not focus on this much needed feature, instead, they focused on Apps and the Apps market so new features and updates are more centrally available and controlled. But these apps has limitations where they can only use client side code and the SharePoint components like content types and term sets that may be needed to make the app work well, will need to be manually created and configured, something that should have been avoided with an installation package.

I can see they are discouraging custom development, and while I know the apps will be a hit. The limitations will be frustrating and more importantly, I wish Microsoft provided a way to more easily package and deploy solutions WITH all its necessary counterparts.

Conclusion

SharePoint 2013 has met my expectation in many ways, managed to find ways to exceed my expectations and in a few yet critical situations, it failed to meet my expectations. This is a BETA review on a BETA product, so things may change once I get to know the product better or get involved in some early training and official demonstrations. Do you think I missed the mark? Let me know

Paperless Trail - Going green with SharePoint

2012-06-04T12:50:00.000+02:00

A paperless office is a work environment in which the use of paper is eliminated or (more realistically) greatly reduced.

You achieve this by converting your documents and other papers into digital form. By “going paperless” you can …

Save money (paper, printers, inks and toners)
Boost productivity
Save space
Make documentation and information sharing and searching easier
Keep personal information more secure
And, of course, help the environment.

We are now more environmentally aware, we see the production of paper as a significant contributor to deforestation and climate change. We have added measures like recycling and tree-free paper to help reduce the environmental impact, but online document management systems like SharePoint is seen as the strongest contributor in reducing the need for paper.

A solution like SharePoint provides a paperless alternative to …

Business cards, Index cards and Rolodex
Typed/written letters and faxes
Diaries and schedules
Training material
Reference books
LOB documentation for sharing and collaboration
Record keeping
Paper-based processes

Providing an alternative to paper-based processes that rely on forms, applications and surveys provides a solution to the biggest hurdle in having a paperless office.

Also, our behaviour is changing. Thanks to the popularity of digital mobile devices like e-books, tablets and smart phones, we are finding it more acceptable to read documents and other information over these mediums – this means that the advantage that paper use to have about being mobile is now phasing away so this paperless office concept is truly possible.

So how do we use SharePoint to achieve a paperless office?

Back scanning (with 3^rd party solutions like http://www.kofax.com/) and migrating all your existing records and LOB content into one SharePoint environment is a great first step. Right away you will see the cost saving of phasing out all your existing paper – also when you consolidate all your document storage environments to one digital environment, there are added benefits in having one central point for the users to find and store information, that’s one learning curve and one infrastructure to support and maintain.

Next, you want to convert your manual paper-based processes to more automated SharePoint workflows. The biggest obstacle many organisations face when phasing out paper based processes is the fact that documents needs to be signed to acknowledge acceptance or approval. Tools like InfoPath that integrates well with SharePoint, provides digital signature capabilities with additional security features like making the signature become invalid if the information on the form has changed.

Once the workflows are in place, your business will produce less paper, and the processes would have better control and faster turnaround time. So that’s a saving on cost and time.

I mentioned earlier how the main advantage of paper, i.e. the mobility of paper, is slowly phasing away due to electronic mobile devices. SharePoint is a web based solution, making it easily accessible to these mobile devices – also, doe to the popularity of this product, there are plenty of apps available for iOS and Android, while the Windows 7 devices come with strong integration features by default.

The only real unavoidable use of paper would be around compliancy around the external companies you will need to interact with, companies and government organisations that follow paper-based processes that needs to be followed for compliancy reasons – maybe in time, when the paperless approach is more mainstream – this can be avoided.

The problem with small companies

2012-04-21T14:54:00.000+02:00

Up until the start of this year, I have been working for small consultancy companies. While I have always enjoyed the flexibility of this environment, I have never really had strong exposure to large organisations until now.

The company I work for has been acquired by a much larger organisation, meaning that I am now part of a big company. It’s been three months since the acquisition, and while the transition is still taking place, I am already noticing a shift in my thinking. I would like to share this with you, and maybe add on to this post as more differences are identified.

Before I continue, I just need to add that the points below are not necessary points from the company I work in. I have been in this industry for a long time, and have built relationships with all kinds of people in different positions and companies (including my competitors) and have managed to pick up some consistencies and facts worth mentioning. Some of these points are negative, which is why I believe people are afraid to talk about it, but I also believe that these points are too important to keep to myself. I hope you enjoy.

Small companies NEED work to pay salaries

As a small company, your biggest expense is salary. If you don’t get enough work, you don’t have a good enough cash flow to pay salaries and other debts. So, as a small company, your number one objective is to get that sale, no matter what!

“No matter what” may be a bit strong, but there is a lot of pressure to get that sale, and as a small consultancy company that specialise in Microsoft Web Solutions, you will know that competition is tight, and to make things worse, its tight against other small companies who also NEEDS the sale to improve their cash flow.

So what does this mean? Small companies in a highly competitive market tend to over promise, and cut down prices to secure that deal. After all, a little revenue that can make you break even, is better than no revenue, right?

As a larger company, the cash flow is not a problem. Salaries are paid by HR and no matter how much sales you get, you can rest assured that the salaries will get paid. This does not mean that we can rest on the sales component and do less work. No, while we still have the responsibility to be profitable, and to meet certain sales targets, the cash flow component is not 100% dependant on the sales we get.

This is great, better cash flow provides more options. More cash can be dedicated to infrastructure, training, staff morale and a few other perks that was not available as a small company.

Large companies are more focused

When you are responsible for a division of a small company, you are in charge of every single thing related to that division. You are fully involved in the hiring, the training, the social events and not forgetting the actual service delivery.

When you are part of a large company, many of these tasks are “outsourced” to other divisions of the organisation that specialise in these things. Things like social events are no longer your problem, your involvements in the hiring are training are also drastically reduced, allowing you focus on the things that are important – your service delivery.

And even on the service delivery side, you can focus on a specific service (like only SharePoint projects) while another division can focus on other services (like ASP.NET projects). This focus provides huge advantages on the delivery quality side because your focus and training and strategising is focused and on a specific service, which is a huge adjustment when compared to small companies.

Small companies try to have as little staff as possible that can do as much as possible, or alternatively, they get a lot of junior staff that they task with more senior work (preferably mentored by a senior), either way, the objective is to keep their biggest cost down, the salaries.

Now in order to keep salaries down, and yet be able to deliver on whatever sales has over promised, small companies need staff that have a variety of skills, they don’t have to specialise in anything just be able to know a variety of things well enough to come off as a Subject Matter Expert and be able to Google the rest. This sounds cruel, and may be a bit exaggerated, but sadly, not by much.

Small companies are more flexible

Being part of a large company, I must say that the processes for action and decision making are stricter and even a little longer. There is a lot more paperwork that one has to go through to achieve the results. It makes sense when you consider that there are more people involved, meaning that more can go wrong if we are working in a more chaotic state.

In a smaller company, you can get away with it because you usually have full control of the situation. This lack of processes allows you to deliver faster but it also means that more can go wrong. In the Microsoft Service Delivery space, clients are placing a high priority on speed so the smaller companies will have a big advantage here.

This “advantage” leads to another point. Smaller companies are willing to accept higher levels of risk. The faster delivery comes at a cost, and that cost is increased risk because processes are not strictly followed. When you are part of a larger company, especially a listed company, you have a greater responsibility to deliver well, after all, somebody’s retirement fund may be indirectly dependant on your performance, and because of that, a more risk averse approach is necessary.

Large companies has more available for less

As a small company, I have an internal design team that I use primarily for website designs, then team is skilled in designing marketing material like posters and banners, but whenever I get that type of work, I have to outsource the printing component to a printing company who charge normal market rates. The same goes for software licencing and acquiring hardware. While these services greatly complement my service delivery I just don’t have any skills, experience or equipment to deliver on this (and I don’t plan to build on this, because the demand isn’t there) forcing me to find alternative companies to assist me, who will offer there service at full price. Over time, you can build a relationship with these 3^rd parties and get the price reduced, but you get the point I am trying to make.

As a large company, these complementary services are more easily accessible (chances are, there is another division in the company that provides that service) and the cost is less because these divisions has built great relationships with their supplier and the work is not outsourced to a 3^rd parties. This allows the large companies to deliver more with less dependency on others and less cash.

This advantage goes beyond service delivery. Benefits such as medical aid, RAF, etc. are now available at better rates. As a large company, you have more buying power with these insurance and medical aid companies and can form better relationships when compared to smaller companies resulting in better rates and cover.

Conclusion

Right now, I am very positive on what large companies can offer. I say this from a point of view of a client looking for a service from a outsourced company or as a skilled individual seeking employment.

They seem to tackle most of the disadvantages that exists in smaller company’s very well, especially the cash flow problem. While the cost of this is stricter processes and longer turnaround time, this does create a more governed or less risky environment. I also like the fact that the service delivery is more focused, thus resulting is higher quality output.

UPDATE : The feedback I am getting on this post indicate that many think that I am wrong in my findings (something about be identifying common problems and not necessary the difference between small and large companies), this may be true, since I have only been part of the large organisation for about 3 months now and may be jumping to conclusions here. So please think of this as my initial findings, and if my point of view changes, I will update this post - Thanks.

How to write good technical documents

2012-03-10T18:39:00.000+02:00

Developers love to code and hate to write documents, but documents are important in IT.

We need documents to define how the solution will be built so that coders who enter the scene later in the project can easily catch up. We need operational and training manuals so the people who are looking after the solution are aided in doing it right, we also need numerous project management related documents but today, I want to focus on those documents that are more technical in nature, and somehow ends up on the coders lap, who usually has no idea how to handle this responsibility.

Firstly, let’s start off by saying that document writing is a skill; it’s a valuable skill that coders should embrace rather than avoid. It may take you away from your comfort zone, but building this skill alone will helps you advance your career to the next level. So give this responsibility proper attention and you will see the rewards.

But how does one write good technical documents? Project managers tend to give this responsibility to coders without guiding them. It’s a common mistake, and I hope this post will provide the guidance you need.

Get a template

The first thing you should do when given this task, is ask for a template or a similar document from a previous project. If your company has a good capability maturity, then these templates should be easily available. If you work in a company that is less mature (or a nastier way to put it, “more chaotic”) and you will need to build the document from scratch. Its more work, but don’t freak out, the main purpose of the template is to provide a tried and tested format, that’s it, you should not see a document from a previous document as an opportunity to copy and paste 70% of the work, that’s messing up the quality of the document and you are robbing yourself from the opportunity to build up on your document writing skills.

So yes, you need to have a format of the document. If the document is well formatted, it will be well organised so it’s easy to read and easy to understand. If your company does not have templates for you, there are plenty of templates on the internet; many of them are free so you may want to go there for a little help in getting started. Look at multiple documents, and come up with an outline that works for you.

Know your audience

As a coder, it is natural for you to use IT jargon in your everyday language, you talk to other coders in that language, and they understand you and respond appropriately. When you approach a non-coder and communicate with them, you make that extra effort to use words that “normal” people use so they can understand and respond appropriately. Writing a document works on the same principle.

So if you are writing a technical document that will assist other coders in installing that brilliant add-on you created, you can keep the IT jargon. But if you are building a document for managers or end users, then cut down on the jargon, use simple English and add think about adding a few more pictures like screenshots or wireframes, they love that.

Why is this document important?

Ask yourself what value you are adding, by creating this document. What’s the purpose of this document? Once you have an answer to this question, you can draw of a draft and then ask yourself if this document is achieving its intended purpose. If it is, you on the right track.

Gathering Information

Make sure you have collected all the information and facts needed for the document, use the template to assist you in what type of information you need.

Gathering information may involve digging in the code built by you or other people, but don’t forget to ask other people for information as well, it may be quicker that figuring it out on your own. Even communication is a skill, it’s a skill that many coders avoid, and this is a good opportunity for you to step away from your comfort zone and work on those skills.

First Draft

With the information above, you should be able to draw up your first draft. The first draft is really filling out each section of the template with the facts and information it needs. After that is done, you can focus on the writing.

And now the writing, finally

The first draft does not consider writing style and formatting, at that phase, it’s all about the facts. Now you need to up the quality with proper formatting. here are some points to help you:

Don’t try to be too fancy with the language. Keep it simple, clear and unambiguous so there is little room for confusion and misunderstanding.
Be consistent. If you are using the words “Power User” to describe a particular role, don’t change it to “Business Administrator” later in the document.
Use bullet points and images where ever possible. it’s easier to read, and important information isn’t lost in the paragraphs.
Avoid contradicting yourself.
Keep the document honest with truthful facts, even if it’s bad news.
Use numbering system so your document can be easily referenced later.

Well, I hope that helps. If you have any more helpful points, please add it to the comments. And to those coders writing a document – Good Luck and embrace this opportunity.

What Are SharePoint Architects?

2012-02-19T19:21:00.001+02:00

When we look at the minimum resources needed for a SharePoint environment, we think SharePoint administrator and maybe the “can-do-it-all” SharePoint Developer.

Don’t get me wrong, these resources are great and are vital for the stability and growth of the SharePoint environment, but they are essentially the workers, or the term I like using, the brick layers (don’t take it as an offensive term, that’s not my intention), they have a strong focus on the technical component of the SharePoint environment, and although they can achieve greatness on their own, they can achieve so much more if they had guidance from someone with a broad understanding of SharePoint (not necessary focused on the technical side) with the ability to map a business problem to the SharePoint technology, that can engage with the business user and plan an approach that will achieve the greatness that SharePoint can provide. They are the SharePoint Architect.

The reason why I am focusing on this role right now, is the fact that this role is truly misunderstood (This role is typically given to the SharePoint Developer), and regarded as not important in SharePoint deployments. It’s also a rare skillset, there isn’t decent SharePoint Architect training out there, well, nothing officially from Microsoft from what I can see.

I also looked around on who is providing this skillset, and noticed a disturbing commonality. People that play the role of SharePoint Architect are not dedicated to this role, I met a few SharePoint Consultant companies, and noticed that there “SharePoint Architects” are also the “Accounts Manager”. This means they are from sales with the primary objective to sell SharePoint (i.e. bias towards using SharePoint) but they call themselves Architects. The problem with this is that Architects needs to match the business problem to the SharePoint technology and if the technology does not match the problem, i.e. too much customisation is required to achieve the desired result, then the Architect needs to say no and recommend an alternate/non-SharePoint approach. Can the people from Sales do that? I don’t think so.

So, what makes a good SharePoint Architect?

The Architect has …

• A broad understanding of SharePoint, the Architect understands the building blocks of SharePoint (IIS, ASP.NET, Virtual Directories, AD)

• A good understanding of what functionality SharePoint can provide, the types of configurations that’s available, the limitations of the functionality, and what can be achieved via OOTB (Out of the Box) or Customisation.

• Sufficient knowledge in capacity planning and performance monitoring

• A good understanding of SharePoint advanced features, like Business Intelligence, FAST and what SharePoint license is required to use these features.

• Knowledge on how to integrate with other systems

• Good communication and documentation skills

• Best Practice approaches in defining a strategy for SharePoint and other common tasks like Migration

The Architect is heavily involved during the planning phase of your SharePoint deployment, the Architect’s skillset is more focused on achieving the business objective, by using best practice approaches and building proper plans that are achievable by the SharePoint administrator or SharePoint Developer.

Without them, you essentially have brick-layers and plumbers, eager to build, without anyone drawing up and approving the plan. This will eventually lead to an unstable environment, with no capacity planning, and solutions that built solutions that do not resolve the business problem.

SharePoint in the year 2011

2012-01-01T12:31:00.000+02:00

While a new version of SharePoint did not come out in the year 2011, it does not mean that nothing happened in that year. This post will highlight the key achievements of SharePoint in the year 2011 and also my predictions in the year 2012.

The phase out of SharePoint 2007: SharePoint 2010 was introduced in the 2nd half of 2010. The year 2011, started the phase out of SharePoint 2007 and the phase in of SharePoint 2010 – this is evident when you look for SharePoint training currently available. At the start of 2011, Good (or any) SharePoint 2010 training was few and expensive, and now, at the end of 2011, SharePoint 2007 training has been completely phased out.

Using SharePoint better: People loved SharePoint 2007, but they were not using it correctly, which introduced many pitfalls, as mentioned in my blog post written exactly one year ago (http://nadirkamdar.blogspot.com/2010/12/common-pitfalls-in-sharepoint-projects.html) . The year 2011 shows that we are starting to use SharePoint better, we know the potential risks and pitfalls are have taken the additional steps to mitigate it. This is evident as more emphasis/training/articles is now placed on planning and governance.

Improved stability, performance and security: The latest Service Pack for SharePoint 2010 was released in November 2011. The service pack included the following improvements:
• Improved support for Internet Explorer 9.
• Recycle bin: Lets you restore a site collection or a web that was deleted.
• Remote Backup Systems (RBS) and shallow copy can decrease downtime and increase efficiency by moving pointers to databases instead of moving databases.
• You can see which folders are taking up valuable space with the improved Storage Management feature in site settings.
• Support for Microsoft SQL Server 2012.
• A more robust Search Host Distribution service that improves error recovery and performance during the search crawl.
• Adds backup and restore functionality to recover deleted site collections and webs.

Microsoft Launches Office 365 and places SharePoint in the Cloud: In June 2011, Microsoft officially launched Office 365. This includes the launch on the cloud based version of SharePoint 2010 (called SharePoint Online). This positions SharePoint as an essential productivity and collaboration tool, placed in the Cloud making that is affordable and appealing to any businesses.

SharePoint matures with BI and Visio 2010: While BI functionality was available on SharePoint 2007, great improvements were introduced in SharePoint 2010 with the much needed integration into Visio 2010, but we only understood the power of these advancements in 2011, when Microsoft introduced BI and Visio specific Demo VM and videos. Theses Demo VM’s can be sound here : http://mssalesdemos.com/ .

SharePoint designs are better: A very common request when deploying SharePoint solutions is to make SharePoint not look like SharePoint, achieving this compromised the functionality, and I complained about this in my earlier blog posts ( http://nadirkamdar.blogspot.com/2010/07/please-dont-pimp-your-sharepoint-site.html ). Today, I’m not complaining. As a balance can be reached where there site is fully functional with a strong look. This balance is achieved after a years’ worth of lessons learnt and new design related articles and guides. A good showcase on what can be achieved can be found here: http://www.topsharepoint.com/

So what’s in store for SharePoint in the year 2012?

Well, Office 365 will receive better adoption, and the shift to the Cloud will be more prominent.

The new SQL Server will launch, resulting in better BI functionality, which will directly affect SharePoint. Maybe improved speed and better functionality in features like Reporting Services and Power Pivot.

Better support for mobile devices: I don’t believe they got this quite right as yet, as seen in the video below (its good, but not perfect), and mobile support is just too big to ignore.

What I am expecting is a service pack that provides site templates that makes better use of HTML 5 and CSS 3 or at least templates that make better use of W3C compliant code and JavaScript so it works well on these devices.

The end of SharePoint 2007, If you or your corporation are using SharePoint 2007, it will most likely reach its end of live in 2012, which means upgrade/migrate or use what people will soon call the “Old SharePoint”. Due to the reduction (or complete removal) of SharePoint 2007 training, there will soon be no resources available to support the environment. No support means no growth and if your technology cannot grow, its becomes a liability.

While 2011 was about strong awareness on how we are destroying the world, 2012 is about us doing something about it. 2012 will see SharePoint as a strong paperless option for collaboration and productivity.

So that’s my take on SharePoint in the last year with a few predictions in the year to come. Tell me what you think?

The importance of de-stressing

2011-11-28T12:14:00.000+02:00

Like many hard working individuals (I want to say workaholics, but I think that’s too strong a word), I have designed my entire lifestyle around doing my job as best as I can.

To start off with, I make sure I get plenty of sleep so I so I am fully charged to handle the 8-10 hours of taxing activities. I follow news and subscribe to feeds that relates to my work, and catch up whenever I have some free time. I don’t smoke, don’t drink alcohol, don’t do drugs and limit my coffee consumption to social outings. I even try to stay active and eat healthy since it affects my health and eventually the way I work (but admittedly, I easily compromise those tasks when more after hour work is required or the tasks I handled for that day was more draining than usual).

While I think it’s important to give your job high importance, I also understand that life is about balance. So while your job may expose you to stress, it is important to counter that with some de-stressing tasks.

Without de-stressing, your job WILL cause the following:

Loss of enthusiasm
Loss of motivation
Symptoms of depression (sadness, low self-esteem, isolation, tiredness)
Over sensitive
Over anxious
Short tempered
Overeating or loss of appetite
Disturbed sleep
Feeling overwhelmed

My de-stressing techniques are the following. They are simple, yet effective.

Talk to a friend.
I follow this rule with limitations as too much of this affects the quality of your work. I have turned off all my social network notification tools, I don’t use any chatting program and only check my Gmail after hours or during lunch (and that’s if I’m taking lunch) as these tools are distracting and prevents me from doing my duties optimally.
But speaking to someone that can take your mind off work for a few minutes, and in that time, you get a smile on your face or even a bit of laughter, you just got yourself an instant de-stressor.

Take your mind off things.
It is important to not think about work when you not working, but when you have tight deadlines, your responsibilities tend to stay in your head, even after hours, stressing you out. So it’s important to find an activity that can pull you away from that.
I can suggest that you do something worthwhile like participate in a charity event so you can find fulfilment via some alternative avenue, but my approach is video games (not very noble, probably regarded by most as a waste of time, but it works).
Video Games allow my mind to enter this alternative universe, and focus on the challenges presented in the game, with that sense of fulfilment when I have accomplished the task. The best de-stressing games I played to date is Batman: Arkham City, but I hear Call of Duty and other FPS (First person shooter) games are great for de-stressing as well.

Get away
I always believed that it was a good idea to build up my leave days to a significant number, and then use it for some significant event like an overseas holiday.
In reality, those overseas trips are few and far between and I learnt, that sometimes you need to take time off to do nothing, i.e. have no plans but to relax.
You could plan to go to some relaxing spa, maybe have a picnic or have a nice walk in the forest or up the mountain. Don’t be scared to take off a day from work every now and then just for relaxing (I know this one may be easier said than done - and if you have any advice on how to do this, let me know)

So that it, that’s my de-stressing techniques, the important thing is that you have to love your job and gain fulfilment from it, it makes all the stress worthwhile.

SharePoint 2010 and Content Migration Presentation

2011-11-05T17:59:00.000+02:00

If you managed to catch my SharePoint 2010 and Content Migration presentation for SharePoint Saturday, thank you for attending - as promised, my slides are attached.

If you didn't attend my presentation, which is very possible sine the majority of my audience is from the States, India and Jhb. I tried to make the presentation as clear and detailed as possible. I also reduced all the unnecessary images I used to give the presentation more live so the downloads are much smaller. Enjoy.

Don’t use SharePoint 2010’s mobile view for internet presence sites

2011-10-12T13:34:00.000+02:00

SharePoint offers a lot; it’s a platform that provides BI, ECM, Search, collaboration and a lot more including WCM. It’s the Web Content Management (WCM) component that makes it’s a great platform for internet presence sites and with the more appealing pricing structure available for internet presence, it is becoming the preferred choice to internet deployment.

Without any effort from the SharePoint deployment team, SharePoint provides a “mobile friendly” view of the site been created. In theory, this sounds great, this means that we can create a SharePoint site, apply a nice appealing brand to it (it is an internet site, so the look is important), spend some time on the structure the content so information can be easily found (very important for internet sites), configure search correctly and then fill the site with information and images knowing that it will automatically work out the best way to display this information on mobile devices, right? WRONG!

SharePoint offers a mobile friendly version of the site, that is true, but when they created this mobile friendly view, they created it for the convenience of collaboration, and the factors that are important for internet sites were not included in the mobile view.

Here are a few examples:

By default, mobile view does not allow anonymous access, there are ways to override this, but that should be your first clue that the mobile view is not internet friendly.

The look and feel is not displayed in the mobile view, and there is no interface to change the look and feel of the mobile view, meaning that your internet brand will not be viewable in the mobile devices, solutions in solving this problem involves editing files located in the 14 hive folder, and that approach is not best practice, and should be avoided.

Navigational structure and content pages is not displayed in the mobile view, the main part of an internet site is the navigational structure and the content. Those elements are ignored in the mobile view; instead, the mobile view’s navigational menu provides easy access to SharePoint lists and sufficient control in managing those lists. So if you have some hidden lists on your internet sites (like a list to store feedback responses) they will be easily accessible in the mobile view. Also, since the mobile view is focused on list management (and not the displaying of web parts), web parts containing content and information are usually removed from the mobile view.

Search facility is not working in mobile view. Search is becoming the preferred way to search for information, so all the effort in classifying and indexing your information is rewarded by providing powerful search functionality, this functionality is turned off in the mobile view.

If you don’t believe me, here’s proof. Go to Microsoft’s SharePoint site, http://sharepoint.microsoft.com, look and the brand, the navigational structure, and the content and the search functionality. Now go to the mobile view of this site, http://sharepoint.microsoft.com/en-us/Pages/default.aspx?mobile=1, you will notice that you will need to sign in (thankfully, your hotmail account will work), and after you login, look for the brand, the navigational structure, the content, and now try to search for it. It is clear that this view is not designed for internet sites.

So how can I make my site more mobile friendly?

SharePoint looks at the browser accessing the site and if the browser is a mobile browser, the user is directed to a mobile friendly template.

The file that contains the list of mobile browsers can be edited to stop the redirect from taking place or you can make use of URL rewriting features. This will cause the mobile device to load the standard view of your site and since SharePoint sites are W3C compliant, it should work fine on those devices (but they will probably have to do a bit of scrolling).

Best practice will involve you doing a little work on the standard view, you will need to create a mobile friendly CSS file and with some clever JavaScript you can load the correct CSS that will provide the mobile friendly look on mobile devices.

UPDATE : Mobile Entrée (http://www.mobileentree.com), seem to have a good solution this problem, they created a 3rd party extension of the SharePoint platform that provides mobile friendly sites.

Sharing files with a iPad/iPhone/iPod wirelessly

2011-09-18T15:27:00.000+02:00

I have a Windows laptop that I mainly use for work and blogging, a PC at home that is configured to act as a HTPC (Home Theater Personal Computer), an iPhone and an iPad.

When I am at home, all these devices are connected to my local network via WIFI. Some of my devices download files like eBooks, while others act as my eBook reader, the same thing happens with movies. so there is this need for me to easily share files amongst my devices and I dont want to connect my iPhone/iPad to the PC/laptop via USB - its just too much effort and I am sure I can find a better solution.

Cloud based file storage mediums like DropBox are available, which pretty much means that I can store my files on the internet and access it via any other devices, but I have a problems with this approach. Cloud based file storage mediums cost me bandwidth, and in South Africa, bandwidth is expensive (which is why I believe that South Africa will be slow adopters of cloud based solution – but that’s another topic for another blog).

If I want to copy my 1MB photograph taken from my iPhone to my laptop wirelessly, I will need to upload the file to my DropBox storage via the iPhone app. This will cost me 1MB of bandwidth, I will then need to use another MB of bandwidth to download the file to my laptop, I know this sounds petty, but do this for 10, 20 or 100 files, the bandwidth adds up and it is really unnecessary, especially since all my devices are connected to my local network so I should be able to do this without using the internet.

There are other disadvantages like slow speed, restrictions with the storage medium and this actual step of uploading or duplicating files just to make it available for sharing. So the preferred alternative would be the ability to share files across my local network.

iDevice (iPhone/iPad/iPod) developers figured out how to transfer files from their iDevice over a WIFI network. They have created apps like File Sharing (http://itunes.apple.com/us/app/file-sharing/id300556365?mt=8), Air Sharing (http://itunes.apple.com/app/air-sharing/id289943355?mt=8) and Good Reader (http://itunes.apple.com/app/id306277111?mt=8) just to name a few (there’s a lot out there). They all work on the same principle. They use WebDAV protocol (http://en.wikipedia.org/wiki/WebDAV ) that makes it possible to access files on the iDevice via a URL.

WebDAV protocol are perfect for this purpose, it’s a standard set of rules and methods that makes it possible to edit and manage documents all via HTTP, it essentially makes the device on the network a readable and writable medium.

Having an iDevice app that uses WebDAV is a step in the right direction (I installed and love Good Reader) but more needs to be done for me to achieve a true file sharing environment across all my devices.

The biggest problem is that I can share files from my iDevice, but I cannot share files from my PC or laptop – now for me to get that right, i need to get my PC or Laptop to share files via WebDAV or FTP protocols (Good Reader allows you to access other shares on the network via WebDAV or FTP) – and that is a lot more complicated that it sounds – I tweaked around with my IIS settings, loosen my security configurations, installed all kinds of server type software on my PC/Laptop, read all kinds of articles on how Microsoft did a poor job in WebDAV support and just about gave up – I was unable to make my PC/Laptop files accessible to my iDevices. Finally I found some a programme that provides a “User Friendly, personal FTP and WebDAV server”.

The programme is called neteK (http://netek.berlios.de/ ), its freeware but its incomplete, the FTP part is working, but the WebDAV part is not, I tried contacting the developer as I believe that there is a market for this product once the WebDAV component is working, but he is not replying to my emails and from what I can see, this product was last updated in 2006 – so it’s probably a dead product – but he made his source code available (http://developer.berlios.de/project/showfiles.php?group_id=6106) - so if anyone wants to work on a product that provides a user friendly, personal FTP and WebDAV server – get a kickstart from the source code and work on a solution. I believe that this problem I am experiencing with sharing files amongst my devices is a common problem and is rapidly becoming a big problem as more different types of devices are connected to the network and needs to talk to each other, so there is a market for this product. Imagine how great this product will be if you can get it working for iOS, Android, XBox, PlayStation, Wii, BlackBerry, Windows, Linux, etc.

Good luck, When you get rich with this product, remember to mention my name :)

SharePoint 2010 and Branding

2011-08-28T15:17:00.000+02:00

Some time ago, before SharePoint 2010 was popular, I write an article (http://nadirkamdar.blogspot.com/2010/07/please-dont-pimp-your-sharepoint-site.html) pleading with the world to please stop creating fancy looking SharePoint sites, because you are not using SharePoint the way it was intended, and you are creating a maintenance nightmare.

Now, with SharePoint 2010 around, I think its time we re-visited this topic.

Lets me start off by saying that SharePoint has improved greatly when it comes to branding, but I still believe that strong branding should be limited to internet sites.

Intranet sites are more feature heavy, and those features has a default size, layout, structure and design that fits the default look provided by SharePoint, so I still see a lot of additional time spent on tweaking the features so it fits the new look, and then a lot of time will be needed to maintain these tweaked features.

So, when you want to apply a brand to a SharePoint site, use the publishing templates, these templates are designed to be a strong WCM (Web Content Management) type site, so the features are more limited to publishing content rather than collaboration and BI (Business Intelligence) type features but the overall look of the template is easier to adjust, tweak and re-design.

There are now some great tutorials out there to help you be a SharePoint Designer Guru; I like his article straight from the Microsoft MSDN site: http://msdn.microsoft.com/en-us/library/gg430141.aspx, and if you prefer a video, here’s a great one from Technet: http://technet.microsoft.com/en-za/sharepoint/hh239611, it’s a series of WCM videos but Chapter 5 gives you a 40 minute video just on branding.

And, after all that training, if you need inspiration, check out Top SharePoint: http://www.topsharepoint.com/ for the biggest gallery of Branded SharePoint sites.

3d printing - the next big thing - see it in action with Lego

2011-08-21T07:43:00.000+02:00

The NXT STEP - LEGO® MINDSTORMS® NXT Blog: Amazing Mindstorms 3D printer: Brought to us by Arthur Sacek from Brazil's ZOOM Education for Life , this very well built and surprisingly precise 3D milling machine is tr...

That O365 Guru from Cape Town (#NadirKamdar)

Mastering Excel Office Scripts from Beginner to Expert: Creating a Well-Designed Excel Report

What are Office Scripts and Why Use Them?

Beginner: Recording and Writing Your First Office Script

Intermediate: Running Office Scripts with Power Automate

Expert: Advanced Automation – Generating Excel Reports via SharePoint and Office Scripts

Tips for Success and Best Practices

Conclusion

Using SVG Images in PowerApps: Scalable Icons, Dynamic Data, and Animations

Why Consider SVG in PowerApps?

Embedding SVGs in PowerApps (Image Control vs. HTML)

Where to Find SVG Icons and Images

Example: SVG Icon (Creattie) with a Hover Color Change

Binding Dynamic Data to SVGs

Adding Animations to SVGs in PowerApps

Example: Dynamically Animated Bar Chart with Easing

My Journey to Lesotho – Afriski: A Snowy Adventure to Remember

Tipping for Takeaways: A Counter-Intuitive Trend in a Customer-Focused Era

My Uber Eats Wake-Up Call

The New Norm of Upfront Tipping (and Why It Feels Wrong)

Tipflation: Tipping Creeps Into More Places

When Tipping Hurts the Customer Experience

Putting Service Before Tips: How to Fix It

Conclusion: Let Gratitude Follow Service

Power Automate OData Filter Query 101: Filtering SharePoint Columns for Beginners

What is an OData Filter Query (and Why Use It)?

OData Filter Query Basics (Syntax & Tips)

Filtering a Yes/No (Boolean) Column

Filtering a Date/Time Column

Filtering a Currency Column

Filtering a Lookup Column (Single-Value Lookup)

Filtering a Choice Column (Single-Select)

Filtering a Choice Column (Multi-Select)

Filtering a Person (People Picker) Column – Single Person

Filtering a Person Column – Multiple Selections

Filtering a Hyperlink (URL) Column

Filtering a Managed Metadata (Tag) Column (Single Value)

Filtering a Managed Metadata Column (Multi-Select)

Example: Combining Multiple Filter Conditions

Notes and Common Pitfalls

References

Using SharePoint’s validateUpdateListItem in Power Automate: A Beginner’s Guide

What is validateUpdateListItem and Why Use It?

Setting Up the HTTP Request in Power Automate

Understanding the JSON Body Structure

Updating Different Column Types with validateUpdateListItem

Yes/No (Boolean) Columns

Date and Time Columns

Currency Columns

Lookup Columns

Choice Columns (Single and Multi-choice)

Person or Group Columns (Single and Multiple)

Hyperlink or Picture Columns

Managed Metadata (Taxonomy) Columns – Single and Multi-value

Other Field Types (Quick Notes)

Using the HTTP Action in a Flow – End-to-End Example

Comparing validateUpdateListItem with the Standard "Update item" Action

References

PowerApps Patch 101: Patching SharePoint Columns for Beginners

Patch Function Basics (Syntax and Usage)

Patching a Date Column

Patching a Currency Column

Patching a Lookup Column

Patching a Choice Column (Single-Select)

Patching a Choice Column (Multi-Select)

Patching a Person (People Picker) Column – Single Person

Patching a Person Column – Multiple Selections

Patching a Hyperlink (URL) Column

Patching a Yes/No (Boolean) Column

Patching a Managed Metadata (Tag) Column (Single Value)

Patching a Managed Metadata Column (Multi-Select)

Conclusion

References

I have seen the future in IT.

Get into ChatBots NOW, its what your customers want, but don't know it yet

SharePoint Lists Views + JS Links + Data Tables = Awesome Threesome

SharePoint Lists Views

JS Links

Data Tables

So how do we get this Awesome Threesome?

What is `validateUpdateListItem` and Why Use It?

Updating Different Column Types with `validateUpdateListItem`

Comparing `validateUpdateListItem` with the Standard "Update item" Action