<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jenn Taylor on Medium]]></title>
        <description><![CDATA[Stories by Jenn Taylor on Medium]]></description>
        <link>https://medium.com/@deepwhydesign?source=rss-f8ef453ff045------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*kxAK5fIvXoG0cAOZDOf_Jw.png</url>
            <title>Stories by Jenn Taylor on Medium</title>
            <link>https://medium.com/@deepwhydesign?source=rss-f8ef453ff045------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 23:11:26 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@deepwhydesign/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Salesforce Obscura: Nonprofit Data Pipeline On the Cheap (Part 4): Skyvia]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-skyvia-part-4-bcb8c0426e6c?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/bcb8c0426e6c</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[nonprofit-data]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[salesforce-for-nonprofits]]></category>
            <category><![CDATA[nonprofit-technology]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Sun, 21 May 2023 18:26:08 GMT</pubDate>
            <atom:updated>2023-05-21T18:45:13.817Z</atom:updated>
            <content:encoded><![CDATA[<h4>The vital glue that lets all this happen</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E-xbYK7k2UNe_jF0AZRwHQ.png" /><figcaption>In this article, we are setting up the highlighted section — Nightly SkyVia Data Replication — that sits between Salesforce and Azure SQL Server</figcaption></figure><p>So far in this series, we have explored <a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-2-the-ecosystem-365ec39bdd96">why</a> you might want to set up a simple and inexpensive data pipeline from Salesforce to Azure SQL Server, and <a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af">how to prepare your Azure SQL Server</a> environment. This article takes you through using <a href="https://skyvia.com/">Skyvia</a> as a relatively inexpensive, simple, and reliable connector between the two.</p><p>This article will take you through:</p><ol><li><a href="#d23d">Creating your Skyvia connection to Salesforce</a></li><li><a href="#cee9">Preparing your Azure environment</a> to allow Skyvia to connect</li><li><a href="#8c7b">Creating your Skyvia connection to your SQL Server</a> environment</li><li><a href="#02f0">Setting up your replication</a></li><li><a href="#14cd">Setting up the scheduled sync</a></li><li><a href="#90b8">Considering ongoing maintenance</a></li></ol><p>The work we outline in this step is often the realm of a data engineer or someone very similar. A data engineer is someone with a foot in a few different worlds, but with a focus on efficiently and correctly moving data from one source to another. You may have heard the term “data pipeline” — how data flows from one database to another. A data engineer typically designs and executes on the data pipeline based on your unique needs.</p><p>For this type of very simple setup, your Salesforce systems administrator can execute on these steps and would often be the one to oversee the maintenance of the synchronization and the Skyvia job itself.</p><h4><strong>Why Skyvia instead of something else?</strong></h4><p>I don’t really have a strong opinion in any direction about the tool used to get the job done. Whatever you can afford, sustain and support is the right tool for you.</p><p>While I’ve touched most ETL tools in my career, these days I only care about whether the tool is affordable for a midsized nonprofit and if it’s sustainable for that same midsized nonprofit, who doesn’t have a coder on staff to tend to an ETL solution or roll their own. I mostly need to reliably ship data from point A to point B. I’ve been impressed by Skyvia. It grew out of a well-established ETL firm, it’s secure, and easy to use, so that’s what this series is describing. Your mileage may vary.</p><p><strong>What Skyvia components will you need?<br></strong>We use all of Skyvia’s products for different needs, but if you’re just getting started, set up your trial by going to their <a href="https://skyvia.com/data-integration/replication">Replication</a> use case. You will need their Data Integration and Query products for this work (as of May 2023).</p><p>Once you have your trial, you can set up your replication.</p><h4><strong>Step 1: Create your Connection to Salesforce</strong></h4><p>A Connection is a reusable way in which you connect to different resources — your Salesforce instance and SQL database, in this case.</p><p><strong>From the top menu bar, Click +New then choose Connection</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/1*TkEDqsqEO8in_7_VhjVuNw.png" /><figcaption>How to start to create a new Connection in SkyVia — New then Connection</figcaption></figure><p><strong>In the list of connectors, search for Salesforce<br></strong>Then click on the “Salesforce” logo to go to your Salesforce connection setup screen.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yd-p-1HriFYH9GKUr1fNUQ.png" /><figcaption>Filter by typing “salesforce” into the box until you see the Salesforce logo. Then click it.</figcaption></figure><p>On the Connector Setup Screen:<br><strong>Click on the word “Untitled” </strong>to edit inline the name of your connector. Name it something descriptive, like “Salesforce Production Instance” or “Salesforce Demo Sandbox.”</p><p><strong>Choose whether you are connecting to Production or a Sandbox.</strong></p><p>Then click<strong> “Sign in with Salesforce” </strong>to authenticate. Note that you can — and probably should — authenticate with an API only or service user that has only the required permissions to perform a read on the objects you plan to store in your data warehouse, just as a best practice with stored accounts from any source.</p><p>Adjust the <strong>Metadata Cache</strong> if needed. This tells Skyvia how often to refresh the metadata (the objects and fields list) since it, like most products, minimizes the API calls to Salesforce by caching the metadata definitions itself. If you frequently add fields or objects, set the cache to be more frequent; if you are syncing slower-moving objects, leaving the cache to Infinite is fine, just note you will need to refresh the cache manually when you do make a change to Salesforce that Skyvia needs to know about.</p><p><strong>Test Connect then Create<br></strong>And you’re done with Connection #1 to your Salesforce org.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NlK7GVuGGYJ1n5sKIgKU6g.png" /><figcaption>Skyvia connector screen for Salesforce</figcaption></figure><h4>Step 2: Prepare your SQL Server instance to allow Skyvia to Connect</h4><p>Remember that your SQL Server will reject all attempts to connect that aren’t from a recognized IP address.</p><p>Log into your Azure portal</p><p>Find your list of resources and choose the database you created in <a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af">article 3 of this series</a>. Click on the one that says “SQL Database” (not SQL Server).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tuEljU_gs7gwyphAwQ_2SQ.png" /><figcaption>Click to get to the details of your SQL database</figcaption></figure><p>As with <a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af#fcaa">allowing your own personal IP to connect</a>, you now need to let Skyvia’s IPs connect.</p><p>Click on “Set Server Firewall”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D9AXbtniZAwWWSbL4us8hQ.png" /><figcaption>Set server firewall is in the top nav of your SQL Database page</figcaption></figure><p>Scroll a bit until you see “Firewall Rules” then click + Add Firewall Rules</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bkKtlkMbRgs8p0ZHp1HMzA.png" /><figcaption>Add a new row for a firewall rule</figcaption></figure><p>Give your rule a name (we usually use “Skyvia”) and in both the start and end IP addresses type in 52.190.252.0. Then hit “OK”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/972/1*dBYojbbAaovWpMD3DQTvhQ.png" /><figcaption>Your new Skyvia rule</figcaption></figure><p>Note that Skyvia, like any other cloud provider, occasionally needs to change its IP addresses so you will need to adjust this over time.</p><p><strong>NOTE: Your changes are not saved until you click “Save” at the bottom of this screen!</strong></p><h4>Step 3: Create your Connection to your SQL Server instance</h4><p>You will want to connect to the SQL Server database that you created in the <a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af">3rd article in this series</a>.</p><p>And specifically, <a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af#4244">you want to connect it to the SF schema user</a> (NOT your admin!).</p><p><strong>From the top menu bar, Click +New then choose Connection</strong></p><p><strong>In the list of connectors, search for SQL<br></strong>Then click on the “SQL Server” logo to go to your Azure SQL Server connection setup screen.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lDrXtnEUjJ2zA6D7nUePcw.png" /><figcaption>Filter for “SQL” then choose SQL Server</figcaption></figure><p>On the Connector Setup Screen:<br><strong>Click on the word “Untitled” </strong>to edit inline the name of your connector. Name it something descriptive, like “Data Warehouse SF Schema.”</p><p>In <strong>Connection Mode</strong>, choose <strong>Direct</strong>.</p><p>In <strong>Server</strong>, put the server name of the database server that you set up in the last article. It is the same one you used in <a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af#bb42">testing your connection</a> in Azure Data Studio. If you aren’t sure what it is you can go to your Azure portal, find your database, and see it in the main information bar. Do not prefix it with <a href="https://!/">https://!</a> Make it identical to what you see in Server Name on your Azure portal.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9BEeHB1Sguz6gz70nmweSg.png" /><figcaption>Where to find your database server name in the Azure portal</figcaption></figure><p>In <strong>User ID</strong>, put the user ID of the Salesforce Schema user you created. If you followed the demo exactly, this would be <em>SFSchemaUsr.</em></p><p>In <strong>Password</strong>, put the password of the SFSchemaUsr.</p><p>Click on <strong>Database. </strong>If you set up your firewall rules correctly (and remembered to save them) you will see a drop-down that shows the master db as well as your own database. Choose the database you set up.</p><p>Click Test Connection then Create Connection.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7z9gZnk-6xOHXuIg-4kcYw.png" /><figcaption>Your SQL Server connector should look similar to this — with your user ID filled in, of course.</figcaption></figure><h4>Step 4: Set up a Replication job</h4><p>Skyvia’s replication is a simple, efficient, and reliable “set it and forget it” solution that will:</p><ul><li>create the SQL tables for you</li><li>populate them with your Salesforce data</li><li>then at whatever frequency you prefer, perform incremental updates (add new records, update changed records, and delete records).</li></ul><p>Also note that when you first set up your sync, you may have more rows than your subscription limits, because you are first populating <em>all</em> records. Once you get into the incrementals it should drop to within your subscription levels. You will get charged a modest fee for going over your limits, so just be aware of the total number of records that you’re working with and plan accordingly.</p><p><strong>From the top menu bar, Click +New then choose Replication</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/970/1*DWwdEpW_yhzDE_bC104Bpg.png" /><figcaption>Set up a new Replication</figcaption></figure><p>On the Connector Setup Screen:<br><strong>Click on the word “Untitled” </strong>to edit inline the name of your connector. Name it something descriptive, like “Nightly Sync between SF and SQL.”</p><p>For <strong>Source</strong>, choose the Salesforce connector you set up in step 1.</p><p>For <strong>Target</strong>, choose the SQL Server connection you set up in step 2.</p><p>Leave<strong> Incremental Updates </strong>and<strong> Create Tables </strong>checked.</p><p>You may want to <strong>uncheck “Create foreign keys</strong>”. <br>Note that at this time we are still trying to solve a problem that is more of a SQL than Skyvia problem. We prefer to have SF lookup fields set as foreign keys in SQL. However this means that a delete will time out in larger/more complex data structures. We’re exploring whether indexing will solve the problem.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xs1y9BRhkAVmazaDjAVMVQ.png" /><figcaption>The left-hand side of the connector setup simply specifies source, target, and structure.</figcaption></figure><p>Once you have chosen your source data, the <strong>right-hand side</strong> of the screen will populate with your Salesforce schema.</p><p><strong>Check the boxes next to the objects</strong> you wish to replicate from Salesforce into SQL Server. Note that with large objects, the replication jobs can exceed their limits and you may need to break your replication into multiple parts depending on your specific use case.</p><p>To see what you have selected, click on “Show all” — this will toggle your view to “Show selected only.”</p><p>By default, all fields in the selected objects will be replicated. If you want to exclude certain fields, click the pencil icon next to your selected object and uncheck the fields that should not be replicated.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qgz5tNW-r2EnMIQK6Od50A.png" /><figcaption>In this example, we are only replicating Accounts and Contacts. You will have far more than this in your model.</figcaption></figure><p>Click “Validate” to ensure nothing is glaringly wrong with your replication setup. Then click “Create.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/398/1*X5t2yK81PjY6DQ7CkXNUQA.png" /><figcaption>When you first set up a replication, you have only Validate and Create.</figcaption></figure><p>Once you have created the package, you can run it. Note that, as the alert will tell you, this will likely take a long time. Skyvia does a good job of working in bulk and working to have as soft as possible an impact on Salesforce resource limits, so your initial sync may be faster than you think.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/384/1*QTfIl9pJcd1wKzN77Xocag.png" /><figcaption>The Run, Clone and Delete options appear after you Create.</figcaption></figure><p>You can monitor the job’s success on the Monitor tab. You should get messages that tell you what was successfully completed and what, if any, failures occurred.</p><p>If you get failure notifications, you will need to fix them and try again.</p><h4>Step 5: Set up the Schedule</h4><p>Once you have a successful first sync, all of your data will be in SQL Server, in the SF schema, in tables named the same thing as your Salesforce objects and with all fields created with their API names.</p><p>You’re ready to set this up to run on a schedule and know that data is flowing from SF to SQL Server regularly.</p><p>In your replication job, click on “<strong>Schedule</strong>”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/590/1*R-EYZs4Fc0E-jK5O1mswmw.png" /><figcaption>Set up the replication package’s schedule</figcaption></figure><p>A window will appear asking you to set up some basic scheduling parameters. You have already chosen incremental in the model’s settings, so this is just setting up when the solution will run.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RxmjlB-LW63Uw7AohH1_9w.png" /><figcaption>Fill in your preferred schedule then Save</figcaption></figure><p>Congratulations! You have set up a sync that will allow you to use SQL to work with your Salesforce data!</p><p>You can stop here if you want. That may be all you need if all you want to do is have a SQL database to work within.</p><p>Note that SQL Server by itself has no method to execute scripts on a schedule. You either need an Azure Automation account to create runbooks or you may be able to use Skyvia’s new (in 2023) Automation feature. The last article in this series will talk in general about how we approach these questions in case you need to take it that one step further.</p><h4>Step 6: Preparing for Ongoing Maintenance</h4><p>Salesforce lets you <a href="https://medium.com/@deepwhydesign/its-never-just-a-field-4c2154cf938">add objects and fields</a> so quickly that we often forget there are consequences and things to plan for in enterprise environments.</p><p>Now that you have connected your Salesforce to a data warehouse, you have to plan for ongoing maintenance.</p><p>Whenever you add a field to Salesforce, if the object is in your replication package and your metdata cache auto-refreshes, your replication may break because it has no corresponding field in SQL.</p><p>You must now:</p><ol><li>manually <a href="https://www.w3schools.com/sql/sql_alter.asp">add the new field</a> with the correct <a href="https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16">data type</a> to the SQL database</li><li>refresh the Skyvia metadata cache so it picks up the new field from Salesforce</li><li>confirm that your new field is checked in the Skyvia replication model (click the pencil to see all the fields, look for your new field, check the box if it’s unchecked).</li></ol><p>The hardest part is remembering to do these steps before your nightly sync job breaks on you. We recommend just adding these steps to your internal policies and procedures relating to Salesforce administration.</p><p>Let’s say you add My Custom Field (API name: My_Custom_Field__c) to the Contact object, and it’s a long text field. The Contact object is part of your nightly replication and is in the SF schema.</p><p>You would use Azure Data Studio to connect to your SQL server as an administrator. And you would execute the following SQL command:</p><blockquote>Alter table [SF].Contact add My_Custom_Field__c varchar;</blockquote><p>This will add a varchar field with the same name to your SQL table, so that Skyvia can add your new field’s data nightly.</p><h3>What’s in this series?</h3><p>You should read these in the order listed below.</p><ol><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-1-ca5cfad710b"><strong>Overview</strong></a> — introduces the series and outlines our example use case</li><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-2-the-ecosystem-365ec39bdd96"><strong>Salesforce and the data visualization ecosystem</strong></a> — this article, which explores the more conceptual aspects of data and reporting</li><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af"><strong>Setting up SQLServer as a Data Warehouse</strong></a><strong> </strong>— the steps to set up SQLServer in an Azure environment to meet the requirements of being “good enough” for this use case</li><li><strong>Setting up SkyVia to replicate your Salesforce data into SQLServer</strong> — the steps to use SkyVia to pull data from Salesforce into SQLServer on a regular basis</li><li><strong>Transforming data inside your SQLServer</strong> <strong>for visualization </strong>— the overview of why you’d have data in SQLServer in the first place, what your automation options are, and how you might use this thinking to support visualizations</li></ol><p><em>FAIR WARNING: YOU GET WHAT YOU PAY FOR WHENEVER DATA IS CONCERNED. Analysis on the cheap is going to be bad analysis and the more powerful the toolkits the more that can go wrong. I am focusing only on building an affordable data pipeline and storage solution, which are things that are likely in reach for a sufficiently technical Salesforce administrator.</em></p><p><em>If you read this series and feel despair at executing on this stuff yourself, reach out. We at </em><a href="https://deepwhydesign.com/"><em>Deep Why</em></a><em> focus on making your Salesforce ecosystem work really well, which can include getting it into an Azure data warehouse. Our friends at </em><a href="http://inciter.io/"><em>Inciter</em></a><em> specialize in helping you understand your data and making all of your reporting needs seamless and easy. We often work together, so if you’re not sure just shout and we’ll get you to the right team.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bcb8c0426e6c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Nonprofit Data Pipeline On the Cheap (Part 3): Azure Resources]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/a3d25dc1a2af</guid>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[nonprofit-technology]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[nonprofit-data]]></category>
            <category><![CDATA[salesforce-for-nonprofits]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Fri, 12 May 2023 17:50:27 GMT</pubDate>
            <atom:updated>2023-05-21T18:32:06.895Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tVXl7xn9K7CWuHOZk4sP9Q.png" /><figcaption>In this article, we are setting up the environment to allow the highlighted section — Azure SQL Server — Raw data from SF — to be populated.</figcaption></figure><p>For this part of the work, you will need to be able to create Azure resources. While this article is fairly long, it incorporates all of the foundational Azure-related components so we thought it best to keep it in one article.</p><p>This article will take you through:</p><ol><li><a href="#0656">Creating an Azure account</a> (if you don’t have one)</li><li><a href="#f9ff">Creating a subscription</a> for this work</li><li><a href="#aafd">Creating a resource group</a> for your database and automation account</li><li><a href="#25be">Creating an Azure SQL Server</a> that is sufficient for non-real-time warehouse processing</li><li><a href="#fcaa">Allowing your IP to connect</a> through the firewall</li><li><a href="#bb42">Testing your connection</a> with Azure Data Studio</li><li><a href="#4244">Setting up the schema</a> and permissions for the sync</li></ol><p>We’ll tackle the automation account in the 5th article of this series, since you may not need it to accomplish your goals.</p><p>You need to be very, very careful with each of these steps as Azure uses a utilization-based pricing structure and a mistake in setup can easily cost a lot more than you were expecting. For any full evaluation of your setup you should talk to a Microsoft expert, which I definitely am not.</p><p><strong>Proceed at your own risk. This works for me and my clients, it may all end in tears for you.</strong> If any part of this doesn’t work for you, I can’t help you troubleshoot it, sorry. This is provided purely for informational purposes so proceed with caution!</p><p>The people who usually do this work are spread across a few different disciplines. Setting up an Azure account and maybe even the database and firewalling would most often be your IT team who are going to be focused on infrastructure access, security, and cost. Creating the schema and users would fall to a database administrator, which is typically a completely different skillset than the IT team as it requires knowledge of SQL and an understanding of how databases are put together.</p><h4>Step 1: Set up an Azure account if you don’t have one</h4><p>You can start for <a href="https://azure.microsoft.com/en-us/free/">free</a> and if you work for a nonprofit, make sure you connect with an account exec to explore your grant options. You may be able to access some of these resources at lower cost.</p><h4><strong>Step 2: Create a Subscription if you don’t have one</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/490/1*gNaFN-8cHuK319jBz1HhMw.png" /><figcaption>Create a Subscription that you’re going to link all of your other resources to.</figcaption></figure><p>A subscription is sort of a cost container — it’s linked to a billing account (so you’ll need to create one of <em>those</em> if you don’t have one, though you were probably walked through that step when creating your new Azure account) and all of the things you’ll build out in this tutorial will be linked to the same Subscription.</p><p>I have never filled in the Advanced or Tag sections here — but I’m not a Microsoft expert. So maybe I’m missing something, but it hasn’t hurt me yet.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hj-UX--xIZ9B_2IJQv2FaA.png" /><figcaption>Create a subscription screenshot, with the billing account information blanked out.</figcaption></figure><h4>Step 3: Create a Resource Group if you don’t have one</h4><p>A resource group is a container within your subscription. Microsoft experts can explain it better than I can, but for this tutorial just know that you need one.</p><p>Find the Resource Group icon and click + to add one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/876/1*sMjUoUymtH_A9fKGrIJo7A.png" /><figcaption>The Resource Group icon</figcaption></figure><p>Choose the Subscription you created in step 2, and give your Resource Group a name. I have just named it the same thing as my Subscription for this demo.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5BAlI8rO9iNOzfticyyj1g.png" /><figcaption>Creating a Resource Group</figcaption></figure><h4>Step 4: Create a SQL Database Server</h4><p>Click the big + sign to create a resource, then choose “SQL Database.”</p><p><strong>Page 1: Basics<br></strong>Link it to the <strong>subscription</strong> and <strong>resource group</strong> you created in steps 2 and 3.</p><p>Give your database a <strong>name.</strong></p><p>You will be asked for the <strong>server</strong> — you should create this from this screen (image below).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s0P8PhrJDecV6HEPa5C67Q.png" /><figcaption>An example of the screen that creates the SQL Database Server, invoked from “Add New”</figcaption></figure><ul><li>Click “Add New”, give your server a name and choose how you will authenticate to this server.</li><li>I have frequently implemented these in settings that do not have Azure AD, so feel free to choose “Use SQL Authentication” if you also don’t use Azure AD. Just set a strong password and please do not choose “admin” as your username.</li><li>When you click “OK” you will be returned to the previous page to continue creating a SQL Server database, with your new server linked in the correct space.</li></ul><p>Choose NO for “<strong>Want to use SQL elastic pool?</strong>”</p><p>By <strong>Compute+Storage</strong>, click “Configure database”. You will be taken to a new screen to configure (image below).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1ji6YLDNr-WyS3rUAxLBKg.png" /><figcaption>The Service Tier that is quite sufficient for this use</figcaption></figure><ul><li>Under “Service Tier” look for DTU-Based, then choose “Standard” and take the defaults</li><li>Your Estimated Cost per Month should be very low, like under $25/mo low.</li><li>NOTE: You are choosing a less responsive, going-to-be-throttled storage because you don’t need anything else. You’re not doing real-time, responsive work from this system, but rather, you’re storing a bunch of mostly static data and some views, and using it to fuel your data visualizations.</li><li>Click “Apply” to return to the Database setup screen</li></ul><p>Choose whichever <strong>backup and redundancy</strong> setting is appropriate for you.</p><p>When you’re finished, your screen should look similar to the below and your estimated monthly cost should be quite low.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-c2-uVoR_BeoQgEwTDynVQ.png" /><figcaption>An example of a newly created SQL Database, first tab of configuration.</figcaption></figure><p><strong>Page 2: Networking<br></strong>By default, the database is firewalled off from the universe. We typically explicitly allow only specific IP addresses while we’re building and working with data until we are able to work with a technologist who can apply other types of Azure-based access controls. If that isn’t available, we stick with IP-based restrictions and just manage any changes manually.</p><p>On this page, we are going to leave the defaults as they are. Ultimately we will set up a private endpoint for the runbooks to use, so if you’re comfortable doing that go ahead here. Otherwise just leave the defaults.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F5btsTwYQX0ZHjb98nx0SQ.png" /><figcaption>An example of the Networking tab in a SQL Database setup</figcaption></figure><p><strong>Page 3: Security and Page 4: Additional Settings<br></strong>We typically take the defaults here, but you should consider which of these settings make sense for your environment. They’re not covered here.</p><p><strong>Page 5: Review and Create</strong></p><p>On this page, make sure you click “Create” in the bottom left of the screen or you will not actually create your database!</p><p>You will need to wait until your deployment is complete to attempt to connect, so now is a good time to download <a href="https://azure.microsoft.com/en-us/products/data-studio">Azure Data Studio</a> if you don’t already have it.</p><h4>Step 5: Allow your IP to Connect</h4><p>Once your SQL Server has deployed, you will get a screen letting you know, and a button to let you navigate to the resource. Click “Go to Resource.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*vqiR-xQWLVIZdypag321qw.png" /><figcaption>Click Go to Resource when your deployment is completed</figcaption></figure><p>You will be taken to your new SQL Server. Look for a row of tabs that includes “Set Server Firewall” and click on it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/910/1*7uneDWzJYf9oHHIiKI5tjQ.png" /><figcaption>Click on “Set server firewall”</figcaption></figure><p>On the Public Access tab, change the option from “Disable” to “Selected networks.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y2ZRqCKhNzScx8TC-k5rSQ.png" /><figcaption>Choose Selected Networks</figcaption></figure><p>CLICK SAVE BEFORE PROCEEDING! Otherwise you will get an error.</p><p>Under “Firewall rules” you should see a + with ‘Add your client IPV4 address” — click this to add your IP. Then click “Save.”</p><h4>Step 6: Confirm that You Can Connect</h4><p>We use the free <a href="https://azure.microsoft.com/en-us/products/data-studio">Azure Data Studio</a> to connect directly to the SQL Server database and perform basic tasks, like security setup.</p><p>Launch Azure Data Studio</p><p>Create a Connection — in my Azure Data Studio, it’s a convenient button on my home screen. If you don’t see that, it’s also visible in the Connections menu in the left nav.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/970/1*pb-DxXa2_YWj1tgQY-AQMA.png" /><figcaption>Create a new connection in Azure Data Studio from the left nav</figcaption></figure><ol><li><strong>Connection Type</strong>: Microsoft SQL Server</li><li>Connect with <strong>Parameters</strong></li><li><strong>Server</strong>: the server you created at the beginning of step 4 when you created the SQL Database Server. It should be something very similar to [yourservername].database.windows.net</li><li><strong>Authentication Type</strong>: SQL Login (unless you chose a different structure in step 4)</li><li><strong>Username and Password</strong>: the ones you set up in step 4 when you created the SQL Database Server.</li><li>It’s up to your organization’s policies whether you remember the password or aren’t allowed to do so.</li><li><strong>Database</strong>: click the dropdown to select your database. Azure Data Studio will use the credentials you entered to attempt to connect to your database server, so you may see “Loading…” for a bit. If it doesn’t find your database, go back into portal.azure.com and verify your firewall entry.</li><li><strong>Encrypt</strong>: Mandatory (True)</li><li><strong>Trust Server Certificate</strong>: False</li><li><strong>Server Group</strong>: &lt;Default&gt;</li><li><strong>Name</strong>: Give this a name so you know from your connections list what you’re connecting to in the future.</li><li>Click connect.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/974/1*StKvruDl0bX_xS4YUC953A.png" /><figcaption>An example connection screen for Azure Data Studio</figcaption></figure><h4>Step 7: Set up a schema and user for your Salesforce sync</h4><p>There are many different approaches and philosophies that guide how you might want to design your warehouse. We use a very simple approach that is intended to work well with Skyvia’s replication packages and allows you to replicate multiple Salesforce orgs into the same database without any trouble.</p><p>We create a separate schema for each source database. In this series, we’re only connecting a single source (Salesforce), but in reality you likely wouldn’t go through all of this setup without wanting to connect multiple sources.</p><p>We also create a separate user for each schema.</p><p><strong>For all of this work, you will be executing SQL commands in Azure Data Studio.</strong> There are, apparently, different ways of doing this but I find this clear and it allows me to store the scripts that I used so I can easily rebuild if needed. After many years with technology, I <em>always</em> save my scripts.</p><p>To open up a SQL compose window, just right-click on the connection (which will bring up a contextual menu) and choose “New Query.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/662/1*o2S4ENyMkRpaZJCoV_-Oqw.png" /><figcaption>The contextual menu in your Azure Data Studio</figcaption></figure><p><strong>Query 1: Create the schema<br></strong>The commands to do this work are quite simple but it can be confusing to know exactly what to do, in exactly which steps, so we’re listing them here.</p><p>In your query editor, type in the following. Then click Run.</p><blockquote>create SCHEMA SF;<br>GO</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C-mhmEkooTM3eYQYg4IwVA.png" /><figcaption>The query composer screen, with the command and an indicator of where the Run button is.</figcaption></figure><p>Very shortly after you hit run, a Messages pane should open below your SQL compose window and it should say</p><p>“Commands completed successfully.<br>Total execution time: ….”</p><p>Once you get the “Commands completed successfully” you can move to the next step.</p><p><em>Explainer: </em>a schema is a collection of tables that can operate semi-independently in a database. It’s helpful to have different schema for different data sources for a number of reasons that are beyond the scope of this article to cover.</p><p><strong>Query 2: Create the database users</strong> <br>You will need to <strong>change the connection</strong> to ‘master’ before you take the next step!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0v7_74iU9HLgRDQXYpE4Pw.png" /><figcaption>You cannot take the next step until you click on the down arrow and choose “master”</figcaption></figure><p>In your query editor, type in the following. <strong>CHANGE ReplaceWithYourPwd! </strong>You are setting a password for this user, so generate a strong password, put it in a safe place, and only leave it in this script long enough to run it. Don’t store this script with your actual password in plain text!!</p><blockquote>CREATE LOGIN SFSchemaUsr WITH password=’ReplaceWithYourPwd’;<br>GO<br>CREATE USER SFSchemaUsr FOR LOGIN SFSchemaUsr WITH DEFAULT_SCHEMA=[SF];<br>GO</blockquote><p>Then click Run.</p><p><em>Explainer</em>: you are creating a new user and password in the first statement. This is the username and password you will put into Skyvia when you create the database connection (article 4 will go through these steps) and you can also use this username and password in Azure Data Studio to connect and create queries. Yes, it’s called “login” and yes, it’s distinct from “user” but that’s just the way these systems are set up.</p><p>The second query creates the user that is linked to the login and assigns that user the default schema that you set up in query 1.</p><p><strong>Query 3: Create the schema users with the right permissions<br></strong>You will need to <strong>change the connection back </strong>to your database before you take the next step!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F61QBQg8A_8XaLRixjZ5Ag.png" /><figcaption>You cannot take the next step until you click on the down arrow and choose your database</figcaption></figure><p><strong>Take note</strong>: this step links the login you created above with rights to modify your database. I have granted a somewhat extensive set of permissions to this user for this schema because your Skyvia replication needs to be able to create tables and perform all data operations in those tables. You will want to understand what each of these permissions does and assess whether the permissions are right for your use case.</p><blockquote>CREATE USER SFSchemaUsr FOR LOGIN SFSchemaUsr WITH DEFAULT_SCHEMA=[SF]<br>EXEC sp_addrolemember ‘db_datareader’, ‘SFSchemaUsr’;<br>GO</blockquote><blockquote>EXEC sp_addrolemember ‘db_datawriter’, ‘SFSchemaUsr’;<br>GO</blockquote><blockquote>GRANT EXECUTE, ALTER, INSERT, REFERENCES, DELETE ON SCHEMA :: SF TO SFSchemaUsr;<br>GO</blockquote><blockquote>grant CREATE TABLE to SFSchemaUsr;<br>GO</blockquote><p><em>Explainer: </em>The first query makes the user yet again, but this time in the target database (rather than the master database). And yes, it’s linking it again to the schema, because you have to specify these things at each step. The remaining queries are granting permissions required for this new user to operate within its schema within this database so that Skyvia has the correct permissions to replicate your Salesforce data.</p><p><strong><em>Summary</em></strong><em>:</em> <br>For every data source you want to connect to this warehouse, you would create a new and different schema (query 1), then create new and different logins and users in the master database (query 2), then create and assign the right permissions to those new and different logins and users in the target database (query 3).</p><p>For example, if you want to bring in your ADP data, you would repeat queries 1–3, but replacing “SF” with “ADP” so that you have:</p><blockquote>create SCHEMA ADP;</blockquote><p>and in query 2</p><blockquote>CREATE LOGIN ADPSchemaUsr WITH password=’ReplaceWithYourPwd’;<br>GO<br>CREATE USER ADPSchemaUsr FOR LOGIN ADPSchemaUsr WITH DEFAULT_SCHEMA=[ADP];</blockquote><p>and so on.</p><h3>Step 8: Take a deep breath!</h3><p>You got through the hard part. The rest of it is easy from here on out. Congrats!</p><h3>What’s in this series?</h3><p>You should read these in the order listed below.</p><ol><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-1-ca5cfad710b"><strong>Overview</strong></a> — introduces the series and outlines our example use case</li><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-2-the-ecosystem-365ec39bdd96"><strong>Salesforce and the data visualization ecosystem</strong></a> — this article, which explores the more conceptual aspects of data and reporting</li><li><strong>Setting up SQLServer as a Data Warehouse </strong>— the steps to set up SQLServer in an Azure environment to meet the requirements of being “good enough” for this use case</li><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-skyvia-part-4-bcb8c0426e6c"><strong>Setting up SkyVia to replicate your Salesforce data into SQLServer</strong></a> — the steps to use SkyVia to pull data from Salesforce into SQLServer on a regular basis</li><li><strong>Transforming data inside your SQLServer</strong> <strong>for visualization </strong>— the overview of why you’d have data in SQLServer in the first place, what your automation options are, and how you might use this thinking to support visualizations</li></ol><p><em>FAIR WARNING: YOU GET WHAT YOU PAY FOR WHENEVER DATA IS CONCERNED. Analysis on the cheap is going to be bad analysis and the more powerful the toolkits the more that can go wrong. I am focusing only on building an affordable data pipeline and storage solution, which are things that are likely in reach for a sufficiently technical Salesforce administrator.</em></p><p><em>If you read this series and feel despair at executing on this stuff yourself, reach out. We at </em><a href="https://deepwhydesign.com/"><em>Deep Why</em></a><em> focus on making your Salesforce ecosystem work really well, which can include getting it into an Azure data warehouse. Our friends at </em><a href="http://inciter.io/"><em>Inciter</em></a><em> specialize in helping you understand your data and making all of your reporting needs seamless and easy. We often work together, so if you’re not sure just shout and we’ll get you to the right team.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a3d25dc1a2af" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Literacy for Nonprofits Part 5— Buckets of Reporting]]></title>
            <link>https://deepwhydesign.medium.com/data-literacy-for-nonprofits-part-5-buckets-of-reporting-9cb1da2fcb91?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/9cb1da2fcb91</guid>
            <category><![CDATA[nonprofit-leadership]]></category>
            <category><![CDATA[nonprofit-technology]]></category>
            <category><![CDATA[data-literacy]]></category>
            <category><![CDATA[nonprofit-data]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Wed, 10 May 2023 20:37:36 GMT</pubDate>
            <atom:updated>2023-05-10T20:37:36.941Z</atom:updated>
            <content:encoded><![CDATA[<h4>Data has different flavors. Sadly, one of them is not strawberry.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/847/1*0R0Z9K-JEkWiGk8dhpUyog.jpeg" /><figcaption>Data isn’t all the same, so don’t fall into the trap of comparing apples to oranges.</figcaption></figure><p><em>This article shares a lot of content with our </em><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-2-the-ecosystem-365ec39bdd96"><em>Salesforce Obscura: Data Pipeline on the Cheap</em></a><em> article. We’ve reused the relevant bits for this series, because understanding that reporting falls into different categories is the last piece in truly using your data literacy superpowers to drive your organization forward.</em></p><p>Now you know that <a href="https://deepwhydesign.medium.com/practical-data-for-nonprofits-part-2-all-data-comes-from-somewhere-b6e9675042a1">all data comes from somewhere</a> and has hidden costs you need to be aware of. You’ve taken a <a href="https://deepwhydesign.medium.com/practical-data-for-nonprofits-part-3-why-is-it-so-hard-to-change-something-in-my-database-eed149ca8d0e">look at the stack</a> that drives all things on all computers, and you have stared into the void and <a href="https://deepwhydesign.medium.com/data-literacy-for-nonprofits-part-4-data-is-meaningless-2e72ff9a1f44">found meaning in your data</a>. Now it’s time to bring it all together and actually get those reports you wanted when you started on this journey.</p><p>But wait — what KIND of reports?</p><p>You will be a lot less frustrated with your reporting if you understand that in the nonprofit sector, reports actually fall into four big buckets:</p><ol><li><strong>Operational or transactional </strong>— usually listmaking to help people get their jobs done</li><li><strong>Managerial or performance monitoring</strong> — to help keep the day to day on track</li><li><strong>Outcomes</strong> — the stuff you report to funders and the board</li><li><strong>Predictive</strong> — trying to get out ahead of things by using data to look ahead, rather than looking back</li></ol><p>And to use each of these well, it’s helpful to know that you ask different questions from different types of skillsets or software depending on what you’re asking for.</p><p>The diagram below is for Salesforce because that is <a href="https://deepwhydesign.com/">Deep Why</a>’s expertise, but you can substitute any operational system into this diagram and it will probably be accurate. If it’s on a computer, and people use it primarily to do their day to day work (or key in the results of their day to day work), it’s an operational data store and belongs in the blue part of the diagram.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*mpYiNvQcKZM34sOD.png" /><figcaption>A diagram showing the 3 main pieces of a Salesforce data ecosystem — Salesforce in blue, a warehouse in white, and analysis tools in yellow.</figcaption></figure><h3>Report Bucket 1: Operational or Transactional Reports</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*39TxeaudNZXy2ofr.png" /></figure><p>Operational or Transactional reports live in your operational data systems and are managed by the admins of that system — usually relatively expert users rather than deeply technical people, but sometimes a business analyst role who spans the gap between process and technology.</p><p>Operational (or Transactional) reports are typically “how many / how much” and “right now” type reports that show things like “how much money have we raised this year so far” or “how many participants are on each person’s case load right now.”</p><p>These belong as close to your operational data solution as possible, and typically are very well supported within these solutions. Products that support specific business processes (like accounting systems and some fundraising systems) will have pre-built reports that are designed to support real-time use of these systems; platforms like Salesforce will support someone in building those types of reports.</p><p>In the diagram above, they’re in blue. And they’re usually created and managed by end users, data managers, or business analysts.</p><h3>Report Bucket 2: Managerial or Performance Monitoring Reports</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LBboPyR4U8-jelhh.png" /><figcaption>An example of a Salesforce-delivered dashboard that could be used to monitor across staff for performance-related questions. This screenshot is for a sample organization with fake data.</figcaption></figure><p>Managerial (or performance monitoring) reports are typically KPI-style reports that grow out of operational data and help managers keep an eye on things. They are answering questions like “are we on track to raise what we’re supposed to” and “are all of my staff doing what they’re supposed to do with our participants.”</p><p>For example, if your program helps people obtain stable housing, and you know that there are typically 10–15 interactions with the housing agency required for this to occur, a managerial or performance report might:</p><ul><li>look at how many interactions a caseworker has had on behalf of a participant, or</li><li>ask how many participants have fewer than 10 interactions after 2 months in the program.</li></ul><p>In the diagram above, these reports may live in your operational data store (blue) or they may be incorporated into more structured data visualizations (yellow). Depending on which area they come from, they may be managed by business analysts or by a data and analysis role.</p><h3>Report Bucket 3: Outcomes Reports</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5xq7zuI-Owyvtwjt.png" /></figure><p>Outcomes reporting lives firmly in the analytics side of the house, usually from a data model that is fed by your operational data stores — the systems where you do your day-to-day fundraising, accounting, and program delivery work.</p><p>Outcomes are typically end of year, “look back” type reports. These are the things you report to funders and use to look across your organization’s performance year on year. They are aligned to your logic model and theory of change.</p><p>They are typically carefully defined and curated and often relatively slow to produce. They usually require a lot of processing to turn “how many/how much” data into real indicators of impact. They are often some of the oldest reports in an organization.</p><p>A change to outcomes reporting ripples throughout an entire organization and should involve many people. Most organizations’ activities are aligned around their outcomes reporting, so changing an outcome means you have to assess what front-line staff are doing, what needs to change for the new outcome to be reportable, what needs to change for training and management, and so on. It is and should be a Very Big Deal.</p><p>Outcomes reporting typically resides in the analytics (yellow) part of the diagram above and is often at least one step removed from living, transactional data.</p><h3>Report Bucket 4: Predictive or Leading Indicator Reporting</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5hkyarQw1yUMl3fc.png" /></figure><p>Predictive reporting uses additional tools that can handle the statistical and mathematical modeling required to ask questions about which data might impact which other data.</p><p>Predictive reports are computations that run against real-time or near-real-time data to help you see if you’re on track for your outcomes or not.</p><p>An outcome might be “ensured 60% of our clients moved from unstable to stable housing in a year.” The operational data that drives the outcome might simply be a couple of fields that are “started the year in unstable housing” and “ended the year in stable housing.”</p><p>But you don’t know until the end of the year how you did, and that’s way too late. Some organizations can use their managerial reporting to keep an eye on what they might expect in outcomes. Others want to incorporate statistical analysis and, increasingly, AI to comb through every data element they capture for patterns that humans may overlook.</p><p>This work lives very firmly in the Analysis (yellow) part of the diagram and is owned by people who really understand data analysis and data modeling, statistical validity, and other mathematical pieces and parts that require a lot of infrastructure before their work can begin.</p><p>Most of the organizations I work with don’t get into predictive analysis simply due to cost and complexity, but we’re seeing it more and more and it does have an impact. Know that it is a significant investment and it comes <em>after</em> you already have solid transactional, managerial and outcomes reporting.</p><h3>What’s with the “ETL” and “Data Model” part of this diagram?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0S28pQST0u2480Iv.png" /></figure><p>The middle bit of the diagram, highlighting ETL, additional storage, and data models.</p><p>A <strong>data model</strong> is nothing more than the way the data fits together so that it can be analyzed correctly. Your transactional data store might have the data spread across many different objects (tables) — think about it like lots of different tabs in a spreadsheet.</p><p>When you want to analyze it, you need to understand how all those pieces fit together. Sometimes you need to change it from one format to another — to “flatten” it (for example) so that you have one row per person with many columns representing activities. That has to be done with programming or in a query language like SQL. Data visualization tools also have a range of ways to construct a data model that can be used for dashboards and reports.</p><p>The data as presented in a structure optimized for your visualizations is a <strong>data set</strong>. This might be the one spreadsheet tab that you’ve created by combining all the others so that you can pivot it how you want. Or it might be a database table or view or any number of other things.</p><p><strong>ETL</strong> means “Extract / Transform / Load.” Whenever you have to move data around between systems, you’re doing a type of ETL. Even downloading a csv (“extract”) and manipulating it in Excel (“transform”) can fit this pattern.</p><p>“Possible Additional Storage” — sometimes you don’t need anything other than your operational data store, but sometimes there’s value in having several different sources of data pooled together into a single, separate system (like a data warehouse).</p><h4><strong>So what?</strong></h4><p>As you ask more complex questions of your data, you may run into some of these terms so it’s helpful to know what they mean. You will run into a range of roles or job descriptions and ways to approach reporting and analytics, and having a handle on some of the key concepts can make your reporting asks a lot easier.</p><p>There are a range of different labels applied to the things covered in this article. The key for data literacy in nonprofits is knowing that data is produced and meant to be consumed differently, for different purposes, and that different skills may be required to answer your reporting questions depending on which category they fall into.</p><h3>Congrats! You made it!</h3><p>I hope you’ve learned a lot about data in nonprofit organizations. Maybe you’ve picked up some new ideas about the kinds of questions you can ask from the systems you’ve got, or learned a bit about why it can be so difficult to adapt a database to your ever-changing organizational needs. Maybe you’ve gained a new appreciation for why your data team can be so annoying when you ask a simple question.</p><p>You made it this far, and we really appreciate you reading. Share your experiences and insights in the comments and happy nerding out!</p><p><em>This article is part of a series on data literacy for nonprofit leaders. Its goal is to share terms and concepts that aid in making good technology decisions when you’re not a technology expert (or even if you’re a little bit tech-phobic).</em></p><p><em>See the </em><a href="https://deepwhydesign.medium.com/practical-data-for-nonprofits-part-1-a-primer-2d063be48827"><em>overview</em></a><em> here</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9cb1da2fcb91" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Nonprofit Data Pipeline On the Cheap (Part 2): The Ecosystem]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-2-the-ecosystem-365ec39bdd96?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/365ec39bdd96</guid>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[salesforce-for-nonprofits]]></category>
            <category><![CDATA[nonprofit-technology]]></category>
            <category><![CDATA[nonprofit-data]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Wed, 10 May 2023 12:42:23 GMT</pubDate>
            <atom:updated>2023-05-21T18:29:01.437Z</atom:updated>
            <content:encoded><![CDATA[<p>This article is the only one in the series that is largely conceptual and focuses on framing up the solutions. It’s also part of our <a href="https://medium.com/@deepwhydesign/practical-data-for-nonprofits-part-1-a-primer-2d063be48827">data literacy</a> project as it talks about the different types of reporting and how to think about reporting ecosystems.</p><p>I don’t ever like to introduce tools without talking about when you should (and shouldn’t) consider them and framing up why we make the decisions we make at <a href="https://deepwhydesign.com/">Deep Why</a>.</p><p>So if you’re not a technical person, but are instead looking to understand more about reports in a Salesforce ecosystem, read on!</p><h3>Buckets of Reporting</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m_JzH73fkLOT3L-3r7Q0cg.png" /><figcaption>A diagram showing the 3 main pieces of a Salesforce data ecosystem — Salesforce in blue, a warehouse in white, and analysis tools in yellow.</figcaption></figure><p>Many organizations who use Salesforce rely solely on the reporting and dashboarding tools that come with it, and those tools can take you a really long way. A skilled admin or architect also understands the interventions that let you go even further with Salesforce reports (see “when do you look beyond Salesforce” below for examples).</p><p>But at some point, if you’ve gotten so many Flows or triggers or other automations to move data around and optimize for reporting and you <em>still </em>can’t get the reports you need, your organization may have outgrown the native tools, powerful as they are.</p><p>When we’re working with clients on data and reporting needs, we try to frame our questions by breaking reporting down into <strong>4 big buckets, </strong>being very clear about which bucket a request falls into, and being very strict about the processes that can impact those buckets.</p><p>We love Salesforce because it’s fast and responsive and nonprofits live in a world that is impossibly demanding of rapid change. Sometimes, with data, you need to be able to respond quickly. But you also need to be very clear when a fast change is not appropriate or is outright harmful. Framing your questions within the buckets helps an organization shift out of data confusion and into a more useful enterprise data mindset.</p><h4><strong>Report Bucket 1: Operational or Transactional Reports</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*A6Y-oahDzTQWeox-MG-bEQ.png" /><figcaption>Operational or Transactional reports live in Salesforce and are managed by Salesforce admins</figcaption></figure><p>Operational (or Transactional) reports are typically “how many / how much” and “right now” type reports that show things like “how much money have we raised this year so far” or “how many participants are on each person’s case load right now.”</p><p>These belong as close to your operational data solution (Salesforce) as possible, and typically are very well supported within Salesforce. It’s an immediate response to data someone is keying in in real time.</p><p>In the diagram above, they’re in blue. And they’re usually created and managed by end users, Salesforce data users, or Salesforce systems administrators.</p><h4>Report Bucket 2: <strong>Managerial or Performance Monitoring Reports</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YJ4-BhVry91cc5E_wGmSZg.png" /><figcaption>An example of a Salesforce-delivered dashboard that could be used to monitor across staff for performance-related questions. This screenshot is for a sample organization with fake data.</figcaption></figure><p>Managerial (or performance monitoring) reports are typically KPI-style reports that grow out of operational data and help managers keep an eye on things. They are answering questions like “are we on track to raise what we’re supposed to” and “are all of my staff doing what they’re supposed to do with our participants.”</p><p>For example, if your program helps people obtain stable housing, and you know that there are typically 10–15 interactions with the housing agency required for this to occur, a managerial or performance report might:</p><ul><li>look at how many interactions a caseworker has had on behalf of a participant, or</li><li>ask how many participants have fewer than 10 interactions after 2 months in the program.</li></ul><p>In the diagram above, these reports may live in Salesforce (blue) or they may be incorporated into more structured data visualizations (yellow). Depending on which area they come from, they may be managed by Salesforce administrators or by a data and analysis role.</p><h4>Report Bucket 3: <strong>Outcomes Reports</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*79GtY-sSLZMNos4f3t7vpQ.png" /><figcaption>Outcomes reporting lives firmly in the analytics side of the house, usually from a data model that is fed by Salesforce (among, possibly, other sources).</figcaption></figure><p>Outcomes are typically end of year, “look back” type reports. These are the things you report to funders and use to look across your organization’s performance year on year. They are aligned to your logic model and theory of change.</p><p>They are typically carefully defined and curated and often relatively slow to produce. They usually require a lot of processing to turn “how many/how much” data into real indicators of impact. They are often some of the oldest reports in an organization.</p><p>A change to outcomes reporting ripples throughout an entire organization and should involve many people. Most organizations’ activities are aligned around their outcomes reporting, so changing an outcome means you have to assess what front-line staff are doing, what needs to change for the new outcome to be reportable, what needs to change for training and management, and so on. It is and should be a Very Big Deal.</p><p>Outcomes reporting typically resides in the analytics (yellow) part of the diagram above and is often at least one step removed from living Salesforce data.</p><h4>Report Bucket 4: <strong>Predictive or Leading Indicator Reporting</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y45p4IhLDVRQB3yAhe2mIQ.png" /><figcaption>Predictive reporting uses additional tools that can handle the statistical and mathematical modelling required to ask questions about which data might impact which other data.</figcaption></figure><p>Predictive reports are computations that run against real-time or near-real-time data to help you see if you’re on track for your outcomes or not.</p><p>An outcome might be “ensured 60% of our clients moved from unstable to stable housing in a year.” The operational data that drives the outcome might simply be a couple of fields that are “started the year in unstable housing” and “ended the year in stable housing.”</p><p>But you don’t know until the end of the year how you did, and that’s way too late. Some organizations can use their managerial reporting to keep an eye on what they might expect in outcomes. Others want to incorporate statistical analysis and, increasingly, AI to comb through every data element they capture for patterns that humans may overlook.</p><p>This work lives very firmly in the Analysis (yellow) part of the diagram and is owned by people who really understand data analysis and data modeling, statistical validity, and other mathematical pieces and parts that require a lot of infrastructure before their work can begin.</p><p>Most of the organizations I work with don’t get into predictive analysis simply due to cost and complexity. Part of the reason I am writing this series is to try to find ways to reduce reporting infrastructure costs and burdens so that organizations can spend more time making meaning out of data and less time fighting inefficient tools that get in the way of meaning making.</p><h4>What’s with the “ETL” and “Data Model” part of this diagram?</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UBlHid53-zdrmBtlIGlq-Q.png" /><figcaption>The middle bit of the diagram, highlighting ETL, additional storage, and data models.</figcaption></figure><p>A <strong>data model</strong> is nothing more than the way the data fits together so that it can be analyzed correctly. Your transactional data store might have the data spread across many different objects (tables) — think about it like lots of different tabs in a spreadsheet.</p><p>When you want to analyze it, you need to understand how all those pieces fit together. Sometimes you need to change it from one format to another — to “flatten” it (for example) so that you have one row per person with many columns representing activities. That has to be done with programming or in a query language like SQL. Data visualization tools also have a range of ways to construct a data model that can be used for dashboards and reports.</p><p>The data as presented in a structure optimized for your visualizations is a <strong>data set</strong>. This might be the one spreadsheet tab that you’ve created by combining all the others so that you can pivot it how you want. Or it might be a database table or view or any number of other things.</p><p><strong>ETL</strong> means “Extract / Transform / Load.” Whenever you have to move data around between systems, you’re doing a type of ETL. Even downloading a csv (“extract”) and manipulating it in Excel (“transform”) can fit this pattern.</p><p>“Possible Additional Storage” — sometimes you don’t need anything other than Salesforce, but sometimes there’s value in having another system that operates differently than Salesforce and provides the structures available in a SQL database or some other data storage approach.</p><p>In this series of articles, we are using Azure SQL Server as the additional storage and Skyvia as the ETL.</p><h3>When do you look beyond Salesforce reports and dashboards?</h3><p>Every organization is different on this answer. For some, the answer is “never.” For some, it’s “immediately.” Frankly most just don’t know.</p><p>Because we work with small and midsized nonprofits, our rule is: <strong>only after you’ve exhausted every other reasonable approach and can articulate needs that clearly would incur massive technical debt inside of Salesforce to accommodate</strong>.</p><p>If you have Salesforce you hopefully have a Salesforce admin, so wherever possible we try to keep solutions within the skillset of that admin. Moving into the kinds of analysis and reporting structure we’re talking about in this series means adding yet another ongoing expense, a governance and change management requirement, and a need for new skillsets. That’s worth it when you really need that infrastructure, but is just an added strain until you do.</p><h4><strong>What does “exhausting every other reasonable approach” look like?</strong></h4><p>Salesforce gives you a very powerful toolkit. Be warned — there’s a fine line between exhausting all approaches and <a href="https://medium.com/@deepwhydesign/salesforce-obscura-stop-put-that-process-builder-down-9a16d34f53dc">amassing technical debt</a>. We still err on the side of starting with approaches familiar to Salesforce administrators. This allows you to onboard a new admin or consultant more easily than when you move into a more complex environment.</p><p><strong>(1) Making full use of the architectural tools</strong></p><ul><li><strong>Aggregating</strong> data from child to parent objects using rollups or DLRS</li><li><strong>Cross-linking objects </strong>— for example, if you have a Contact object with many Interventions but you only ever need to report on the most recent Intervention, you would add a lookup on Contact to Intervention (called “Most Recent Intervention” most likely), set up a Flow to populate that field, and voila, you have access to the data you need.</li><li>Making thoughtful and thorough use of <strong>report types and fields</strong> linked via lookups. For example, needing a “flatter” view from an object two or three levels deep in the architecture, you might build a custom report type that shows only one row per your child object with all the fields from its parent objects.</li></ul><p><strong>(2) Making full use of Salesforce automation tools</strong></p><ul><li>Remembering that <strong>reporting snapshots</strong> are super useful in the right circumstances. Are you struggling to answer stuff that is a “point in time” question — like, at this time last month what did our active enrollments look like compared to today? You can use reporting snapshots to help answer that.</li><li><strong>Using Flows</strong> to move data around for better reporting. Just because it’s easier for your users to store data in one way doesn’t mean you’re stuck with that data structure for reporting — could a Flow help you move data behind the scenes to make a better report?</li><li><strong>Assessing if another Salesforce-native tool answers the questions</strong> you’re asking. Apsona is frequently used as the “next step” reporting tool, for example, and it and similar tools should not be discounted if they can solve the problem. Total cost of ownership can and should be compared between Salesforce-native tools and the approach documented in this series.</li></ul><p><strong>(3) Looking to process and partnership before technology</strong></p><ul><li><strong>Think critically in partnership with the requesting user about reporting requests</strong>. As technologists, we’re inclined to solve problems with technology. Partnering with users to <a href="https://medium.com/@deepwhydesign/its-never-just-a-field-4c2154cf938">really investigate</a> what they’re trying to solve may help you point out better and more useful ways of answering the question.</li></ul><h3>What might some unsolvable problems look like?</h3><p>Some examples of things I’ve run into as a Salesforce solutions architect and someone with an extensive SQL / database background. Your mileage may vary.</p><h4>(1) The Dreaded Hinge</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1022/1*4y8lPmR8KdNgjGwFXOtUmw.png" /><figcaption>Contact is the “pin” that links together the two equal sides of Referrals and Incidents</figcaption></figure><p>Providing meaningful reporting on two or more peer objects that are related through a single, shared parent in some many-to-many fashion. The architecture is in the shape of a door hinge, with the parent object being the key piece holding the other pieces together. I hate this problem so much I named it “the dreaded hinge.”</p><p>If you don’t need data off of both objects, and can roll up a count from one object to the parent, you can answer these questions in Salesforce. Otherwise, not so much.</p><p>When rollups and cross filtering fail, it’s the Dreaded Hinge problem.</p><h4>(2) Show me ONLY the people who have (count/sum) for (arbitrary thing)</h4><p>Everybody wants to do things like sum(amount) and then filter on the sum. Show me ONLY everybody who has donated more than $500 in some arbitrary time period not already summarized by the NPSP. Show me ONLY people who have 3 or more registrations for this one summer camp.</p><p>For folks who expect to be able to do <em>any arbitrary analysis</em> on known data points, this just doesn’t work.</p><p>In SQL or a programming langauge this is another trivial problem to solve. But you’d have to resort to programming to get that answer in Salesforce, which doesn’t result in what people need or want for many types of questions they need to ask.</p><h4>(3) Anything that requires using the results of complex architecture mapping, basically</h4><p>In Salesforce reporting tools, you can use all the architectural, automation and report type tricks to emulate a lot of stuff you’d have in a SQL engine, but you are never actually accessing the same concept as when you express, in SQL:</p><p><em>select a.field1, b.field2, c.field3<br>from a<br>left join b on a.id = b.id<br>left join c on a.id = c.id<br>where a.Id in (select d.id from d where d.startDate &gt; today)</em></p><p>To truly do this in Salesforce, you would have to write an Apex script. You would first select what you want from object a and store it in a list; then in object b and store <em>that</em> in another list; then in object c into a final list. Then you would create a map to show the outputs you want, and iterate through your lists programmatically to identify which records belong in your map. Then you would do whatever it is you’re going to do with your map.</p><p>So while you can mimic many things a SQL database might do, the underlying translation that is happening in SQL (or that you engage with directly as a programmer) is not something Salesforce provides you in its native reporting toolkit. So sometimes, you just have problems you can’t solve, or you can only sort-of solve them in ways that require further processing.</p><h4>(4) You need to combine data from many data sources</h4><p>Not unsolvable in Salesforce, but many times it’s just easier to use the right tool for the job. Old school data warehouses and all of their successors all boil down to putting a bunch of unrelated stuff in the same place where it’s easy to link it all together to make meaning out of it. And SQL provides things like views and stored procedures that let you transform that data a lot more easily than having to do it all in Salesforce.</p><h3>OK, we really do need SQL too. What’s next?</h3><p>In the next article we’re going to shift away from the conceptual and into step-by-step guides. It definitely helps if you have some Azure or SQL experience but it isn’t necessary. You do need to be in a technical role in your organization to do the next steps.</p><p>First we set up the Azure environment that will become the data warehouse, then we use SkyVia to transfer data every day into the Azure environment, and finally we talk a bit about transforming data inside of SQL to create the “data set” part of the diagram that ultimately feeds into powerful visualizations.</p><h3>What’s in this series?</h3><p>You should read these in the order listed below.</p><ol><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-1-ca5cfad710b"><strong>Overview</strong></a> — introduces the series and outlines our example use case</li><li><strong>Salesforce and the data visualization ecosystem</strong> — this article, which explores the more conceptual aspects of data and reporting</li><li><a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af"><strong>Setting up SQLServer as a Data Warehouse </strong></a>— the steps to set up SQLServer in an Azure environment to meet the requirements of being “good enough” for this use case</li><li><a href="https://medium.com/@deepwhydesign/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-skyvia-part-4-bcb8c0426e6c"><strong>Setting up SkyVia to replicate your Salesforce data into SQLServer</strong> </a>— the steps to use SkyVia to pull data from Salesforce into SQLServer on a regular basis</li><li><strong>Transforming data inside your SQLServer</strong> <strong>for visualization </strong>— the overview of why you’d have data in SQLServer in the first place, what your automation options are, and how you might use this thinking to support visualizations</li></ol><p><em>FAIR WARNING: YOU GET WHAT YOU PAY FOR WHENEVER DATA IS CONCERNED. Analysis on the cheap is going to be bad analysis and the more powerful the toolkits the more that can go wrong. I am focusing only on building an affordable data pipeline and storage solution, which are things that are likely in reach for a sufficiently technical Salesforce administrator.</em></p><p><em>If you read this series and feel despair at executing on this stuff yourself, reach out. We at </em><a href="https://deepwhydesign.com/"><em>Deep Why</em></a><em> focus on making your Salesforce ecosystem work really well, which can include getting it into an Azure data warehouse. Our friends at </em><a href="http://inciter.io/"><em>Inciter</em></a><em> specialize in helping you understand your data and making all of your reporting needs seamless and easy. We often work together, so if you’re not sure just shout and we’ll get you to the right team.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=365ec39bdd96" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Nonprofit Data Pipeline On the Cheap (Part 1)]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-1-ca5cfad710b?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca5cfad710b</guid>
            <category><![CDATA[nonprofit-technology]]></category>
            <category><![CDATA[salesforce-for-nonprofits]]></category>
            <category><![CDATA[salesforce]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Wed, 10 May 2023 12:36:17 GMT</pubDate>
            <atom:updated>2023-05-21T18:27:43.601Z</atom:updated>
            <content:encoded><![CDATA[<h4>Data on a budget? We’ve got some duct tape for that!</h4><h4>Salesforce reporting is amazing until it isn’t</h4><p>There comes a time in every nonprofit’s Salesforce journey where you encounter The Dreaded Hinge Problem (tm). I have named it this because your data model looks like a door hinge — two child objects linked together by the same parent.</p><p>Let’s say you keep participant data in Contacts, you track their program participation in a custom Program object, and you track their employment engagement in Affiliations. You also track their engagement in program-related events in Campaign Member records.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t6fImkzYH_ZAmlH5804HGw.png" /><figcaption>A diagram showing how a very simple and very common data structure might look in Salesforce</figcaption></figure><p>If you need to ask how many participants took part in a specific campaign — easy. That’s a Campaign with Contacts report.</p><p>If you need to ask how many participants started a job with a particular company during a particular timeframe — easy. That’s an Affiliations report.</p><p>If you need to ask how many participants started or completed your program in a given timeframe — easy. That’s a Program and Contact report.</p><p>If you need to know, of the participants who started your program last year, how many participated in a specific event? A little less easy but that’s a cross-filter report most likely.</p><p>But if you need to know, of those same participants, which events did they participate in while in the program and who wound up with a job at a specific employer, with details from both objects? Yeah, not so much.</p><h4><strong>Sometimes you need SQL. Sorry, not sorry.</strong></h4><p>Salesforce is awesome at simple list building but doesn’t provide a true query tool (aside from actual programming), and without a true query tool you can’t specify exactly how to join these lists of different lengths together.</p><p>Before you start with your suggestions — I have spent over a decade engineering Salesforce-native solutions to this problem. At Deep Why we love <a href="https://install.salesforce.org/products/dlrs/latest">DLRS</a> — we don’t really deploy without it anymore and if I ever meet Andy all the the rounds are on me. I am perfectly well aware of Reporting Snapshots and use them when I need to. I am also perfectly well aware of every single method of using Salesforce reports and all the available tools in the Salesforce toolkit to tackle this. Yes, I know there are paid 3rd party solutions.</p><p>And because I have an enterprise data systems background, frankly I’m just tired of adding technical debt or relatively high cost to a powerful solution just to get ad-hoc reports. All of those solutions work when your intended data set is stable and predictable — but tell me honestly how many times you just need something for a funder that is totally out of left field?</p><p>Sometimes you truly need a real warehouse/lake/whatever solution like my friends at <a href="http://inciter.io/">Inciter</a> can provide. Sometimes you just need some targeted automations and data architecture skills to put your data at your fingertips using all Salesforce native tools.</p><p>And sometimes you’re stuck in the middle, where you really just need a few giant datasets to cover 90% of every question anybody ever asks, but it’s not possible with your architecture and a more costly solution isn’t warranted.</p><p>Here’s what we’re doing for those situations, in hopes it helps others.</p><h4>A low-cost and simple data pipeline</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*A9WlUllbvqrpRyB7cBvWCQ.png" /></figure><p>We use:</p><ul><li>Salesforce for data capture — even in very complex architectures — and all reporting and dashboarding</li><li><a href="https://skyvia.com/">Skyvia</a> to ship data wholesale at low cost (about $85/mo*) into a…</li><li>…Microsoft Azure SQLServer database set to the lowest cost tier (about $15/mo) to set up views and large flat aggregation tables</li><li>Using Microsoft Automations to run nightly queries that transform the raw data into the aggregation tables (can be free if you optimize to less than 500 minutes of runtime per month)</li><li>Then back to Skyvia to ship the data back into a big flat object in SF for super easy reporting</li><li>We also use Google Data Studio for dashboards and visualizations when needed, being sure to expose only anonymized/aggregated data</li></ul><p><em>*Skyvia is volume based and this price assumes an annual rather than monthly subscription. Our very large clients need to step up from the $79/mo to $399/mo data subscription, but our very large clients would be paying a LOT more for a different stack and are served really well with this.</em></p><p>Total annual cost is whatever Salesforce costs you plus less than $2k / year if you have fewer than 500,000 records transferring around per month, which is totally doable if you use the incremental replication functionality.</p><p>We are not partners with** or receiving any money from any of these tools. We are consultants who get to play with a bunch of different tools while doing our work, so have hands-on experience with what it really takes and what it really costs for a range of options. From that expertise we have found this toolkit works really well and have seen that the actual costs are affordable, so that’s where we’re coming from.</p><p>** <a href="https://deepwhydesign.com/"><em>Deep Why’s</em></a><em> sister company </em><a href="https://hcasw.com/"><em>HCA</em></a><em> is a Salesforce ISV partner, in the interest of transparency.</em></p><h4>What’s in this series?</h4><p>It took us awhile to figure out the right set of tools to get this done at a reasonably affordable price, and of course if you don’t have in-house SQL expertise you will have to add some consulting dollars to your budget. We’re hoping that this helps folks who are looking for an affordable solution for those tricky “in between” situations — we learned it and are happy to share it with everybody in case it’s useful.</p><p>You should read these in the order listed below.</p><ol><li><strong>Overview</strong> — this article, which introduces the series and outlines our example use case</li><li><a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-2-the-ecosystem-365ec39bdd96"><strong>Salesforce and the data visualization ecosystem</strong></a> — when do you need to consider something other than Salesforce for reporting?</li><li><a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-part-3-azure-resources-a3d25dc1a2af"><strong>Setting up SQLServer as a “good enough” Data Warehouse</strong></a><strong> </strong>— the steps to set up SQLServer in an Azure environment to meet the requirements of being “good enough” for this use case</li><li><a href="https://deepwhydesign.medium.com/salesforce-obscura-nonprofit-data-pipeline-on-the-cheap-skyvia-part-4-bcb8c0426e6c"><strong>Setting up Skyvia to replicate your Salesforce data into SQLServer</strong></a> — the steps to use Skyvia to pull data from Salesforce into SQLServer on a regular basis</li><li><strong>Transforming data inside your SQLServer</strong> <strong>for visualization </strong>— the overview of why you’d have data in SQLServer in the first place, what your automation options are, and how you might use this thinking to support visualizations</li></ol><p>At each step we’ll talk about who typically owns or should be participating in this work and considerations for executing on each step, but we won’t be providing actual SQL examples for transformation since that depends on your unique situation.</p><p><strong><em>FAIR WARNING: YOU GET WHAT YOU PAY FOR WHENEVER DATA IS CONCERNED</em></strong><em>. Analysis on the cheap is going to be bad analysis and the more powerful the toolkits the more that can go wrong. I am focusing only on building an affordable data pipeline and storage solution, which are things that are likely in reach for a sufficiently technical Salesforce administrator.</em></p><p><em>If you read this series and feel despair at executing on this stuff yourself, reach out. We at </em><a href="https://deepwhydesign.com/"><em>Deep Why</em></a><em> focus on making your Salesforce ecosystem work really well, which can include getting it into an Azure data warehouse. Our friends at </em><a href="http://inciter.io/"><em>Inciter</em></a><em> specialize in helping you understand your data and making all of your reporting needs seamless and easy. We often work together, so if you’re not sure just shout and we’ll get you to the right team.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca5cfad710b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Update a field when you upload a file (with Flows!)]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-update-a-field-when-you-upload-a-file-with-flows-dbefda00d57c?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/dbefda00d57c</guid>
            <category><![CDATA[nonprofit-technology]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[salesforce-for-nonprofits]]></category>
            <category><![CDATA[salesforce-flow]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Thu, 27 Oct 2022 20:27:39 GMT</pubDate>
            <atom:updated>2022-10-27T20:35:52.130Z</atom:updated>
            <content:encoded><![CDATA[<h4>One more way to use Screen Flows to manage Files while tracking exactly which files have been uploaded</h4><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FvcU1StmH1k8%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DvcU1StmH1k8&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FvcU1StmH1k8%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="640" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/e440824948c79b7ed837c8f347394ad9/href">https://medium.com/media/e440824948c79b7ed837c8f347394ad9/href</a></iframe><p>In our <a href="https://deepwhydesign.medium.com/salesforce-obscura-auto-copy-a-file-to-a-rich-text-field-with-flows-bb028d883149">last article</a>, I showed you how to grab an uploaded image file from an Experience Cloud page and copy it into a Rich Text field. This article is a slightly more practical variation (and a bit easier to implement too!).</p><p>Our clients are all nonprofits, and a large percentage of them need to track and report on whether their participants have provided certain files. Our clients make requirements reportable and track them in Salesforce in one of 3 different ways:</p><ul><li>Creating and completing a Task for each requirement.</li><li>Creating custom fields so they can check a box (please don’t*) or enter a date (MUCH better!) to indicate the completion of each requirement.</li><li>Adding a record to a custom child object for each requirement.</li></ul><p>Each of these approaches is great for reporting, list views, and generally keeping track of a lot of people with a lot of files to track.</p><p><em>(* Why not use a checkbox? They’re everybody’s favorite field! As an architect and data person I hate them because an empty box can equally mean “not yet captured” and “I forgot to check the box”; also checkboxes can’t give you any history or sense of when they were checked. Is this stale data? Recent? Who knows, it’s a mystery! I like dates for requirements, but a picklist that lets you distinguish between “not provided” and “I forgot” works well in the right setting too.)</em></p><p>But every file-based requirement means that first you have to use the File uploader, and then you have to navigate to wherever else you’re keeping track of that information and update that data too. It doesn’t sound like a lot, but our clients deal with thousands of people a year so every click saved compounds into a really big deal.</p><p>Good thing we can solve it with Screen Flows!</p><h4>Example Use Case and Solution Overview</h4><p>Our example nonprofit needs to track whether its applicants have submitted a resume, two essays and a photo. The applicant can’t be evaluated for entry into the program without all of these items completed.</p><p>Applicant data is tracked on a custom Application object, because people can apply to the program once time each year. We’ve set up 4 date fields — one for each requirement — and a formula to let us know when they’re all done.</p><p>We’re going to create a screen flow that captures and attaches the file upload to the Applicant record while also setting the date based on the type of file uploaded.</p><p><strong>Solution Overview:</strong></p><ol><li><a href="#5755">Prepare the Application record with your requirements fields</a></li><li><a href="#72b3">Create a Screen Flow and set up its Resources</a><br><a href="#2ff5">(2a) New Resource: AppRecordID<br></a><a href="#9a24">(2b) New Resource: UploadedCVIds</a></li><li><a href="#8860">Create the file upload screen</a><br><a href="#e182">(3a) Create the Screen element</a><br><a href="#3d5f">(3b) Add the picklist component</a><br><a href="#b33e">(3c) Add the file upload component</a></li><li><a href="#16a9">Process the uploaded file and update the Application record</a><br><a href="#3edf">(4a) Decision: Do we proceed? If so, what type of file was uploaded?</a><br><a href="#76d3">(4b) Update Record: Update Application</a><strong><br></strong><a href="#1af8">(4c) Save and Activate your Flow</a></li><li><a href="#fa68">Embed the Screen Flow into the Lightning Record page</a><br><a href="#5665">(5a) Drag the Flow onto the Lightning Record Page</a><br>(5b) Configure the Flow Component</li></ol><p>There are so many variations on this solution. You could find and complete a Task based on the file type. You could set up an uploader that accepts all 4 files at once. You could replace the dates with URLs so that the record links right to the file preview (that seems…redundant since that’s what Files does, but hey, you live your dreams). My goal is to prompt some “could we make this easier” thinking and show how to address some interactions with Files and Flows.</p><h4>(Step 1) Create requirements fields on your object.</h4><ul><li>I added 4 Date fields — one for each requirement — on my Application object. Your details will obviously differ from mine.</li><li>I also added a formula field that shows me when all requirements are met, for easy filtering in list views.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5KuWAyXdF0MuO1vc8KR1bA.png" /><figcaption>Four new date fields to track requirements</figcaption></figure><p>The formula from these returns a checkbox, and is structured like this:</p><blockquote>IF(AND(NOT(ISBLANK(Date_Essay_1_Received__c)),<br> NOT(ISBLANK(Date_Essay_2_Received__c)),<br> NOT(ISBLANK(Date_Photo_Received__c)),<br> NOT(ISBLANK(Date_Resume_Received__c))<br>),<br>TRUE,<br>FALSE<br>)</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C17uBQ8-htM51UFPmYaHWg.png" /><figcaption>The starting point for our Application page layout — Files is visible and our Requirements fields are also visible, but it would currently be a manual process to track both</figcaption></figure><h4>(Step 2) Create a new Screen Flow and Set Up Resources</h4><p>The overview of my Screen Flow is below. Because this is being integrated into an internal Lightning Record page, I’m not doing the “do we have all of our data” checks that we did in the <a href="https://deepwhydesign.medium.com/salesforce-obscura-auto-copy-a-file-to-a-rich-text-field-with-flows-bb028d883149">last solution</a>. If you want nicer error messages, you certainly can do that check first.</p><p>This part of the documentation focuses on the Screen Flow setup. <a href="#8860">Step 3</a> shows setting up the file capture itself, and <a href="#16a9">Step 4</a> shows the processing logic.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0wutAy6clplFH5P0ggZAFg.png" /><figcaption>The full Screen Flow overview, designed to be embedded in an Application Record page</figcaption></figure><p>You will need two Resources to make this work, outlined in detail below. Create all new Resources in the Toolbox, under “Manager,” by clicking “New Resource.”</p><p><strong>(2a) New Resource: AppRecordID<br></strong><em>Explanation</em>: to construct this solution, you have to know which Application record you want to attach the new file to and, for this example, which record contains the field/s you will update. In the Lightning Record Page you can pass the ID of the Application into a Flow variable. So you are setting up this variable to anticipate embedding it into your record page.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g4EF98EYo4LY3GveRsLQMg.png" /><figcaption>Setting up the Application Record ID variable for use throughout the flow</figcaption></figure><p>Create your Application Record ID variable and ensure it is Available for Input so that it can be embedded properly</p><ul><li><em>Resource Type:</em> Variable</li><li><em>API Name:</em> AppRecordID</li><li><em>Description</em>: This variable contains the ID of the Application Record that embeds this flow</li><li><em>Data Type</em>: Text</li><li>Make sure to check the box next to Available for Input</li></ul><p><strong>(2b) New Resource: UploadedCDIDs<br></strong><em>Explanation</em>: the file uploader available in Screen Flows will return the IDs of the uploaded files (content documents) and content versions, but always as a list (collection). So you are creating a collection variable to contain the results, even though you are only going to allow a single file to be uploaded in the flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6hqhrqWTKkPw64mZE22D1Q.png" /><figcaption>Set up a collection variable to capture the returned data from the File Uploader</figcaption></figure><p>Create a collection variable to contain the Content Document IDs returned from the file uploader</p><ul><li><em>Resource Type:</em> Variable</li><li><em>API Name:</em> UploadedCDIDs</li><li><em>Description</em>: This collection contains the record set required by the file uploader to get the Content Version IDs back</li><li><em>Data Type</em>: Text</li><li>Check the box next to “Allow multiple values (collection)”</li></ul><h4>(Step 3) Create the File Upload Screen</h4><p>Now that you have the prep work done, you can start building your screen flow. This is a very simple screen that captures the type of requirement this file satisfies, and the File Uploader itself.</p><p><strong>(3a) Create the Screen element<br></strong><em>Explanation: </em>In Screen Flows, a Screen is the way you interact with a user (rather than the data processing elements like Decision or Assignment). We need to create the screen that requests both the file itself and asks the user to identify which requirement the upload is satisfying.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YtTc83Q24OZ8AnyYBXeN5Q.png" /><figcaption>The Screen that allows a user to upload a file and specify which requirement it’s for</figcaption></figure><ul><li>Click + to start your Flow, and choose “Screen”</li><li><em>Screen Properties: Label</em>: Upload Requirement Document</li><li><em>Screen Properties: Description</em>: Upload a document to satisfy a program requirement.</li><li><em>Configure Header: </em>Check “Show Header” and (optional) Show Help Text.</li><li><em>Configure Footer: </em>Check “Show Footer”.</li><li><em>Configure Footer: Next or Finish Button:</em> Use a Custom Label (“Save File”)</li><li><em>Configure Footer: Previous Button: </em>Hide Previous</li><li><em>Configure Footer: Pause Button: </em>Hide Pause</li></ul><p><strong>(3b) Add the picklist component<br></strong><em>Explanation: </em>We want to present a required field on our screen, asking which requirement this upload satisfies. For this example we have chosen a picklist because our use case requires a one-to-one relationship between a file upload and a requirement. If one file upload satisfies multiple requirements for you, you can change this as needed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*F_rPd1K3-zyBzQJAy1ZDhg.png" /><figcaption>An example of the Picklist configuration with manually-assigned choices</figcaption></figure><ul><li>Under “Components” on the left-hand side, find “Picklist” and drag it into the center panel.</li><li><em>Label</em>: Requirement Satisfied by this Document</li><li>Check the box next to “Require”</li><li><em>Configure Choices: Let Users Select Multiple Options</em>: No</li><li><em>Component Type</em>: Picklist</li><li><em>Choice</em>: Enter the first choice of your picklist and click “+ Create [choicename] Choice” to save it as an option. Do this until all choices are set.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/608/1*Bd6SskHPvW2P7lvfQeDLQw.png" /><figcaption>How to manually add a choice to a picklist in a Flow</figcaption></figure><p><strong>(3c) Add the file upload component<br></strong><em>Explanation: </em>This component allows you to capture the file upload from the end user and let Salesforce do all the heavy lifting of putting it where it needs to be. In this step there are a few setup items that are necessary to ensure the file is linked correctly to its parent record and gives us back enough information for the next step.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MS4acarHDSXDt01fOXEg_A.png" /><figcaption>Manage the file upload — set the Related Record ID</figcaption></figure><p>Under “Components” on the left-hand side, find “File Upload” and drag it into the center panel.</p><ul><li><em>API Name</em>: UploadDocument</li><li><em>File Upload Label</em>: Upload Document</li><li><em>Accepted Formats:</em> leave this blank unless your requirements are of a specific file type (e.g. .pdf)<br>Note: you can just type in a comma-separated list of accepted formats</li><li><em>Allow Multiple Files: </em>False<br>Note: click into this field and type false. It will set the field to {!$GlobalConstant.False}</li><li><em>Content Document IDs: </em>LEAVE THIS BLANK</li><li><em>Content Version IDs: </em>LEAVE THIS BLANK</li><li><em>Disabled: </em>leave this blank</li><li><em>Hover Text: </em>leave this blank or put some sort of help text in if you prefer</li><li><em>Related Record ID: </em>{!AppRecordID}<br>Find the AppRecordID resource you set up in <a href="#2ff5">step 2a</a>.</li><li><em>Uploaded File Names: </em>leave this blank</li><li><em>Optional: Set Component Visibility</em>: I have left this to “Always”</li></ul><p><strong>IMPORTANT</strong>: <strong>Expand the Advanced dialogue box</strong></p><ul><li>Check the box for “Manually Assign Variables”</li><li>Leave everything else blank except for <em>Content Document IDs</em></li><li><em>Content Document IDs: </em>{!UploadedCDIDs}<br>Find the UploadedCDIDs collection resource you created in <a href="http://9a24/">step 2b</a>.</li><li><em>Revisited Screen Values</em>: Check next to “Refresh inputs to incorporate changes elsewhere in the flow”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*07KEg546I7OYThqjeU96Uw.png" /><figcaption>Put the UploadedCDIDs into the Advanced section</figcaption></figure><p>The Advanced options for File Upload are the same as the standard ones, but you must set the Content Version IDs here instead of in the standard settings for this example to work.</p><h4>(Step 4) Process the uploaded file and update the Application record</h4><p>This next step of the Flow will process the uploaded file so that we know which requirement date to set.</p><p><strong>(4a) Decision: Do we proceed? If so, what type of file was uploaded?</strong><em>Explanation</em>: After the user clicks “Save File,” Salesforce will automatically do what it needs to do to put the file on the parent object, but we need to just double-check to make sure that happened correctly. And we need to know which requirement field to update, which is why we’ll take advantage of the multiple Outcomes in a Decision action.</p><p>Immediately after your screen, add a Decision to determine not only what kind of file was uploaded, but also if we even have a file at all. That’s just a simple best practice to double check before proceeding that nothing went wrong.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iAd0e5XNsINODSdaJarldw.png" /><figcaption>An example of the Decision component that determines if we have a successful file upload and, if so, what type of file was uploaded</figcaption></figure><ul><li>Click + to under your screen and choose “Decision”</li><li><em>Label</em>: What Type of File</li><li><em>Description</em>: What type of file was uploaded?</li></ul><p>Outcomes 1:</p><ul><li><em>Label</em>: Photo Uploaded</li><li><em>Condition Requirements to Execute Outcome</em>: All Conditions are Met (AND)</li><li><em>Set up the first condition: </em>Requirements_Satisfied_by_this_Document [Equals] Photo<br>In this condition, we are asking if the <a href="#3d5f">picklist</a> on the File Upload screen was set to our first choice, which for this example is Photo.</li><li><em>Set up the second condition: </em>UploadedCDIDs [Is Null] False<br>In this condition, we are asking if the file uploaded successfully. Under Resource, choose the UploadedCDIDs resource you set up in <a href="#9a24">step 2b.</a></li></ul><p>Outcomes 2–4: repeat the same steps for Outcomes 1, one for each requirement <a href="#3d5f">picklist</a> value you created when setting up your screen flow.</p><p><strong>(4b) Update Record: Update Application<br></strong><em>Explanation: </em>The whole purpose of uploading a document and choosing the requirement it fulfills is to be able to set the correct piece of data on the Application record. This is the step where we make all of that happen by using the decision we set in the step above to determine which field on the Application record to set.</p><p>Under each of your decision branches, you will add an “Update Record” action to update the relevant requirement date field on the Application that started the flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZLOd_1dbDJlFinv7FTb0Qw.png" /><figcaption>The first of the Update Records actions, one for each decision branch. This one update the Photo requirement date.</figcaption></figure><ul><li>Click + to under your first decision branch (ours is called “Photo” to indicate a photo was uploaded) and choose “Update Record”</li><li><em>Label</em>: Update Application with Photo</li><li><em>Description</em>: Update the Photo Requirement date</li><li><em>How to Find Records to Update and Set Their Values: </em>Specify conditions to identify records, and set fields individually</li><li><em>Object</em>: Application <br>Choose the object that started your flow. Ours is called Application.</li><li><em>Condition Requirements to Update Record</em>: All Conditions are Met (AND)</li><li><em>Condition</em>: ID [equals] AppRecordID<br>Find the AppRecordID resource you set up in <a href="#2ff5">step 2a</a>.</li><li><em>Set Field Values for the Application Records: Field: </em>Date_Photo_Received__c<em><br></em>Choose whichever requirement field corresponds to the upload of the first picklist item. For us, if the file uploaded is a Photo, then we update the Date Photo Received field on Application.</li><li><em>Set Field Values for the Application Records: Value: </em>$Flow-&gt;Current Date<br>In this field, look for $Flow from the list of options. Click on it and choose Current Date.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*DCxtgG7TdJ9uFbm8RSdmJA.png" /><figcaption>When setting the value of a field, look for $Flow &gt;</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*HOKD4F_uo5JvFJ2kYToUjA.png" /><figcaption>Once you click on $Flow &gt; choose CurrentDate</figcaption></figure><p>Copy and paste this element into each of your decision branches, making sure to update the Field to reflect whichever requirement you are on.</p><p><strong>(4c) Save and Activate your Flow<br></strong>Now is a really good time to pause and celebrate making it this far.</p><h4>(Step 5) Embed Your Flow into a Lighting Record Page</h4><p>The last step is to allow your users to access and launch your flow. We wanted to embed this right above the File list on the Application page, so that’s what this example shows.</p><p><strong>(5a) Setup-&gt;Object Manager-&gt;Application-&gt;Lightning Record Pages and drag the Flow component onto the screen<br></strong><em>Explanation: </em>this Screen Flow was designed to be embedded directly into your record page so that it’s super easy for your users. This step just connects the dots between the Flow you just built and the record itself.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mYx_x8CpA4AAJWe5uM_QjQ.png" /><figcaption>Drag the Flow Component into your page layout</figcaption></figure><p><strong>(5b) Configure the Flow Component<br></strong><em>Explanation</em>: This step links the Flow you created above to the record page. It uses the Input variable you set up in <a href="#2ff5">step 2a</a>. Remember when you checked the “Available for Input” box on the AppRecordID resource? that’s what allows the Flow Component to pass the Record ID in to your Screen Flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*yr9DR_AOgqDRtgN29tWB5w.png" /><figcaption>Set up your Flow component and pass in the Record ID</figcaption></figure><ul><li>Click on the component to open up its contextual configuration window.</li><li><em>Flow</em>: Upload Requirements Document<br>or whatever you named the flow you created in steps 2–4</li><li>Check the box next to “Pass record ID into this variable” under the “AppRecordID” variable.</li><li>Save and publish.</li></ul><h4>(Step 6) Test it out</h4><p>Go to your record and upload a file. You should see your requirement automatically captured and the file linked to the record.</p><p>Congratulations! Have fun with your new expertise!</p><p><em>My firm (</em><a href="http://deepwhydesign.com/"><em>Deep Why Design</em></a><em>) specializes in weird use cases and things nobody else wants to touch — from big and innovative shared platforms and custom solutions to </em><a href="https://www.deepwhydesign.com/courses-and-training"><em>teaching accidental admins</em></a><em> how to get the most out of their systems. My areas of expertise are fire jumping and forensics — call when there’s an unsolvable problem — and the design and management of large, integrated, and complex data systems in highly constrained situations.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dbefda00d57c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Auto-copy a File to a Rich Text Field with Flows]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-auto-copy-a-file-to-a-rich-text-field-with-flows-bb028d883149?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/bb028d883149</guid>
            <category><![CDATA[salesforce-communities]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[salesforce-flow]]></category>
            <category><![CDATA[screenflow]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Sun, 23 Oct 2022 21:00:40 GMT</pubDate>
            <atom:updated>2022-10-23T21:02:38.606Z</atom:updated>
            <content:encoded><![CDATA[<h4>Option 2 of 2 — using ScreenFlows to upload the file from an Experience Cloud setting</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*B1MC9r1SKc9uLFdY.png" /></figure><p>In our <a href="https://deepwhydesign.medium.com/salesforce-obscura-auto-copy-a-file-to-a-rich-text-field-6664d21f455d">last article</a>, we demonstrated how to use FormAssembly to capture and load a file into Salesforce, then use a Flow to automatically display that uploaded file in a Rich Text field.</p><p>This article will take you step-by-step through accomplishing the same goal with Screen Flows in a 100% Salesforce native way. Note that it may look like a lot of steps, but I’ve broken it down into a true step-by-step tutorial so that those less familiar with Flows and Screen Flows can follow along.</p><h4>Example Use Case and Solution Overview</h4><p>For this article, we tackle a very common request from our clients: getting a Customer Community User to upload their own photo and displaying that photo in a rich text field for internal users to view and use.</p><p><strong>Solution Overview:</strong></p><ol><li><a href="#3eb4">Prepare the Contact record with a Rich Text field to display the photo and a field to help control the Flow</a></li><li><a href="#59f9">Create a Screen Flow and set up its Resources</a><br><a href="#21c9">(2a) New Resource: ContactRecordID</a><br><a href="#ba2d">(2b) New Resource: CVID</a><br><a href="#4ff3">(2c) New Resource: UploadedCVIds</a><br><a href="#da0f">(2d) New Resource: TypeOfFile</a><br><a href="#b96d">(2e) New Resource: DocumentURL</a><br><a href="#e582">Getting the URL for the Image tag</a></li><li><a href="#7f12">Create the initial logic and file upload screen</a><br><a href="#fc3b">(3a) New Get Records: Find Related Contact Record</a><br><a href="#8000">(3b) Decision: Do we have a contact record at all? With or without a photo?</a><br><a href="#126a">(3c) Add Element: Screen under “Yes — With Photo”</a><br><a href="#caa4">(3d) Add Element: Screen under “No”</a><br><a href="#8a40">(3f) Add Element: Screen under “Yes — No Photo”</a></li><li><a href="#3154">Process the uploaded file and update the Contact record</a><br><a href="#6c03">(4a) Loop: Process Content Version IDs</a><br><a href="#5ef7">(4b) Assignment: Assign CVID variable</a><br><a href="#173c">(4d) Assignment: Set Photo Fields on Contact Record</a><br><a href="#8367">(4e) Update Record: Update Contact</a><strong><br></strong><a href="#1f84">(4f) Save and Activate your Flow</a></li><li><a href="#134a">Embed the Screen Flow into the Experience Cloud page</a><br><a href="#d1de">(5a) Add Flow to Contact Detail Page in the Builder</a><br><a href="#1871">(5b) Configure the Flow Component</a></li><li>Test</li></ol><p><strong>Important Notes for this solution</strong>: Once you begin working in Experience Cloud, a variety of permissions and visibility issues come into the picture. This article does not address this solution for public-facing pages where guest user permissions become important, though maybe one day we will. We are demonstrating something that is relatively straightforward on the permissions side and assuming the logged-in user has a Customer Communities license and, therefore, access to view their own Contact record. We are assuming that, if you’re reading this, you already have a correctly-set-up Experience Cloud with all relevant permissions already set.</p><h4>(Step 1) Create 2 new fields on your object.</h4><p>Add the following fields to your object:</p><ul><li>A text field, 18 characters long, to contain the ID of the file you will upload. I called mine “Photo CV ID” (note, you could make this a checkbox or anything else, it just aids in routing your screen flow)</li><li>A rich text field to display the photo itself. I called mine “Photo” and just took the defaults for a rich text field.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WSCxNlZlkA3tSzhr.png" /><figcaption>Two new fields — one for the ID, one rich text, on your target object</figcaption></figure><h4>(Step 2) Create a new Screen Flow and Set Up Resources</h4><p>The overview of my Screen Flow is below. I am first confirming that I get the information I need (Contact ID from the Experience Cloud page, in this case) and then determining which of 3 screens to show — a “thank you” message, our main uploader, or an error screen.</p><p>This part of the documentation focuses on the Screen Flow setup. <a href="#7f12">Step 3</a> shows setting up the file capture itself, and <a href="#3154">Step 4</a> shows the processing logic.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SYI_xQ5KF9CZfmFBNg2iHw.png" /><figcaption>The full Screen Flow overview, designed to be embedded in a Contact Record page</figcaption></figure><p>You will need several Resources to make this work, outlined in detail below. Create all new Resources in the Toolbox, under “Manager,” by clicking “New Resource.”</p><p><strong>(2a) New Resource: ContactRecordID<br></strong><em>Explanation</em>: to construct this solution, you have to know which Contact record you want to attach the new file to and display the uploaded photo within. In the Experience Cloud builder, as long as you’re using a Record Page, you can pass the ID of the Contact Record into a Flow variable. So you are setting up this variable to anticipate embedding it into Experience Cloud.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lPDCEAMUGNwaqJ_tbP5EUQ.png" /><figcaption>Create your Contact Record ID variable and ensure it is Available for Input so that it can be embedded properly</figcaption></figure><ul><li><em>Resource Type:</em> Variable</li><li><em>API Name:</em> ContactRecordId</li><li><em>Description</em>: This variable contains the ID of the Contact Record that embeds this flow</li><li><em>Data Type</em>: Text</li><li>Make sure to check the box next to Available for Input</li></ul><p><strong>(2b) New Resource: CVID<br></strong><em>Explanation</em>: the key to making a photo display in a rich text field is having the Content Version ID of the uploaded file. So you create a variable to contain it after the upload process. This isn’t strictly necessary but it makes our lives much easier.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X2WR51BVR3xFKPSnn20a6g.png" /><figcaption>Create a variable to hold the ID of the Content Version of the file uploaded by the user</figcaption></figure><ul><li><em>Resource Type:</em> Variable</li><li><em>API Name:</em> CVID</li><li><em>Description</em>: Content Version ID for use in a formula</li><li><em>Data Type</em>: Text</li></ul><p><strong>(2c) New Resource: UploadedCVIds<br></strong><em>Explanation</em>: the file uploader available in Screen Flows will return the IDs of the uploaded files (content documents) and content versions, but always as a list (collection). So you are creating a collection variable to contain the results, even though you are only going to allow a single file to be uploaded in the flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xR6V5Z5oGVVKssNUm0FPfQ.png" /><figcaption>Create a collection variable to contain the Content Version IDs returned from the file uploader</figcaption></figure><ul><li><em>Resource Type:</em> Variable</li><li><em>API Name:</em> UploadedCVIds</li><li><em>Description</em>: This collection contains the record set required by the file uploader to get the Content Version IDs back</li><li><em>Data Type</em>: Text</li><li>Check the box next to “Allow multiple values (collection)”</li></ul><p><strong>(2d) New Resource: TypeOfFile<br></strong><em>Explanation</em>: the Rich Text display needs to know what type of file it is displaying so that it can correctly generate a preview. This variable contains that information to help in our final step, the display formula.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kCMTf5Fpj-5zVkXKahQ6fA.png" /><figcaption>New variable TypeOfFile to help control the embedded display</figcaption></figure><ul><li><em>Resource Type:</em> Variable</li><li><em>API Name:</em> TypeOfFile</li><li><em>Description</em>: The uploaded photo can be one of several image types. This helps the rich text field display the photo correctly.</li><li><em>Data Type</em>: Text</li></ul><p><strong>(2e) New Resource: DocumentURL<br></strong><em>Explanation: </em>This is where you bring it all together into the formula that actually displays the uploaded file in your new Rich Text field. It is an HTML image tag that references the content version ID and the type of file (resources you created above). Note that they are referenced in the formula, so if you changed the names of the resources you will need to change them in the formula too.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1_t3ojD3y1FQVhZ_rKzfEQ.png" /><figcaption>The formula that actually performs the photo embed</figcaption></figure><ul><li><em>Resource Type:</em> Formula</li><li><em>API Name:</em> DocumentURL</li><li><em>Description</em>: This is the formula that allows for the preview in the rich text field.</li><li><em>Data Type</em>: Text</li><li><em>Formula</em>: <br>‘&lt;p&gt;&lt;img src=”https://REPLACEME.file.force.com/sfc/servlet.shepherd/version/renditionDownload?rendition=ORIGINAL_&#39;+{!TypeOfFile}+&#39;&amp;amp;versionId=&#39;+{!CVID}+&#39;&amp;amp;operationContext=CHATTER&quot; alt=”Photo” height=”200&quot; width=”200&quot;&gt;&lt;/img&gt;&lt;/p&gt;’</li></ul><p><strong>IMPORTANT</strong>: Replace REPLACEME in the formula above with your actual Salesforce domain. From any record page, just grab the part of the URL before “.lightning.force.com”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/0*r1uIuB1HMK-k7U6z.png" /></figure><p>The part in red is what I would put in place of REPLACEME below. Your URL is unique to your Salesforce instance. Note that if you are building this in a sandbox, you will need to update your Flow with your production URL when you move this into production.</p><p><strong>IMPORTANT: Getting the URL for the Image tag<br></strong>The URLs for Salesforce resources change periodically. You may find that this formula works great for awhile and then suddenly stops working, in which case I’d immediately look to the URLs.</p><p>To get the URL currently in use for the photo previews, I:</p><ol><li>Manually upload an image file to a Contact record</li><li>Click on the file to open it in the File Preview. Right click (command+click on mac) and copy the preview to my clipboard.</li><li>Paste from my clipboard into a Rich Text field on Contact and save my changes.</li><li>Use dataloader.io to export only the record I’m testing with, and only my rich text field. The html for the working, pasted image will show up in plain text in the export, allowing you to mimic it in this formula field.</li></ol><h4>(Step 3) Add the Initial Logic and Screens to your Screen Flow</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MXcQAfpzKIVfOKav3KGevg.png" /><figcaption>In Step 3 we will build the pieces shown in the image above: get the contact, decide what screen we need to show, and show the screens.</figcaption></figure><p>Now that you have all of the prep work done, you can start building your screen flow. It’s a best practice to confirm that you have all of the data you’re expecting before jumping right into processing it, so we’re going to take advantage of that to show one of 3 different scenarios: (1) we have already gotten the photo; (2) we don’t yet have a photo; or (3) something is wrong and we don’t have all the data we need.</p><p><strong>(3a) New Get Records: Find Related Contact Record<br></strong><em>Explanation: </em>This Screen Flow is intended to be embedded in a Contact Record page. We need to confirm we have the Contact ID and can access that record before going any further. To do this, we use the Resource “ContactRecordID” that we set up in <a href="#21c9">step (2a)</a> to find the full record details and make them available later in the Flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rH6a0sq8dcvClmI0H15lZg.png" /><figcaption>Use the ContactRecordID variable to find and store the full Contact record.</figcaption></figure><ul><li><em>Label</em>: Find Related Contact Record</li><li><em>Description</em>: Look up the Contact record for the ID that was passed in to the ContactRecordID variable</li><li><em>Get Records of This Object:</em> Contact</li><li><em>Filter Contact Records</em>: All Conditions are Met (AND)</li><li><em>Filter Contact Records</em>: <em>Field</em>: Id [equals] <a href="#21c9">ContactRecordID</a></li><li><em>Sort Order</em>: Not Sorted</li><li><em>How Many Records to Store</em>: Only the first record</li><li><em>How to Store Record Data</em>: Automatically store all fields</li></ul><p><strong>(3b) Decision: Do we have a contact record at all? With or without a photo?<br></strong><em>Explanation</em>: For this example, we want the user to see a different message once they have successfully uploaded their photo. Because it’s not relevant for this article, I’ve put in some very simple placeholder screens. You might choose to display the photo and a “change file” link, or any other logic that your actual use case demands.</p><p>We use the new text field <a href="#3eb4">Photo CV ID that we set up in step 1</a> to help us determine easily whether we have what we’re looking for or not.</p><p>Note that you can use a simple checkbox, look for the presence of data in the Photo field, or any other type of logic you choose. I tend to capture more information to aid in debugging wherever possible, so having the full Content Version ID can give me more information than a checkbox when I’m trying to troubleshoot permissions or duplicates (for example).</p><p>New Decision:</p><ul><li><em>Label</em>: Did we find a record?</li><li><em>Description</em>: This screen flow can only be launched from a Contact page so we should always find a record. It is still a best practice to check for errors.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ENsIvAL2qYcCU017pWKDuQ.png" /><figcaption>Decision part 1: We have a Contact record and it already has a photo</figcaption></figure><p>Outcomes 1:</p><ul><li><em>Label</em>: Yes — With Photo</li><li><em>Condition Requirements to Execute Outcome</em>: All Conditions are Met (AND)</li><li><em>Set up the first condition: </em>Contact from Find_Related_Contact_Record [is null] False<br>In this condition, we are asking if the record itself is null / did we get anything back from the lookup in <a href="#fc3b">step 3a</a>. Under Resource, choose the Find Related Contact Record result that was automatically created when you set up <a href="#fc3b">step 3a</a>. Click on it. It will populate your field with {!Find_Related_Contact_Record.} and prompt you to select other fields.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*xAjkISmxTEa9B5SaX9DGLg.png" /><figcaption>Look for the record related to Find_Related_Contact_Record, or whatever you named your Get Records in (3a)</figcaption></figure><p>Just erase the trailing period so that it says {!Find_Related_Contact_Record} to reference the entire variable.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*IrikaH28YepC0iVXvELxpg.png" /><figcaption>This is what your first condition should look like: {!Find_Related_Contact_Record} with no period</figcaption></figure><ul><li><em>Set up the second condition: </em>Contact from Find_Related_Contact_Record.Photo_CV_ID__c [is null] False<br>In this condition we are asking the simpler question of whether the Photo CV ID on the Contact Record is empty or not. Follow the same steps as the first condition but this time look for the Photo CV ID field when prompted for a field selection.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FbfIUNwEK3yGvZQQHnWYuA.png" /><figcaption>Outcomes two — nearly identical, except we want to detect that the Photo CV ID <em>is</em> empty this time.</figcaption></figure><p>Click + next to Outcome Order to add Outcomes 2:</p><ul><li><em>Label</em>: Yes — No Photo</li><li><em>Condition Requirements to Execute Outcome</em>: All Conditions are Met (AND)</li><li><em>Set up the first condition: </em>Contact from Find_Related_Contact_Record [is null] False<br>This condition is set up exactly as you did for Outcomes 1.</li><li><em>Set up the second condition</em>: Contact from Find_Related_Contact_Record.Photo_CV_ID__c [is null] True<br>This condition is set up exactly as you did for Outcomes 2 <em>except</em> you want True instead of False. For this decision, we care about a Contact that exists but does not yet have a photo.</li></ul><p>Click on “Default Outcome” and change the label to “No” so that if neither of our outcomes is true, we get an error screen.</p><p><strong>(3c) Add Element: Screen under “Yes — With Photo”<br></strong><em>Explanation</em>: Your first decision point — yes, we have a contact record and yes, it already has a photo — will just show a simple “thank you” message for this example.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p7OPWggSyAJE58ots21j0w.png" /><figcaption>The completed, very simple confirmation that we already have a photo for this person.</figcaption></figure><ul><li>Click + under “Yes — With Photo” and choose “Screen”</li><li><em>Screen Properties: Label</em>: Confirm Photo</li><li><em>Screen Properties: Description</em>: This contact already has a photo or has successfully uploaded one.</li><li><em>Optional: Configure Header: </em>I also chose to configure the header and uncheck “Show Header” to make the embedded Screen Flow a bit more seamless</li><li><em>Optional: Configure Footer: </em>I chose to uncheck Show Footer entirely. This is informational only, no actions are needed so no buttons should be visible.</li></ul><p>Under “Components” on the left-hand side, find “Display Text” and drag it into the center panel.</p><ul><li><em>API Name</em>: ConfirmPhoto</li><li><em>In the rich text editor, to display your message:</em> We have received your photo. Thank you.</li></ul><p><strong>(3d) Add Element: Screen under “No”<br></strong><em>Explanation</em>: Keeping it simple, we want to add another Display Text if we have an error. So we’re going to jump over the middle decision point for right now and knock out the other quick screen before moving ahead.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*49GydD2SOQPZ0LK09RAD7A.png" /><figcaption>The finished result of a simple Contact Error Display Text screen under the “No” (default) decision</figcaption></figure><ul><li>Click + under “No” and choose “Screen”</li><li><em>Screen Properties: Label</em>: Error Contact Not Found</li><li><em>Screen Properties: Description</em>: This screen only appears if there is a problem passing the Contact ID or a problem with the end user reading the Contact Record.</li><li><em>Optional: Configure Header: </em>Uncheck “Show Header” as above</li><li><em>Optional: Configure Footer: </em>Uncheck Show Footer as above</li></ul><p>Under “Components” on the left-hand side, find “Display Text” and drag it into the center panel.</p><ul><li><em>API Name</em>: ContactError</li><li><em>In the rich text editor, to display your message:</em> We can’t find the Contact ID, sorry.</li></ul><p><strong>(3f) Add Element: Screen under “Yes — No Photo”<br></strong><em>Explanation</em>: Finally we get to the good stuff. This is where we actually set up our File Uploader. Please note that the file uploader is tricky — the variables it shows by default are <em>not</em> the ones you want! You want the ones in Advanced, as in the steps below.</p><p>When you have set this up exactly as below, you will have a prompt for a Community user to upload a single file as a child of their Contact record. That file will be limited to just image types (gif, jpg and png) and most important it will give us back the Content Version ID of the newly-uploaded file so that we can process it in <a href="#3154">Step 4</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*hvwYrlfA_K2DJveN8TzlzA.png" /><figcaption>Screen properties for our File Upload Screen with Header and Footer configured</figcaption></figure><ul><li>Click + under “Yes — No Photo” and choose “Screen”</li><li><em>Screen Properties: Label</em>: Upload Your Photo</li><li><em>Screen Properties: Description</em>: Upload a photo of yourself to share with our team.</li><li><em>Configure Header: </em>Check “Show Header” and (optional) Show Help Text.</li><li><em>Configure Footer: </em>Check “Show Footer”.</li><li><em>Configure Footer: Next or Finish Button:</em> Use a Custom Label (“Save File”)</li><li><em>Configure Footer: Previous Button: </em>Hide Previous</li><li><em>Configure Footer: Pause Button: </em>Hide Pause</li></ul><p>Under “Components” on the left-hand side, find “File Upload” and drag it into the center panel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*px2IV5-siq4y9V8e1NONtw.png" /><figcaption>Upload File with the settings shown in the right-hand part of the screen.</figcaption></figure><ul><li><em>API Name</em>: UploadPhoto</li><li><em>File Upload Label</em>: Upload</li><li><em>Accepted Formats:</em> .jpg,.png,.gif<br>Note: you can just type in a comma-separated list of accepted formats</li><li><em>Allow Multiple Files: </em>False<br>Note: click into this field and type false. It will set the field to {!$GlobalConstant.False}</li><li><em>Content Document IDs: </em>leave this blank</li><li><em>Content Version IDs: </em>LEAVE THIS BLANK<br>This version of the settings will not do what we want. We’ll handle this in Advanced, below.</li><li><em>Disabled: </em>leave this blank</li><li><em>Hover Text: </em>leave this blank or put some sort of help text in if you prefer</li><li><em>Related Record ID: </em>{!ContactRecordID}<br>Find the ContactRecordID resource you set up in <a href="#21c9">step 2a</a>.</li><li><em>Uploaded File Names: </em>leave this blank</li><li><em>Optional: Set Component Visibility</em>: I have left this to “Always”</li></ul><p>IMPORTANT: <strong>Expand the Advanced dialogue box</strong></p><ul><li>Check the box for “Manually Assign Variables”</li><li>Leave everything else blank except for <em>Content Version IDs</em></li><li><em>Content Version IDs: </em>{!UploadedCVIds}<br>Find the UploadedCVIds collection resource you created in <a href="#4ff3">step 2c</a>.</li><li><em>Revisited Screen Values</em>: Check next to “Refresh inputs to incorporate changes elsewhere in the flow”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*sWdhz-RLxhgMF7s-SHoZ0A.png" /><figcaption>The Advanced options for File Upload are the same as the standard ones, but you must set the Content Version IDs here instead of in the standard settings for this example to work.</figcaption></figure><h4>(Step 4) Process the uploaded file and update the Contact record</h4><p>Still in the “Yes--No Photo” logic branch, this last step of the Flow will process the uploaded file so that it is put into the Contact Rich Text field. It’s a little unintuitive if you’re not a programmer, but basically even though we are limiting the File Uploader to only one file, it will return our Content Version ID in a collection. So we have to process that collection as if there were more records in it, even though there aren’t.</p><p>This step proceeds down the middle of the Flow, directly under our Upload your Photo screen.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*fP2d3ulgxeYxKMjK90EXdw.png" /><figcaption>The portion of the Flow we are working with in Step 4 — directly under the Upload Your Photo screen</figcaption></figure><p><strong>(4a) Loop: Process Content Version IDs<br></strong><em>Explanation: </em>as stated in the Step 4 introduction, even though we know we are only getting back one ID, Salesforce doesn’t know that. So we have to process the results of our file upload as if we had a whole collection. That requires a loop.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Slk-cgAnvB9Fj76_2sMFSQ.png" /><figcaption>Set up a Loop to process the UploadedCVIds from the file uploader</figcaption></figure><ul><li>Click + under the Upload your Photo screen icon</li><li>Choose Loop</li><li><em>Label:</em> Process Content Version IDs</li><li><em>Description: </em>Although we only allow one file to be uploaded, the value is returned in a list so we have to process it in a loop</li><li><em>Collection Variable: </em>{!UploadedCVIds}<br>Use the <a href="#4ff3">UploadedCVIds</a> variable you created in <a href="#4ff3">step 2c</a> and populated from the Flow Uploader Advanced Setup in <a href="#9ca9">step 3f</a></li><li><em>Direction</em>: First item to last item</li></ul><p><strong>(4b) Assignment: Assign CVID variable<br></strong><em>Explanation: </em>you will use the CVID resource you created in <a href="#ba2d">step 2b</a> for the next step, in the <a href="#b96d">formula</a> that displays the photo, and to populate the Contact field that controls which screen shows up for the end user.</p><p>Assign the value that is in whatever loop of the collection we’re on — remember have only one, that’s the only way this works — to the CVID resource.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sFN-xOadFzwa0HHroZZ_EQ.png" /><figcaption>Assign the current item from Loop’s value to the CVID variable.</figcaption></figure><ul><li>Click + under the loop you added in <a href="#6c03">step 4a</a></li><li>Choose Assignment</li><li><em>Label:</em> Assign CVID variable</li><li><em>Description: </em>Although we only allow one file to be uploaded, the value is returned in a list so we have to process it in a loop</li><li><em>Set Variable Values: Variable: </em>CVID<br>Use the CVID resource you created in <a href="#ba2d">step 2b</a></li><li><em>Set Variable Values: Operator: </em>Equals</li><li><em>Set Variable Values: Value: </em>Current Item from Loop Process_Content_Version_IDs</li></ul><p><strong>(4c) New Get Records: Get CV from ID<br></strong><em>Explanation: </em>While we have, in hand, the ID of the Content Version, we do not have any other data about it. So we have to go look it up to populate the File Type.</p><p>Note that this is being done in a loop so it’s extremely poor practice to do this unless you are 100% sure that you are allowing ONLY ONE file. Otherwise you risk performing queries in a loop, which is never OK in Salesforce (or really anywhere else).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KW3UQ4vCVUtgybsnYEq8eQ.png" /><figcaption>Find the Content Version record for the CVID in this loop</figcaption></figure><ul><li>Click + under the Assign CVID variable you added in <a href="#5ef7">step 4b</a></li><li><em>Label</em>: Get CV from ID</li><li><em>Description</em>: Use the ID of the Content Version to obtain the file type</li><li><em>Get Records of This Object:</em> Content Version</li><li><em>Filter Contact Records</em>: All Conditions are Met (AND)</li><li><em>Filter Contact Records</em>: <em>Field</em>: Id [equals] <a href="#ba2d">CVID</a><br>Use the CVID resource you created in <a href="#ba2d">step 2b</a></li><li><em>Sort Order</em>: Not Sorted</li><li><em>How Many Records to Store</em>: Only the first record</li><li><em>How to Store Record Data</em>: Choose fields and assign variables (advanced)</li><li><em>Where to Store Field Values</em>: In separate variables<br>Store the Content Version’s File Type field in the resource <a href="#da0f">TypeOfFile</a> you set up in <a href="#da0f">step 2d</a></li><li>Field: File Type</li><li>Variable: <a href="#da0f">TypeOfFile</a></li></ul><p><strong>(4d) Assignment: Set Photo Fields on Contact Record<br></strong><em>Explanation: </em>This takes place AFTER the loop is over (on the “After Last” line). We set our variables in the loop, and now we need to assign them to the Contact Record that we are working with, which we selected at the very start of our Flow in <a href="#fc3b">step 3a</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yPyvSIROz6ninwSKQBY1ew.png" /><figcaption>Add this step on the “After Last” line by clicking the + indicated by the arrow, then choosing Assignment</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k0Lo0O5VFVvWKLYK8ylrOg.png" /><figcaption>Set the assignments for the Contact record that started our flow</figcaption></figure><ul><li>Click + on the “After Last” line</li><li>Choose Assignment</li><li><em>Label:</em> Set Photo Fields on Contact Record</li><li><em>Description: </em>Set the Photo-related fields on the Contact we found to start this flow</li></ul><p><strong>Variable 1: Contact from Find_Related_Contact_Record.Photo__c [equals] DocumentURL</strong></p><ul><li><em>Set Variable Values: Variable: </em>Contact from Find_Related_Contact_Record.Photo__c<br>Use the Find Related Contact Record that was automatically created for you in <a href="#fc3b">step 3a</a>, and select the rich text Photo field you created in <a href="#3eb4">step 1</a>. We are populating this field on the Contact record.</li><li><em>Set Variable Values: Operator: </em>Equals</li><li><em>Set Variable Values: Value: </em>DocumentURL<br>Use the <a href="#b96d">DocumentURL</a> formula resource you created in <a href="#b96d">step 2e</a> to populate the rich text field Photo on Contact.</li></ul><p><strong>Variable 2: Contact from Find_Related_Contact_Record.Photo_CV_ID__c [equals] CVID</strong></p><ul><li><em>Set Variable Values: Variable: </em>Contact from Find_Related_Contact_Record.Photo_CV_ID__c<br>Use the Find Related Contact Record that was automatically created for you in <a href="#fc3b">step 3a</a>, and select the text Photo CV ID field you created in <a href="#3eb4">step 1</a>.</li><li><em>Set Variable Values: Operator: </em>Equals</li><li><em>Set Variable Values: Value: </em>CVID<br>Use the <a href="#ba2d">CVID</a> resource you created in <a href="#ba2d">step 2b</a> to populate the rich text field Photo on Contact.</li></ul><p><strong>(4e) Update Record: Update Contact<br></strong><em>Explanation:</em> <strong>The last step in the flow! You made it through the hard part!! </strong>All this does is tell Salesforce to actually write the updated fields (from <a href="#173c">step 4d</a>) onto the Contact record we found at the very start of this process in <a href="#fc3b">step 3a</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*l7TNKUcsCEE8W9KSdL0XWA.png" /><figcaption>Update Contact just uses the record we’ve been working with this whole time and is the last step in the flow setup.</figcaption></figure><ul><li>Click + under the Assignment you created in the last step</li><li>Choose “Update Records”</li><li><em>Label: </em>Update Contact</li><li><em>Description</em>: Update the Contact that started this Flow with the data collected from the file upload</li><li><em>How to Find Records to Update and Set Their Values</em>: Use the IDs and all field values from a record or record collection</li><li><em>Record or Record Collection</em>: Contact from Find_Related_Contact_Record<br>Use the Find Related Contact Record that was automatically created for you in <a href="#fc3b">step 3a</a>.</li></ul><p><strong>(4f) Save and Activate your Flow<br></strong>Now is a really good time to pause and celebrate making it this far.</p><h4>(Step 5) Embed Your Flow into Experience Cloud</h4><p>This last step is really just icing. And don’t worry, it’s very simple! If you already know how to embed a Screen Flow on a Record Page in Experience Cloud, you can skip ahead to the testing.</p><p>Since we’re assuming that you already know how to set up Experience Cloud and how to set up permissions and visibility, we’re going to skip the how-to on creating a new Record page for the Contact Object. This example is working with a Contact Detail page that was already set up.</p><p><strong>(5a) Open your Contact Detail Page in the Builder and add a Flow component</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*oCOQof8TSU5M6In6LRUu8w.png" /><figcaption>In Experience Cloud Builder, on Pages, we are working with a Salesforce Object page, Record Detail layout.</figcaption></figure><p>Our page has a custom tab setup and I created a new tab just for this Flow called “Photo”. I have embedded a Flow Component on this tab, as in the screenshot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IpvMpz_0EBZFNvbZgpmxtQ.png" /><figcaption>Drag the Flow component into your page canvas</figcaption></figure><p><strong>(5b) Configure the Flow Component<br></strong><em>Explanation</em>: This step links the Flow you created above to the record page. It uses the Input variable you set up in <a href="#21c9">step 2a</a>. Remember when you checked the “Available for Input” box on the ContactRecordID resource? that’s what allows the Flow Component to pass the Record ID in to your Screen Flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*M_dqBliL9EZttBKNvlenGA.png" /></figure><ul><li>Click on the component to open up its contextual configuration window.</li><li><em>Flow</em>: Upload Photo From Community<br>or whatever you named the flow you created in steps 2–4</li><li>Check the box next to “Pass record ID into this variable” under the “ContactRecordID” variable.</li><li>Save and publish.</li></ul><h4>(Step 6) Test it out</h4><p>Please note that it is beyond what I can tackle in this article to address all of the permissions issues and things that could stand in your way in a Customer Communities license setting, so make sure the user you’re testing with can actually edit their own Contact record and upload files before testing your solution.</p><p>Log in to your community as a user. Upload a file, and you should get:</p><ul><li>A File attached to the Contact record for the user you are logging in as</li><li>A value in the Photo CV ID field</li><li>A 200x200 view of your file in your rich text Photo field</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KPR2jrAL8J-NPPD2.png" /></figure><p>Pictured above: The attached file, the Photo CV ID and the uploaded photo in your rich text field</p><p>Congratulations! You’ve learned a lot about Screen Flows, Flows, Content Documents and Content Versions, and how it all works with Experience Cloud! Have fun with your new expertise!</p><p><em>My firm (</em><a href="http://deepwhydesign.com/"><em>Deep Why Design</em></a><em>) specializes in weird use cases and things nobody else wants to touch — from big and innovative shared platforms and custom solutions to </em><a href="https://www.deepwhydesign.com/courses-and-training"><em>teaching accidental admins</em></a><em> how to get the most out of their systems. My areas of expertise are fire jumping and forensics — call when there’s an unsolvable problem — and the design and management of large, integrated, and complex data systems in highly constrained situations.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bb028d883149" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Auto-copy a File to a Rich Text Field with FormAssembly]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-auto-copy-a-file-to-a-rich-text-field-6664d21f455d?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/6664d21f455d</guid>
            <category><![CDATA[formassembly]]></category>
            <category><![CDATA[salesforce-for-nonprofits]]></category>
            <category><![CDATA[salesforce]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Thu, 13 Oct 2022 00:44:44 GMT</pubDate>
            <atom:updated>2022-10-23T21:01:18.670Z</atom:updated>
            <content:encoded><![CDATA[<h4>Option 1 of 2 — using FormAssembly to upload the file</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MVLVJljDBjd7ZxmLi6NveQ.png" /><figcaption>Need to have your student or participant headshots appear automatically in a rich text field? Easy!</figcaption></figure><p>We often have clients who want photos uploaded to Salesforce and put directly into a rich text field. You know of course that you can just drop a photo from your computer into a rich text field, and you may know that you can attach a file, open it in preview, copy it, and paste it into a rich text field (this is browser dependent).</p><p>But what if you need to capture, say, a student’s photo on their Contact record? What if the student is responsible for uploading that photo as part of onboarding?</p><p>Here’s a simple step-by-step to getting that done. You can upload the file however you want, of course, but FormAssembly is our go-to for all things forms-and-Salesforce.</p><h4>Example Use Case and Solution Overview</h4><p>For this article, we tackle a very common request from our clients: getting an external party (say, a student applicant to a youth program) to upload their own photo and displaying that photo in a rich text field for internal users to view and use.</p><p><strong>Solution Overview:</strong></p><ol><li><a href="#dee0">Prepare the Contact record with a Rich Text field to display the photo and a field to trigger the Flow</a></li><li><a href="#f2f2">Create a Flow that responds to a change on Contact to populate the Rich Text Field</a><br><a href="#e5f9">(2a) Create a new flow on Contact</a><br><a href="#77d9">(2b) New Resource-&gt;Variable: TypeOfFile</a><br><a href="#580b">(2c) New Resource-&gt;Formula: DocumentURL</a> <br><a href="#3f38">Getting the URL for the Image tag</a><br><a href="#d7f1">(2d) Decision: Photo ID Is Not Blank</a><br><a href="#0c81">(2e) Get Records on the “Yes” path from the decision: Find CV Data</a><br><a href="#c008">(2f) Update Record: Update Photo</a><br><a href="#f31c">(2g) Save and Activate Your Flow</a></li><li><a href="#b257">Create the FormAssembly form to capture the file upload</a></li><li><a href="#8c38">Create the FormAssembly connector to store the information in Salesforce</a><br><a href="#e32a">(4a) Create new ContentVersion Record</a><br><a href="#9708">(4b) Update the Contact Record to store the ContentVersion ID</a></li></ol><h4><strong>(Step 1) Create 2 new fields on your object.</strong></h4><ul><li>A text field, 18 characters long, to contain the ID of the file you will upload. I called mine “Photo CV ID”</li><li>A rich text field to display the photo itself. I called mine “Photo” and just took the defaults for a rich text field.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ttg1JNVCFMi0sJ2KbXO7LQ.png" /><figcaption>Two new fields — one for the ID, one rich text, on your target object</figcaption></figure><h4>(Step 2) Create a Flow to respond to your ID field and populate your Photo field</h4><p>My flow is simple, for demonstration purposes. It starts by asking if the Photo CV ID is not null but the Photo field is. If that’s the case, it goes off to find the file type from the Content Version (using the ID in the Photo CV ID field) and then updates the Photo field with a formula that brings it all together.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*onsLCuVXdRDOau09dmtdmg.png" /><figcaption>A simple Flow that asks if the photo ID is available, and if so, finds the other data needed and updates the rich text field.</figcaption></figure><p><strong>(2a) Create a new flow on Contact<br></strong>Or modify an existing one. Please note this tutorial assumes you are familiar with creating and editing flows and will not go into great detail on how to create, edit, save or debug them, sorry! If you’re less familiar with Flows, you may want to take a look at the <a href="https://deepwhydesign.medium.com/salesforce-obscura-auto-copy-a-file-to-a-rich-text-field-with-flows-bb028d883149">companion piece</a> to this article that goes more in depth on each of the steps in building a flow.</p><p><strong>(2b) New Resource-&gt;Variable: TypeOfFile<br></strong>Create a new Variable. It should contain Text, and I have called mine TypeOfFile.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Hn0jCrJrR4DXsTWGCi39Aw.png" /><figcaption>Create a new variable to store the file type.</figcaption></figure><p><strong>(2c) New Resource-&gt;Formula: DocumentURL<br></strong>This is where you construct the actual text to go into your Rich Text field. It is an HTML image tag that references the content version ID and the type of file (variable you created above).</p><p>The formula is of type Text and should be similar to the below. I have called my formula DocumentURL.</p><p>IMPORTANT NOTES:</p><ul><li>If you just copy/paste the formula below, make sure you adjust {!$Record.Photo_CV_ID__c} to match your actual custom field name</li><li>Replace REPLACEME below with your actual domain. From any record page, just grab the part of the URL before “.lightning.force.com”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/1*CvJa1MBF5voKJGv8UlVUIg.png" /><figcaption>The part in red is what I would put in place of REPLACEME below. Your URL is unique to your Salesforce instance.</figcaption></figure><p>‘&lt;p&gt;&lt;img src=”https://REPLACEME.file.force.com/sfc/servlet.shepherd/version/renditionDownload?rendition=ORIGINAL_&#39;+{!TypeOfFile}+&#39;&amp;amp;versionId=&#39;+{!$Record.Photo_CV_ID__c}+&#39;&amp;amp;operationContext=CHATTER&quot; alt=”Photo” height=”200&quot; width=”200&quot;&gt;&lt;/img&gt;&lt;/p&gt;’</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1_t3ojD3y1FQVhZ_rKzfEQ.png" /><figcaption>My formula edit — showing the data type of Text and the way the formula looks in the Resource editor.</figcaption></figure><p><strong>IMPORTANT: Getting the URL for the Image tag<br></strong>The URLs for Salesforce resources change periodically. In fact, between publishing this article for the first time and the update it changed from documentforce.com to file.force.com.</p><p>You may find that this formula works great for awhile and then suddenly stops working, in which case I’d immediately look to the URLs.</p><p>To get the URL currently in use for the photo previews, I:</p><ol><li>Manually upload an image file to a Contact record</li><li>Click on the file to open it in the File Preview. Right click (command+click on mac) and copy the preview to my clipboard.</li><li>Paste from my clipboard into a Rich Text field on Contact and save my changes.</li><li>Use dataloader.io to export only the record I’m testing with, and only my rich text field. The html for the working, pasted image will show up in plain text in the export, allowing you to mimic it in this formula field.</li></ol><p><strong>(2d) Decision: Photo ID Is Not Blank<br></strong>Add a new Decision to start your flow. I have called mine <strong>Photo ID Is Not Blank</strong>. If the record’s Photo CV ID field is not empty but the Photo field is empty, we return a “Yes” decision, which is what tells us we need to take the next step.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PMQYrxpqjOBWW83Dq2a5ew.png" /><figcaption>$Record refers to the field on the Contact record that started this flow. We are testing whether our key fields are blank or not, to know if we need to take the next step.</figcaption></figure><p><strong>(2e) Get Records on the “Yes” path from the decision: Find CV Data<br></strong>Add new Get Records action to the flow on the “Yes” path out of the decsion created above. I have called mine <strong>Find CV Data. </strong>The purpose of this is to step is to use the ID of the Content Version record to obtain the type of file so that its preview displays properly. The field FileType on the Content Version record will be stored in the variable we created in step 2b.</p><ul><li>Get Records of this Object:<strong> </strong>Content Version</li><li>Filter Content Version Records: Where ID equals $Record-&gt;Photo CV ID</li><li>How many records to store: Only the first record</li><li>How to store record data: Choose fields and assign variables (advanced)</li><li>Where to store field values: In separate variables</li><li>Set your variable to store the value FileType in your variable TypeOfFile</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nuvNnu6GR81iqYXV3HyR5Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*baTIyq733Gr03PYqqh5ThQ.png" /><figcaption>Get the Content Version record and store its FieldType in your custom variable.</figcaption></figure><p><strong>(2f) Update Record: Update Photo</strong></p><ul><li>Use the record that created the flow</li><li>Set Filter Conditions: None — Always Update The Record</li><li>Set Field Values For the Record: Set your rich text field equal to the formula field you created in (2c)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D_ljTN3ZviaNiPZQRbOyYw.png" /><figcaption>Update the instantiating record’s rich text field with the formula</figcaption></figure><p><strong>(2g) Save and Activate Your Flow<br></strong>At this point you may want to test the flow by manually putting an ID of a file into the the Photo CV ID field, but you have to make sure to get the Content Version ID (not any of the other IDs that come along for the ride with Files).</p><h4>(Step 3) Create your FormAssembly Form</h4><p>For this example we have a very simple form that has a file uploader and a hidden Contact ID field. We would prefill the Contact ID field with the Contact ID from Salesforce but you can also put enough information to look up the Contact. As with Flows, this tutorial isn’t really able to go too far into exactly how to set up a FormAssembly form — the goal is just to ensure you can attach a File to the right Contact and get the ContentVersion ID.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uX0Iav8RbOweDuCAk0yr-A.png" /><figcaption>Our simple form has a File Uploader and a hidden Content ID field.</figcaption></figure><h4>(Step 4) Create your FormAssembly Connector</h4><p>Connect your FormAssembly form to Salesforce with an After Form Submitted connector. You will first create the file upload, then use the ID from the uploaded file to fill in your Contact field.</p><p><strong>(4a) Create new ContentVersion Record<br></strong>Use the FormAssembly <a href="https://help.formassembly.com/help/sending-an-upload-to-salesforce-files">step-by-step instructions</a> on uploading a file to save your file to Salesforce. Your hidden Contact ID field will be used to find the record to attach the file to.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vrdF8PqqB3IOH1ROxDHkPw.png" /><figcaption>In FormAssembly connectors, create a new ContentVersion record.</figcaption></figure><p><strong>(4b) Update the Contact Record to store the ContentVersion ID<br></strong>Look up the Contact ID in whatever way you normally do. For this example we are just using the hidden field we would have pre-populated via the URL.</p><p>Set your Photo CV ID field you created in step 1 to “the ID of an object above” so that it is 1. ContentVersion.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HtXKhepyEdo-IdhiQ5qh_g.png" /><figcaption>Set Photo CV ID to the ID of the object you created in step (4a)</figcaption></figure><h4>(Step 5) Save your form and test it all out</h4><p>Upload a file to your FormAssembly form, and you should get:</p><ul><li>A File attached to the Contact record for which you provided the prefilled ID</li><li>A value in the Photo CV ID field</li><li>A 200x200 view of your file in your rich text Photo field</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5W1lu05HTP9u93HigQVCSA.png" /><figcaption>Pictured above: The attached file, the Photo CV ID and the uploaded photo in your rich text field</figcaption></figure><p>Congratulations! You’ve just upload a file and put it into a rich text field! Have fun!</p><p><em>My firm (</em><a href="http://deepwhydesign.com/"><em>Deep Why Design</em></a><em>) specializes in weird use cases and things nobody else wants to touch — from big and innovative shared platforms and custom solutions to </em><a href="https://www.deepwhydesign.com/courses-and-training"><em>teaching accidental admins</em></a><em> how to get the most out of their systems. My areas of expertise are fire jumping and forensics — call when there’s an unsolvable problem — and the design and management of large, integrated, and complex data systems in highly constrained situations.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6664d21f455d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Nonprofit database implementation: what most firms still gets wrong (and how to fix it).]]></title>
            <link>https://deepwhydesign.medium.com/nonprofit-database-implementation-what-most-firms-still-gets-wrong-and-how-to-fix-it-711f22147da9?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/711f22147da9</guid>
            <category><![CDATA[nonprofit-technology]]></category>
            <category><![CDATA[nonprofit]]></category>
            <category><![CDATA[enterprise-technology]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Sat, 09 Apr 2022 17:15:59 GMT</pubDate>
            <atom:updated>2022-04-09T17:15:59.658Z</atom:updated>
            <content:encoded><![CDATA[<h4>Teach first. Teach continuously. And for the love of everything do not ask your client to fill out a spreadsheet template for a data migration unless you hold their hand every step of the way.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/675/1*kiYs5qXq2DTmNS2221qrYQ.jpeg" /><figcaption>A globe and boxes to set the stage for our parable</figcaption></figure><h4>The Parable of the Client</h4><p>I want you to imagine that someone — let’s say your boss — has declared that you are moving to a foreign country in four weeks. And you’ll have a few people around to help you with the move. And don’t worry that you don’t speak the language, that’ll all get sorted out when you get there.</p><p>Keep imagining that you don’t immediately look for another job.</p><p>You’re introduced to John, your project manager for the move. John gives you a timeline that is mostly written in the language of the country you’re moving to. You have to ask John to translate, which he sort of does. Your timeline, it seems, includes buying a house, packing up and shipping your belongings, and unpacking them in your house.</p><p>You ask about the culture in your new country, the food, and the location of the house. You want to know whether you’ll need a car or bike. You are understandably concerned about selling your existing house and car because you clearly can’t take those with you. You also start to wonder about whether you’ll need adapters for all your electronics or if you just buy new ones there.</p><p>John tells you the location is convenient, the food is filling, and the rest of the questions will either be answered when you arrive and are given a week of language instruction, or are out of scope. But he’ll ask Jane (your move specialist) about the adapters.</p><p>Jane is from the country you’re moving to. She has never needed adapters in her country. She struggles to answer your questions, but seems nice enough.</p><p>John and Jane give you a template — also written in the language of the country you’re moving to — and ask that you inventory all of your belongings. If you miss any belongings, they won’t get moved and you’ll bear the cost of replacing them. You aren’t sure what you need to take, so you try to ask Jane and John. They give a few obvious examples, like your clothes and toothbrush, but can’t answer any questions about dishes or electronics or furniture. It’s entirely your responsibility. You have 3 days to fill out the spreadsheet.</p><p>It takes you more than 3 days to fill out the spreadsheet. John’s role now seems to be to nag you to fill out the spreadsheet and includes being very grumpy that you’re late on filling out the spreadsheet. Your blood pressure is up and you are stressed beyond belief. Your spouse is begging you to quit this stupid job…but this is a parable, so you don’t.</p><p>Once you have completed the spreadsheet of your belongings, Jane asks you to provide detailed instructions on where those belongings go in your new house. Your new house you’ve never set foot in. Your new house you might have seen some photos of once at the start of the project and haven’t really seen since.</p><p>To help, Jane gives you a floor plan of your new place, with all of the rooms labeled in the new language. You are told not to worry too much about it, since you’ll get a week of language instruction after you move.</p><h4>Technology firms do this to clients all. the. time. and it’s horrible.</h4><p>Enterprise technology firms seem to focus on doing the move, and anything else is somebody else’s problem. The people doing these moves know how to execute on the steps of the move — they are experts in that part of the work. To them, these steps are no big deal.</p><p>A migration or implementation is, at best, annoying and disruptive to the client. Usually it’s annoying, disruptive, intensely time consuming, expensive, and very, very frightening. Your software might be “intuitive,” but it isn’t intuitive to the client <em>yet</em>. They’ve never done this before.</p><p>I have lost count of the number of rescue projects I’ve been dragged into, either right at the end of an implementation or after an implementation left everybody unhappy. Nearly all from brand name firms — or worse, volunteers from for-profit firms — that think an implementation is about technology. They do exactly what “John” does in the parable — give a timeline, talk about what’s in or out of scope, and fob off detailed questions to “Jane” who has only technical answers to provide.</p><p>Those implementations are almost never problematic where the tech is concerned, but they typically leave a big mess in their wake where humans, data, and business processes are concerned. I think this is allowed to continue because you can’t deny that the technical piece is correct, and everything else is somebody else’s problem. You didn’t know you needed training and process consulting and change management? Well, that’s your fault.</p><h4>“Assume” — it make an ass out of you and me</h4><p>I have worked with many folks at these firms who are very skilled and competent. Empathetic. Caring. Wanting very badly to make a difference. They could be in other places, but they choose to spend their time making less money in the nonprofit sector.</p><p>So why are so many tech projects so like our parable? I think that it’s because most consulting firms in our space are modeled directly on consulting firms from corporate and industry, or are just arms of corporate-focused firms.</p><p><strong>Industry consulting is based on an assumption set </strong>that is simply not true in the nonprofit space. These assumptions can be boiled down to:</p><ul><li>The buyer has the technical expertise to make a well-informed decision about the chosen product.</li><li>The buyer has the staffing and skillset to be able to absorb the business process, training, and change issues that are part of every tech project.</li><li>The buyer knows that they must plan for and budget for business process, training, and change management to accompany a tech project.</li><li>The buyer is staffed and has budgeted to absorb all of the risks related to a change of this magnitude, and is hiring the technology firm solely to provide the niche technology expertise that the buyer lacks in-house.</li></ul><p>None of this applies to the majority of nonprofits. There are a whole host of reasons this doesn’t apply and if you’re really curious you can read all about <a href="https://deepwhydesign.medium.com/the-nonprofit-operations-guide-introduction-d30d7bf89c5a">operational capacity, operational maturity</a>, and the <a href="https://ssir.org/articles/entry/the_nonprofit_starvation_cycle">nonprofit starvation cycle</a>.</p><h4>Fine. It sucks. How do we fix it?</h4><p>I was talking with a colleague about how to fix all of this. This colleague is in one of the firms I’m railing against, so I can’t name names.</p><p>Literally nobody wants it to be like this — not the clients, not the firms, and not even me. If I never rescued another implementation again my life would be phenomenally good.</p><p>A big part of the solution, I think, is actually really easy to shift into.</p><p><strong>First, we make nonprofit-appropriate assumptions </strong>when we structure our project plans. We assume that, in the nonprofit space:</p><ul><li>The client does not and cannot be expected to have in-house the expertise and resources to handle the corresponding change management, training, and long-tail support that goes along with a major tech implementation.</li><li>This is likely to be the biggest, most disruptive, and riskiest thing the client has taken on in a long time. They’re putting a lot of trust entirely in an outside firm, so a partnership approach is more warranted than a niche-tech-skills approach.</li><li>Just because they chose your solution doesn’t mean they have any practical idea of what it <em>does</em> or how it applies to their use cases.</li></ul><p>Nonprofit folks are entrepreneurial, energetic, inventive, willing to do whatever is needed to make the project work, and willing to adapt. But <strong>they don’t have enough information to do those things based only on sales materials, a single orientation, and a training that comes <em>after</em> the important decisions</strong>.</p><h4><strong>The answer is to teach continuously throughout the project.</strong></h4><p>Teach them <em>before </em>asking them to prepare any data. Teach them <em>while</em> they’re preparing their data. Teach them <em>after</em> you have migrated their data. Teach them during the sales process that they need teaching as part of the implementation. Teach them during weekly meetings by hearing their questions and showing them or letting them get hands on.</p><p>Adults learn by repeated exposure to a new system, stretched over time and illustrated through multiple real-world use cases. A single training sets the context but is insufficient to let people apply that to their own situation.</p><p>Training is not teaching. Training is more generalized, more structured, more “the decision has been made.” Offer a training FIRST before the project starts, to orient people to their real future lives. Offer a training LAST, with the manual updated to the specifics of the client’s implementation if needed, to re-enforce the final decisions made during implementation.</p><p>Teaching comes in the middle, and teaching adults is really all about translating. The teacher on a project like this should be someone who has mastered the solution (is a resident of the destination country, from our parable) who <em>also</em> has mastered the content that the solution supports. If you are implementing an accounting system, the teacher should know the technical system inside and out and <em>also</em> know accounting as a practitioner, so that when the client asks about a specific accounting question, the teacher can hear, understand, and translate for the student into the new system.</p><p>Hire project managers and incentivize them to teach and translate, so the client understands why the scope is what it is.</p><p>Or just hire teachers and work the teaching into the project budget. Whatever it takes to actually support the client through the process.</p><p><strong>That’s it. That’s all it takes.</strong> Empathy for the gap between a technology solution and the knowledge of the people who have never used that technology before, and a willingness to support those folks in bridging that gap. We have plenty of this in the nonprofit tech sector, but we still haven’t made that pivot to fix the way we do projects. With so much demand for technology projects in our sector, the time is now to change how we do things, and to change for the better.</p><h4>A word to clients</h4><p>If you’re reading this and recognizing your project, advocate to your consulting firm to incorporate more teaching — not training — into the work.</p><p>And if you’re reading this and thinking “I just need to find a technology to fix my current situation, and a migration shouldn’t be as hard as you’re making it out to be” I would encourage you to reflect for a moment on all of the operations that technology supports. You cannot change technology without changing process. The most successful technology projects are partnerships between the client and the consulting firm, and that takes time and energy. Take a moment and see if there’s room in your organization for the change management and process evaluation that is required for a successful technology migration before embarking on one, and you will be more likely to succeed with whatever path you choose.</p><p><em>My firm (</em><a href="http://deepwhydesign.com/"><em>Deep Why Design</em></a><em>) specializes in weird use cases and things nobody else wants to touch — from big and innovative shared platforms and custom apps to teaching accidental admins how to get the most out of their systems. My areas of expertise are fire jumping and forensics — call when there’s an unsolvable problem — and the design and management of large, integrated, and complex data systems in highly constrained situations.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=711f22147da9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Salesforce Obscura: Salesforce + Marketing Cloud = No Exit from the Journey]]></title>
            <link>https://deepwhydesign.medium.com/salesforce-obscura-salesforce-marketing-cloud-no-exit-from-the-journey-ad3cc4b5038f?source=rss-f8ef453ff045------2</link>
            <guid isPermaLink="false">https://medium.com/p/ad3cc4b5038f</guid>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[saleforce-marketing-cloud]]></category>
            <category><![CDATA[journey-builder]]></category>
            <category><![CDATA[marketing-cloud]]></category>
            <dc:creator><![CDATA[Jenn Taylor]]></dc:creator>
            <pubDate>Sun, 16 Jan 2022 17:43:31 GMT</pubDate>
            <atom:updated>2022-01-16T17:44:33.479Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>The Problem You Need To Solve<br></strong>When a new Lead is created in Salesforce that is tied to a specific type of interest (e.g. from a specific landing page), it should immediately enter a Journey (in MC). This same Lead should exit prior to the journey’s end if it converts or is marked as not pursuing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*TKFSsciMGkT7v_a0ahnuug.jpeg" /><figcaption>No Exit from an MC Journey when the entry point is Salesforce? Read on.</figcaption></figure><h4>The Pain</h4><p>This one should be in the category of “if you only do one thing in Marketing Cloud THIS is what you do.” Which should be in the category of “here is a handy step by step guide for people who don’t know Marketing Cloud because this task is super simple.”</p><p>If it is in this category, I couldn’t find that guide. Instead, I invented new swear words while watching my blood pressure rise. So I’m writing it here in case it prevents you from spending days shouting at cryptic and only partially helpful pieces of answers on the internet.</p><p>The good news is that the steps to make this work are mostly super easy once you know what to do.</p><h3>Quick Look: The Steps To Accomplish This</h3><p>If you already know how to do each of these steps, then the hard part is figuring out which steps and in what order. The steps are listed here.</p><p>The rest of this article identifies some Concepts and Lessons Learned (high level stuff that helped me understand what was going on) and then goes into the Step by Step Setup that contains exhaustive instructions and screenshots.</p><ol><li>Make sure the Salesforce Leads object is part of your MC Synchronized Data Extensions, and make sure you’re bringing in the fields you care about for your exit criteria.</li><li>Create a new, empty Data Extension to hold a copy of the Lead data that will be used for evaluating your exit criteria.</li><li>Create a new Attribute Group based off of the DE you created in step 2 above. Make sure you’re linking on Lead ID and it’s a one-to-one relationship.</li><li>Use Automation Studio to copy data from the MC Salesforce Leads (synchronized) table into your new DE.</li><li>Set up your Journey to have Salesforce Data as the Entry Source, to let the Lead come in immediately. Ensure you have Lead ID as the key.</li><li>Create a Decision before each step in your journey to evaluate if the journey contact is found in your new DE, and if the criteria indicate that person should exit or not. You cannot use your new DE to set Exit Criteria, but you can compare the data in it with data in your entry event to ask if <em>this specific person</em> should continue or not.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LMEy6imC-oByAbXk2U56Zw.png" /><figcaption>A diagram representing how things are connected between MC and SF, linked to the steps above</figcaption></figure><h3>Concepts and Lessons Learned</h3><p>This is subject to change as Salesforce decides where it wants to go with Marketing Cloud data integration. As of early 2022, the following concepts are key to handling a journey exit when the entry point is a Salesforce record. <strong>Skip ahead to the next section if you’re just here for the step-by-step guide.</strong></p><ol><li>Marketing Cloud only reads data in real time from Salesforce under very few, very specific circumstances. In a Journey, that circumstance is when you choose the Salesforce Data Entry Source. It does <strong>not re-evaluate the record after that record is on its Journey. </strong>So if you read about setting exit criteria and how a journey contact is automatically evaluated before every journey step if you have a goal or exit criteria set? Yeah, joke’s on you if you use a Salesforce Data entry source! That only works for custom Data Extensions.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/672/1*LxiO3gIJZcR1J7OFgzjIJA.png" /><figcaption>The Salesforce Data Entry Source in Marketing Cloud operates in real time. You can think of it like a trigger in Salesforce that performs an after insert or after update action, but that action injects the contact into an MC journey.</figcaption></figure><p>2. In MC’s Contact Builder, the Synchronized Data Sources are going to make a <strong>copy</strong> of Salesforce data in MC. That alone is not unusual, but what is unusual is that you can’t work with the Synchronized Data Source like you can work with any other Data Extension. You have to <strong>copy parts of the copy</strong> into a local Data Extension to be able to work with it properly. <strong>You will need to automate this copying via the Automation Studio </strong>— details of these steps are outlined below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QnmbNiMmk4PXTFJDVkGlwA.png" /><figcaption>If you’re not sure what I’m talking about, go to Audience Builder-&gt;Contact Builder-&gt;Data Sources-&gt;Synchronized as pictured in the image above. You should see your SF org with the number of sync’d objects and the number of fields across those objects. You can click on the box that says “Entities | Attributes” to get into your sync configuration.</figcaption></figure><p>3. The <strong>synchronized data sources can’t be removed</strong> (*). Once you’ve synchronized an object, unless you <em>completely disconnect</em> your Salesforce, you’re stuck with the object. Basically <strong>just sync only the objects you absolutely, 100% need, and of those only the fields you absolutely, 100% need</strong>. You can always add more later.</p><p>&lt;rant&gt;There are so many things in MC where the official guidance is “well don’t screw it up on initial setup or you’ll hate everything forever ¯\_(ツ)_/¯” that it blows my mind, and I have dealt with some truly dreadful enterprise systems in my time. I wasted soooo much time thinking the can’t-remove-synchronized-data-sources issue couldn’t possibly be true and looking for the real truth. Friends, it is true, so just move on.&lt;/rant&gt;</p><p>4. It is probably easiest to think of your Salesforce Data Entry Source as Table A, and the copy-of-a-copy Data Extension where you will get your exit data from as Table B. These tables will contain similar — if not identical — fields, and you can specify the foreign key between them (put another way, ID in Table A is the same as ID in Table B). <strong>To set up a Journey exit for our specific use case (**), you must compare data in these two tables, since you can’t compare Table A directly with synchronized Salesforce data. </strong>Also important to understand:<strong> table A never refreshes, </strong>so the data remains forever as it was when the person entered the journey,<strong> and table B only refreshes if you make it refresh with Automation Studio</strong>.</p><p>5. You know this one already but it’s worth mentioning — <strong>“Contact” in MC does NOT match the Contact object in Salesforce</strong>. In MC, Contact is a single, central database table containing one row per individual human being, and you can populate this with any information from any source. As an example: first name, last name, and email from the Salesforce Contact object will be stored in the MC Contact table, <em>and also</em> those same fields from the Salesforce Lead object will be stored in the same MC Contact table. For the purposes of this explainer, it’s fine to think of MC’s Contact and MC’s Subscribers as basically the same thing, just know that distinction probably matters somewhere I just haven’t found yet.</p><p>6. <strong>An Attribute Group in the Data Designer is a way to <em>extend</em> the definition of the MC Contact record</strong>. So whatever data storage you’ve got set up — and all of the Data Extensions are basically just database tables — you will link to the main Contact record. In Salesforce, you’d just go into the object manager and add fields on the object you want to extend; in MC, you create a supporting database table and link them.</p><p>—</p><p>(*) Synchronized data sources cannot be removed. But you can pause them, remove all the data from them, and change their fields. So you’re stuck with a messy screen but you do have some ways to at least mitigate data sync mistakes without blowing up your whole, entire Salesforce connection.</p><p>(**) The use case we need very specifically is to bridge the gap between the immediate nature of the Salesforce Data Entry Source and the data stored in traditional MC Data Extensions, which is what we outline below. If you <em>start</em> the journey from a standard MC Data Extension, exit criteria work just fine to re-evaluate before every step in the journey. But then you are dealing with lag from lead creation to first step on the journey, and most of the time that’s unacceptable. Put another way, if you use MC the way MC was designed, it does work more or less as documented. When you toss SF real-time stuff into the mix, things get kinda painful.</p><h3>From Concept to Action — Step By Step Setup</h3><p>These steps will allow you to: (1) Have data from Salesforce drop someone onto a journey immediately and (2) have that person exit the journey when their Salesforce data is updated.</p><p>I will be using Lead and Lead Source as the key data points throughout this example. You can do the same thing with Contact or any other object you need, as long as you can specify how your custom object links to your Salesforce Data Entry source.</p><h4><strong>Step 1: Set up your Salesforce Lead object as a Synchronized Data Source</strong></h4><p>This is the only step where I’ll point you to the <a href="https://help.salesforce.com/s/articleView?id=sf.mc_co_connect_the_clouds.htm&amp;type=5">documentation</a> — if you haven’t already connected your Salesforce and Marketing Cloud instances via the Salesforce Connector, then this article won’t help you and you need to go do that first.</p><ol><li>In your top navigation in MC, hover over Audience Builder and choose Contact Builder.</li><li>Once you’re in Contact Builder, click on Data Sources, then click “Synchronized”</li><li>Click anywhere in the box that appears that shows your SF Org’s name and looks like the screenshot below (except probably with fewer “Entities” (objects) and “Attributes” (fields).</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QnmbNiMmk4PXTFJDVkGlwA.png" /><figcaption>Click anywhere in your version of the box that says “Entities | Attributes” to go to the sync setup.</figcaption></figure><p>4. If you don’t see Leads in the screen that appears next, then you’ll need to set it up. If you do, then you may want to check and confirm that all of the fields you need for your Journey exit decision (Lead Source and Lead Status, in this example) are mapped.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*56oR4OxX-3CAju2Q-Jkkig.png" /><figcaption>An example of Synchronized Data objects. The Set Up Object button is highlighted in this image. You can see that I already have Lead synchronized in this org — if you don’t, click the button.</figcaption></figure><p>4a. <strong>Set up Leads: Only if you don’t see Lead under Synchronized Entities. </strong>Click on “+ Set Up Object” then search for Lead. Click the “Synchronize” button inside the box that is labeled “Lead.” On the next screen, select the fields to synchronize — note that it’s best to just select only the fields you know for sure you need. So you may only sync a few. In this example we would be sure that Converted, Lead Source and Status were selected, along with First Name, Last Name, and Email and anything that is the sync pulls in automatically. When you’re done with this, you do NOT need to do step 4b.</p><p>4b.<strong> Confirm Lead Data if Already Sync’d</strong>: If you already have Lead sync’d (and skipped step 4a) you may just want to double check that the necessary fields are already configured. Click on Lead (where the image above has a rectangle around it) to go into the object configuration screen. Review your synchronized fields (in this example, make sure you see IsConverted, LeadSource and Status in the mapped fields, and add them if you don’t see them).</p><p>5.<strong> Note the poll schedule</strong> — whether you just set up Leads to sync, or whether you already have it sync’d, it’s important to know how long the gap is between refreshes. Remember that the synchronization is <strong>copying data from Salesforce into MC on the schedule defined by the poll sync. </strong>So if your poll schedule is 15 minutes, you’ll have up to a 15 minute lag between a record getting created or updated in SF and having its info refreshed in MC.</p><h4>Step 2: Create a new, empty Data Extension to hold a copy of the data you just sync’d in step 1</h4><p>Yep, you read that right. You’re going to copy the copy, or at least parts of the copy. First we have to set up the container for that copy.</p><ol><li>In your top navigation in MC, hover over Audience Builder and choose Contact Builder.</li><li>Once you’re in Contact Builder, click on Data Extensions then click “Create”</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Cp7x540WEibGsPlzf-S-AA.png" /><figcaption>The Data Extensions menu in MC</figcaption></figure><p>3. Give your new Data Extension a name that lets you find it, and a description. The example below is a little generic, in real life I have it specific to the journeys and business units it supports. Check “Is Sendable?” and (if appropriate) “Is Testable?” then click Next.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M_3zKxgsU1TQ6tRs8zRS6g.png" /><figcaption>Screen one of Create New Data Extension. This screen just gives it a name and some basic settings.</figcaption></figure><p>4. It is a very good idea to set retention settings, so that data doesn’t stick around forever. If you’re not sure right now, you can always adjust this part later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CUhXGK8ToJ_uzYjO034c0A.png" /><figcaption>For my real use cases, I need the information for a bit longer than the journey duration. So I set the retention accordingly. But this part I’m not 100% sure about yet, so if it turns out to be a Poor Life Choice, I’ll update this article.</figcaption></figure><p>5. Finally we get to the important step — setting up the fields for this Data Extension. This is exactly like setting up a new database table (or new object in Salesforce, except that in MC you must also specify which field contains the ID).</p><p>For this example, you will absolutely want to have an ID field that can hold the Lead ID (or the ID from whatever object causes you to enter the Journey). You will probably also want the Email address. Lead Status, Lead Source, and Converted are all important for our exit criteria so we create fields for those. Be careful to match your data types and field lengths to accommodate your actual Salesforce data. For example, Lead Status and Lead Source are picklists in Salesforce, but MC doesn’t have picklists. So we set up the Data Extension field as Text and make sure to increase its size from the default of 50 characters to the 255 max that would be the limit of a Salesforce picklist value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ahQBEeWNZUfJcsR-qGe21Q.png" /><figcaption>The full specification of the Data Extension for this example. It contains only the fields we absolutely need, and the data types and lengths have been adjusted to match whatever we expect to come from Salesforce.</figcaption></figure><p>6. Notice the “Send Relationship” — this is the field that will link the data in this Data Extension back to MC’s single, central record for a person (see the explainer in the Concepts section if this makes no sense). Your system will differ from mine — some organizations use email, and some use the subscriber key (lead or contact ID, roughly). Choose the right one for you and then click “Complete.” (note: if you’re not sure, choose Lead Id as the most likely starting place).</p><h4>Step 3: Connect your (empty) Data Extension to the MC Contact via an Attribute Group</h4><ol><li>Still in Contact Builder, click on “Data Designer”. You will see a big circle that says “Contacts” in the middle, with other circles sticking out from it. The other circles are basically <em>extending</em> the Contact record.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*2qN06rsJd1jXst7Coe5WEQ.png" /><figcaption>The Data Designer screen, image taken from Salesforce Trailhead</figcaption></figure><p>2. Click +Create Attribute Group. Give this new attribute group a name and choose an icon (doesn’t really matter which one, it will show up in the blue part of the circle on the Data Designer screen). Click “Create”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*MaM9DfQNXAzWPT6OnSbvaQ.png" /><figcaption>The screen that appears when you create a new Attribute Group in Marketing Cloud.</figcaption></figure><p>3. Click “Link Data Extensions” from the next screen. We are going to link this attribute group directly to the Data Extension we created in step 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/684/1*eQFCSWzCkLx-DOGJfVX6JQ.png" /></figure><p>4. Ignore what’s on the left-hand side of the screen for a minute. Navigate to the Data Extension you just created in step 2. It is probably in the “Data Extensions” folder, so click that on the right-hand side to see all of your DEs. Click on the one you created to select it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uatv_Bbs_7J6D8-nlF_smQ.png" /><figcaption>Navigate to your new DE. Ignore the left hand side for a minute.</figcaption></figure><p>The next step is how you tell MC how your custom Data Extension relates to the core Contact record. Remember that, thanks to step 1 in this explainer, Salesforce is syncing all of your Leads already. The Salesforce Connector will put the Lead ID and other data into the MC Contact table automatically, and it links Lead ID to Contact Key. We want to do the same thing with our Data Extension — we are linking our custom data table to all these other things with the Lead ID. It’s not that different than setting a lookup in Salesforce or specifying a foreign key in another database.</p><p>5. Click on “Contact Key” in the left-hand side. This selects it as the way you will connect your custom data to the core data set. Then click “Lead ID” in the right hand side, to make the link. You also want to make sure that the drop-down in the upper right that says “One” in the image says “One” on your screen. This drop-down specifies that this data extension exists in a one-to-one relationship with the master data list. Basically when we do a lookup in this table with an ID, we will only ever get one single record, and that’s exactly what we want. Click “Save.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PxdlX5UxJhvAdrUAFmWHng.png" /><figcaption>This screen shows a correctly configured one-to-one link between our new data extension and the core Contact list, linked on Lead ID as the unique identifier.</figcaption></figure><p>7. After you save, you will land on a screen that looks like the one below. You can come back here to edit the relationship or name of the attribute group if you need to later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E9bWgewTcp_TMslkH97-QQ.png" /></figure><h4>Step 4: Copy your synchronized Salesforce data into your customized Extension</h4><p>You have just sync’d data from Salesforce, created an empty storage container, and defined how you want to use that container in your MC data architecture. Now you need to use Automation Studio to copy the data from your synchronized data set (which we can’t use the way we need to) to your data extension, which is the last step before we can use it in Journey Builder.</p><ol><li>Create a new SQL Query activity in Automation Studio.</li></ol><ul><li>Go to Automation Studio (I get to mine via Journey Builder but apparently you can access it other ways).</li><li>Choose “Activities” in the top nav.</li><li>If you want, navigate to your SQL Query folder on the left-hand side.</li><li>Click “Create Activity”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*A4JzrXKgxJZlaTo95L3PEw.png" /><figcaption>Your screen should look more or less like the above if you’re in the right place for this step.</figcaption></figure><p>2. Click on “SQL Query” to choose it then click “Next”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZPBOrIEfnU3DxSxuIy2D-w.png" /><figcaption>When you click to choose the type of activity, it will turn a pale blue with a faint checkmark.</figcaption></figure><p>3. Give your query a name and a description, then click “Next”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ck7k0JoNr9QnR1GUOOCfig.png" /><figcaption>The description says: This query will copy data from the Salesforce Synchronized Lead DE into the Leads for Exit DE for use in journey builder.</figcaption></figure><p>4. Compose your query. Read this next bit carefully, as there are a lot of caveats and this is where a bunch of pieces link together.</p><ul><li><strong>This is the step where you map the fields from the synchronized data into your custom DE. </strong>There is no other place to do this, you do it directly in the SQL using the syntax “select source_fieldname as destination_fieldname”.</li><li><strong>You will not find your Salesforce DEs available in the left-hand menu </strong>where you would normally look for help on what you are selecting from. You can reference your custom DE from this menu.</li><li><strong>MC always names the synchronized objects with the pattern [objectname]_Salesforce.</strong> So in our case we will select from Lead_Salesforce. If you’re not sure, you can check on your Synchronized Data screen, under Entities — the SF name will show up, then the table name to select from.</li><li><strong>MC also always synchronizes the Salesforce field’s API name as the field name</strong>, which again you can check in your Synchronized Data screen once you click into the sync’d object.</li><li>In step 2.5 you set up your custom DE. <strong>You must use <em>exactly</em> what you typed in as the field name in your custom DE to specify the destination in this step </strong>— spacing matters (Field Name and FieldName are different. Not entirely sure if capitalization matters). Use the left-hand menu to find your custom DE, which will list all of the field names when you click on it. You can drag these into the query builder to ensure you get the right field name.</li><li>Use the Validate Syntax button before moving on to the next step.</li></ul><p>So your query will look more or less like the following:</p><blockquote>Select Id as “Lead Id”, <br>FirstName as “First Name”,<br> LastName as “Last Name”,<br> Email,<br> Status as “Lead Status”,<br> LeadSource as “Lead Source”,<br> IsConverted as “Is Converted”<br>from Lead_Salesforce</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I2kSH7TlNbXTa-E8Sxpzzw.png" /><figcaption>Your completed query, mapping from the source table (Lead_Salesforce) to the destination DE.</figcaption></figure><p>5. Select the destination DE (the one you created in step 2) and choose whether you want to append, update, or overwrite. For this example, you want to update. Click Next.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LEUXA2msFCZ1PX_0CznNAQ.png" /><figcaption>In the middle box, scroll until you find your custom DE and click on it to select. Below, choose Update.</figcaption></figure><p>6. Review and click “Finish”. You have just stored a query, now we have to set up the timer so it runs without manual intervention.</p><p>7. Still in Automation Builder, click “Overview” then “New Automation”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*laE-s2nAReTREnelPiPXpQ.png" /></figure><p>8. In the left hand side, click on the green calendar icon that says “Schedule” and drag it into the circle that says “Start with a Starting Source”. Then click “Configure”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8dN98R-YAwui2caDUEN-yQ.png" /></figure><p>9. Configure the schedule.</p><ul><li>Provide a <strong>starting date</strong> and time that is after the current moment.</li><li>For your <strong>Repeat</strong>, make sure to select some frequency other than “None (run once).” I typically set these on every 1 hour because my journey will run on its own timeline and frequently refreshed data is imperative.</li><li>For your <strong>End</strong>, choose the appropriate time to end this automation. Ideally you would end it to coincide with when you shut off the Journey it is supporting, but that’s up to you.</li><li>Click “Done”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s7A-68V_47vHYYj-bfZpZQ.png" /><figcaption>This schedule will run our actions every hour from the start date till the end of time.</figcaption></figure><p>10. From the left-hand side, grab the SQL Query icon and drag it into the empty space to the right of the schedule.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wEAJT_pGwd2f35vzbSlvcQ.png" /></figure><p>11. Once it’s on the canvas, click “Choose”. From the screen that opens, find your SQL Query that you just created and click on it to select it. Once you’ve clicked on it, a review screen will open — just click “Done” in the bottom right corner.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*onm5tUvsrn-Cp-13OH2FLw.png" /><figcaption>Find the SQL Query you created and click on it to select.</figcaption></figure><p>12. In the upper right corner, click the blue “Save” button. Give your automation a meaningful name and description. Do <strong>not</strong> Save and Close — you have one more thing to do.</p><p>13. Click “Active” to turn on your automation.</p><p>You can also click the “Run Once” button in the upper right of your screen to force a single run and ensure that your data is populating correctly. To do this:</p><ul><li>Click “Run Once” and follow the prompts to select which activities (your SQL query is the only option) to run this one time. Then navigate out of Automation Studio.</li><li>Go back to Contact Builder-&gt;Data Extensions.</li><li>On the main DE screen, you’ll see all of your DEs in a big grid with headers Name, External Key, Description, Field Count, and Record Count. Record Count tells you how many records are in your DE — it started out as 0 in step 2, and stayed 0 until now. You should see it increase to whatever you’re expecting from your Salesforce data set after the run once.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p8SjuPyRLKUi1h3Hl-SoJg.png" /><figcaption>The list of DEs, with a successful population showing in the nonzero record count.</figcaption></figure><ul><li>You can also click on the DE itself and see the records it has stored just to triple check everything is as you expect.</li></ul><h4>Step 5: Set up your Journey with a SF Data Entry Event</h4><p>FINALLY!</p><ol><li>From your main nav, go into Journey Builder. Click “Create New Journey” in your upper right hand corner. Choose “Multi-Step” journey from the next screen.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aLgojnqJ7-OdrnNKSc-KWQ.png" /></figure><p>2. Drag the “Salesforce Data” icon into the “Start with an Entry Source” area on the canvas. It goes into the canvas greyed out — click on it to open up the configuration screen.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*47TO3dCMtE26wAOuq5_TZA.png" /><figcaption>Drag the Salesforce Data icon into the circle, then click on the greyed out version that appears.</figcaption></figure><p>3. Choose how data will enter your journey from Salesforce. For this example, we are pulling directly from a newly created Lead, regardless of their Campaign, and we are not dealing with Communities/Experience Cloud users, so we’ll choose Salesforce Data. When you click on it, it will turn pale blue with a checkmark. Click “Next”</p><p>(<em>Important aside: if you have gotten this far with the example and are NOT using Leads, make sure every step above aligns with whatever object you ARE using!!! We built every step up to this point </em><strong><em>with this specific entry point already in mind</em></strong><em>.)</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Yb8za1UVxpO7ylYt0RhTMw.png" /><figcaption>Salesforce Data as our entry source allows us to select from any object in Salesforce, and respond immediately to the create or update of a record. This will create “Table A” from the explainer earlier in the article. We have already created “Table B” in our custom DE from steps 2–4.</figcaption></figure><p>4. Set up Lead as the starting point.</p><ul><li>In the box that says “Select who to inject into the journey” type “Lead” then hit enter. This will just filter the full list of SF objects.</li><li>In the results, click on the “Lead” object to select it. It will expand to show some fields in the Lead object from your Salesforce — these are our potential primary keys for this data set.</li><li>Click “Lead ID” to select it as the primary key. This is how we will join up data in this data set to data in our custom DE — the Lead IDs will match in both tables.</li><li>Click “Next”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5f3BhhYxwgAHd0LakuPhVg.png" /><figcaption>When you’ve completed the above steps, your screen should look like mine. I searched for lead, then clicked on it to expose the fields, then clicked on Lead ID (Lead) to select it as the primary key.</figcaption></figure><p>5. Select which records from Salesforce should enter onto this journey. What fields on this record make it eligible for this journey? And only on create, or also when the record is changed to make it eligible?</p><p>In my example, I want journey entry only on create, only for people from an unknown lead source, and people who are in a specific lead status.</p><ul><li>Check Is Created and/or Is Updated, based on your actual journey entry needs</li><li>Check Meets Specific Event Criteria to add conditions beyond “created or updated”</li><li>All fields on your Lead object will show up. Find the fields you want to use for your additional entry criteria and drag them one at a time into the right-hand filter builder.</li><li>When you’re done, click “Next”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9-545088cErp6LAtkDLU5A.png" /><figcaption>An example of how I built my entry criteria to select only certain leads, when created in SF, to enter immediately into my MC Journey.</figcaption></figure><p>6. Don’t do anything on the “Filter Criteria” or “Entry Data” screens, just “Next” through them.</p><p>Unless you actually need to. And if you do, please put in the comments what on earth these screens are actually for? I am baffled and can’t get a good answer anywhere.</p><p>7. Review your Summary screen and click Done.</p><p>You’ve now got someone automatically entering the journey, immediately, whenever a Lead is created with the right criteria.</p><h4>Step 6: Set up a Decision Split to see if the Lead is still on the journey</h4><p><strong><em>Important note</em>: </strong>this article is already way too long so I’m not going to go into how to set up the journey overall. I’m assuming you have a basic command of the journey builder itself and can add the things you need to add. For this example, I’m just doing a simple time-delayed email just to set it up to illustrate the decision split.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iChOAXSbsaCTlsF8lLAqgA.png" /><figcaption>Our simplistic starting journey, just to illustrate the key Decision Split that joins the custom DE with the Entry Event data to evaluate exits.</figcaption></figure><ol><li>Drag a Decision Split element into your journey prior to the point you need to evaluate. For example, in the journey above it is the requirement that everybody immediately gets the first email, and then we wait a day. I will add my decision split <em>after</em> the day’s delay and <em>before</em> the next email, to ensure that the person hasn’t converted between entry and next send.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XbjejmrjtizwDUbFA2_jzw.png" /><figcaption>Our simple journey, with a new Decision Split placeholder that isn’t configured yet.</figcaption></figure><p>2. Click the Decision Split in your journey to configure it.</p><ul><li>The default split will be “Path 1” and “Remainder” — just click the pencil icons to rename these. Since this decision asks “Still a valid lead?” I just name them “Yes” and “No”</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WRxs4PMSg0RCyPL6YcOetA.png" /></figure><ul><li>Click “Edit” next to Path 1 / “Yes”. This is where we <em>finally</em> connect all of the prep work.</li><li>Click <strong>Contact Data</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/656/1*qgqyxj-6B5Y573Upr7wiFA.png" /><figcaption>Contact Data is the list of all available Data Extensions, including your custom one that is being refreshed automatically.</figcaption></figure><ul><li>Click on the name of your custom DE (“Leads to Evaluate for Exit” is what the example is called)</li><li>A list of fields available in your custom DE will appear. This holds the <em>current information from Salesforce about your leads.</em></li><li>Drag Lead ID and any other fields you want to evaluate into the right-hand canvas. <strong>Do not configure the Lead ID filter yet. </strong>In our example, you are still a viable lead (and still on the journey) if you have not converted and if your lead status is still Inquiry, so you can see this represented in the screenshot below.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bNGILuT_3VrNphlNbGRrYw.png" /><figcaption>We have set up our filter criteria by dragging fields from our custom DE into the Filter Criteria canvas. For anything that we want to evaluate about the lead, we’ve already configured it by clicking on each filter criterion and and entering the relevant data. We have left Lead Id un-configured for now.</figcaption></figure><ul><li>Click on Lead Id in the Filter Criteria canvas to configure it. Check the “Add an attribute to compare” box.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wmeplpnYZdpyJ-J365RY-w.png" /><figcaption>When you check “Add an attribute to compare” a new field type comes up. This is where you link to the journey’s DE using the steps below.</figcaption></figure><ul><li>In the left-hand side (under “Attributes”) click the home icon to go all the way back out to the top level of attributes available to you.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/1*wtgCuAITfoub8widRvzPwg.png" /></figure><ul><li>Click <strong>Journey Data </strong>to access the information about journey participants on this specific journey.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/656/1*oSdMStmwfE9Z2izLy0iAIw.png" /></figure><ul><li>When you click on Journey Data, you should see only one option, which would be your Data Entry element. Click on it to see the available fields.</li><li>Drag Lead ID into the right-hand side to link it to the Lead ID from your custom Data Extension.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nkkbQGcER1P-_izNhWkClg.png" /><figcaption>Drag the Lead ID from the data in “Table A” (this journey’s entry data) to match to the Lead ID from “Table B” (your custom DE that contains updated SF data).</figcaption></figure><ul><li>Click “Summary” to to back to the Decision Split config screen and review your logic.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L1g7vb5mRzBt4XrDripx2g.png" /><figcaption>When you’re done with the setup, you will see a confirmation of the filter logic that matches the path you’ve set.</figcaption></figure><p>Based on the image above, we can see that for anyone who started this journey <em>and</em> is also found in the custom DE (Lead Id equal Lead:Id) AND who also still has a lead status of “Inquiry” (from the custom DE) AND who is not yet converted (from the custom DE) will proceed with the journey on the “Yes” path. Anybody else is kicked to the “No” path which simply exits them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1M3pg3kl2tBS2VAjxQGl-A.png" /></figure><p>Once you have your decision split set up, you can simply clone it and paste it anywhere else on your journey that it’s needed. So it’s tedious, but fast.</p><h4>Step 7: Celebrate</h4><p>You did it. And hopefully you learned a lot, or at least got your marketing team off your back.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*kn55sSbRabX-miwVEZi2YA.gif" /><figcaption><a href="http://www.reactiongifs.com/wp-content/uploads/2013/07/crazy-celebration.gif">http://www.reactiongifs.com/wp-content/uploads/2013/07/crazy-celebration.gif</a></figcaption></figure><p><em>My firm (</em><a href="http://deepwhydesign.com/"><em>Deep Why Design</em></a><em>) specializes in weird use cases and things nobody else wants to touch — from big and innovative shared platforms and custom solutions to </em><a href="https://www.deepwhydesign.com/courses-and-training"><em>teaching accidental admins</em></a><em> how to get the most out of their systems. My areas of expertise are fire jumping and forensics — call when there’s an unsolvable problem — and the design and management of large, integrated, and complex data systems in highly constrained situations. I am NOT a Marketing Cloud expert, obviously.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ad3cc4b5038f" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>