<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Richard C</title>
    <description>The latest articles on DEV Community by Richard C (@richardc_ailstudio).</description>
    <link>https://dev.to/richardc_ailstudio</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4208209%2F83830afb-9163-4856-88bd-a56c5b5f3442.png</url>
      <title>DEV Community: Richard C</title>
      <link>https://dev.to/richardc_ailstudio</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/richardc_ailstudio"/>
    <language>en</language>
    <item>
      <title>How to check record counts and parent relationships after a Salesforce load</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Wed, 11 Nov 2026 14:28:42 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-check-record-counts-and-parent-relationships-after-a-salesforce-load-1k5a</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-check-record-counts-and-parent-relationships-after-a-salesforce-load-1k5a</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;Spreadsheet formulas were tested in LibreOffice Calc 25.2.3.2, not in Google Sheets, Excel, or a Salesforce org. No Salesforce org was queried to write this article.&lt;/p&gt;

&lt;p&gt;A finished Data Loader or Bulk API job can still be short on rows, or it can accept every child while some parent lookups stay blank and one parent ends up with fewer children than the source file. This walkthrough reconciles source-to-target counts per object using the job’s own success and error files, then checks whether parents resolved: how many children have a blank parent lookup, and whether each parent has the same number of children in the source and in the target.&lt;/p&gt;

&lt;p&gt;It does not check whether a weekly Data Export downloaded every zip or attachment before you migrated. It does not walk through Contact-export orphans, duplicate External IDs, or picklist mismatches; those are separate file checks.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Count what the job accepted, then what the org contains
&lt;/h2&gt;

&lt;p&gt;After an import, &lt;a href="https://developer.salesforce.com/docs/platform/data-loader-impl/guide/reviewing-output-files.html" rel="noopener noreferrer"&gt;Data Loader&lt;/a&gt; generates two CSV output files. “One file name begins with &lt;code&gt;success&lt;/code&gt;, and the other starts with &lt;code&gt;error&lt;/code&gt;.” The success file “contains the successfully loaded records, including a column with the newly generated record IDs.” The error file “contains the rejected records, with a column that describes why the load failed.”&lt;/p&gt;

&lt;p&gt;If you loaded through the Bulk API, the job’s &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_reference_jobinfo.htm" rel="noopener noreferrer"&gt;JobInfo&lt;/a&gt; includes &lt;code&gt;numberRecordsProcessed&lt;/code&gt; (“the number of records already processed”) and &lt;code&gt;numberRecordsFailed&lt;/code&gt; (“the number of records that weren’t processed successfully in this job”). Read those two fields from the completed job; do not invent a third number the API did not return.&lt;/p&gt;

&lt;p&gt;Then count the object in the target org with &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_count.htm" rel="noopener noreferrer"&gt;SOQL &lt;code&gt;COUNT()&lt;/code&gt;&lt;/a&gt;. &lt;code&gt;COUNT()&lt;/code&gt; “returns the number of rows that match the filtering conditions” and “must be the only element in the &lt;code&gt;SELECT&lt;/code&gt; list”:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;Contact&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Run the query as a user who can see the same records you meant to load. &lt;code&gt;COUNT()&lt;/code&gt; “includes null values that match the filtering conditions,” which matters in the next section when you filter on a blank parent.&lt;/p&gt;

&lt;p&gt;For each object, put Source rows, Success rows, Error rows, Success+Error, Vs source, Target &lt;code&gt;COUNT()&lt;/code&gt;, and Target vs success on a &lt;code&gt;Counts&lt;/code&gt; sheet. In E2, F2 and H2, then fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(OR(B2="",C2="",D2=""),"",C2+D2)
=IF(E2="","",E2-B2)
=IF(OR(C2="",G2=""),"",G2-C2)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Example data (not a customer org, and not the result of a real load).&lt;/strong&gt; Four Contact rows were sent. Three landed in the success file and one in the error file. The target &lt;code&gt;COUNT()&lt;/code&gt; is 3.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Object&lt;/th&gt;
&lt;th&gt;Source rows&lt;/th&gt;
&lt;th&gt;Success rows&lt;/th&gt;
&lt;th&gt;Error rows&lt;/th&gt;
&lt;th&gt;Success+Error&lt;/th&gt;
&lt;th&gt;Vs source&lt;/th&gt;
&lt;th&gt;Target COUNT()&lt;/th&gt;
&lt;th&gt;Target vs success&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Contact&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;In LibreOffice Calc 25.2.3.2 those formulas produced Success+Error &lt;code&gt;4&lt;/code&gt;, Vs source &lt;code&gt;0&lt;/code&gt;, and Target vs success &lt;code&gt;0&lt;/code&gt;. A blank object row left the calculated cells blank. Matching totals here only mean the rejected row is accounted for in the error file. They do not mean every parent received the children you expected.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Did the parents resolve?
&lt;/h2&gt;

&lt;p&gt;Children point at parents through a lookup or a master-detail field. &lt;a href="https://help.salesforce.com/s/articleView?id=sf.relationships_considerations.htm&amp;amp;language=en_US" rel="noopener noreferrer"&gt;Considerations for Relationships&lt;/a&gt; (Salesforce Help) states that you can convert a lookup to a master-detail relationship “if the lookup field in all the records contains a value,” and that you cannot create a master-detail relationship if the custom object already contains data unless you first fill that parent field. After a load, treat a child that arrived with a blank parent as a lookup left empty (or as a row that never arrived and should be in the error file)—not as proof that a required parent was filled.&lt;/p&gt;

&lt;p&gt;Count children whose parent lookup is blank:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="nc"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="kc"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;COUNT()&lt;/code&gt; includes rows that match the filter, so this returns how many Contacts have no Account. To inspect the rows, use a &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm" rel="noopener noreferrer"&gt;relationship query&lt;/a&gt; that reads the parent with the dot operator:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nc"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;LastName&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Account&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;Name&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="nc"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="kc"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;Account.Name&lt;/code&gt; will be empty on those rows. Replace &lt;code&gt;Contact&lt;/code&gt; / &lt;code&gt;AccountId&lt;/code&gt; with the child object and parent field you actually loaded.&lt;/p&gt;

&lt;p&gt;This is only a blank-parent count. It does not tell you whether a filled parent Id points at the wrong company, and it is not a picklist or External ID check.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Compare child counts per parent
&lt;/h2&gt;

&lt;p&gt;A parent can “resolve” on every successful child and still be short if some of its children were rejected. Compare how many children each parent has in the source file versus the target.&lt;/p&gt;

&lt;p&gt;In the target org, &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby.htm" rel="noopener noreferrer"&gt;GROUP BY&lt;/a&gt; with &lt;code&gt;COUNT(fieldName)&lt;/code&gt; returns a count per parent. Salesforce documents &lt;code&gt;COUNT(fieldName)&lt;/code&gt; with &lt;code&gt;GROUP BY&lt;/code&gt; for summary reporting, and notes that &lt;code&gt;GROUP BY&lt;/code&gt; without an aggregate returns distinct values “including &lt;code&gt;null&lt;/code&gt;”:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nc"&gt;AccountId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="nc"&gt;AccountId&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Salesforce record Ids usually change between the source org and the target org, so do not compare those Id values to the source file. Export (or keep) a parent external key on both sides—this example uses &lt;code&gt;Parent_Legacy_Id__c&lt;/code&gt;—and count children per that key in a sheet.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Example data (same made-up load as section 1).&lt;/strong&gt; Source Contacts: two children of &lt;code&gt;AC-001&lt;/code&gt;, one child of &lt;code&gt;AC-002&lt;/code&gt;, and one child with a blank parent. The rejected row was the &lt;code&gt;AC-002&lt;/code&gt; child, so the target has two children of &lt;code&gt;AC-001&lt;/code&gt;, none of &lt;code&gt;AC-002&lt;/code&gt;, and one blank parent.&lt;/p&gt;

&lt;p&gt;On a &lt;code&gt;Parents&lt;/code&gt; sheet, list each parent key (use &lt;code&gt;(blank)&lt;/code&gt; as the label for empty parents). Put the source parent keys in &lt;code&gt;J2:J5&lt;/code&gt; and the target parent keys in &lt;code&gt;K2:K4&lt;/code&gt;. For a named parent in A2:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF($J$2:$J$5,A2)
=COUNTIF($K$2:$K$4,A2)
=IF(OR(B2="",C2=""),"",C2-B2)
=IF(A2="","",IF(C2-B2=0,"Match","Review"))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For the &lt;code&gt;(blank)&lt;/code&gt; row, count empty parent keys explicitly:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF($J$2:$J$5,"")
=COUNTIF($K$2:$K$4,"")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;In LibreOffice Calc 25.2.3.2 those formulas produced:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Parent              Source children  Target children  Difference  Status
AC-001              2                2                0           Match
AC-002              1                0               -1           Review
(blank)             1                1                0           Match
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;AC-002&lt;/code&gt; is the point: the object-level counts in section 1 matched, and one child loaded with a blank parent lookup, but this parent has no children in the target because its child is in the error file.&lt;/p&gt;

&lt;p&gt;Widen the &lt;code&gt;COUNTIF&lt;/code&gt; ranges to your last data row.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. What matching counts do not prove
&lt;/h2&gt;

&lt;p&gt;A matching per-parent count does not prove each child points at the intended parent. Two parents that each had two children can swap those children and still show &lt;code&gt;Match&lt;/code&gt;. A blank parent lookup can be intentional on an optional lookup; treat it as a review list. Permissions matter: SOQL you cannot see, you cannot count.&lt;/p&gt;

&lt;p&gt;Before you share any export outside your team, for a review or anything else, send only the objects and columns the reviewer needs. Remove or blank personal fields you don't need, such as personal emails, phone numbers, home addresses and birthdates, and never include passwords or org logins.&lt;/p&gt;

&lt;p&gt;AIL Studio is not affiliated with or endorsed by Salesforce, Inc. Salesforce is a trademark of Salesforce, Inc.&lt;/p&gt;

&lt;p&gt;If you want a second pair of eyes on Contact and Account exports (CSV or Excel, up to 25,000 rows per file), there's a &lt;a href="https://ailstudio.netlify.app/products/salesforce-export-check/?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-postload-counts-parent-relationships" rel="noopener noreferrer"&gt;$19 Salesforce Export Check&lt;/a&gt;; the product page explains what's included and how to send the files. It reviews relationship and quality checks on those two objects (blank Account, Account values that are not in the Account file, blank required columns you name, External ID counts and wrong-parent Contacts when you send the matching files). It does not log into your org, merge records, or load data, and it is not a Bulk API job review or a children-per-parent count for every object.&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>datamigration</category>
      <category>productivity</category>
      <category>soql</category>
    </item>
    <item>
      <title>How to check that a Salesforce Data Export is complete before you migrate</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Tue, 10 Nov 2026 15:48:31 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-check-that-a-salesforce-data-export-is-complete-before-you-migrate-2c2d</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-check-that-a-salesforce-data-export-is-complete-before-you-migrate-2c2d</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;Count formulas were tested in LibreOffice Calc 25.2, not in Google Sheets, Excel or a Salesforce org.&lt;/p&gt;

&lt;p&gt;A weekly or monthly Data Export, or a Data Loader extract, is often the file set you will migrate from. A finished job is not the same as a complete file set. You can be missing a zip, an object, the physical attachments, or a few hundred rows, and only notice after the target load.&lt;/p&gt;

&lt;p&gt;This walkthrough checks whether every archive downloaded, the expected objects and files arrived, and CSV row counts match a SOQL &lt;code&gt;COUNT()&lt;/code&gt; run with comparable filters. It does not check wrong-parent Contacts, and it does not group Data Loader import errors.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Download every archive before it disappears
&lt;/h2&gt;

&lt;p&gt;&lt;a href="https://help.salesforce.com/s/articleView?id=000383962&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Salesforce’s Data Export FAQ&lt;/a&gt; (publish date Aug 17, 2026) says export files are available for 48 hours after completion. They are also removed as soon as a new Data Export is queued, even inside that window, and once gone they cannot be recovered by you or by Support.&lt;/p&gt;

&lt;p&gt;Large orgs produce more than one zip. Each archive “can be up to 512 MB (approximately)”; bigger exports generate multiple files, and a zip well above that size can hold one large uploaded file (up to 2 GB). Count the listed zips against what you saved. There is a 60-second wait between downloads; fetching too quickly can return HTTP 429.&lt;/p&gt;

&lt;p&gt;The user who scheduled the export receives the ready-to-download email, and that address cannot be changed. There is no completion SLA; a request “may be expected… to take longer than a week.” Data added while the export is already running is not included.&lt;/p&gt;

&lt;p&gt;Do not treat a sandbox schedule as a backup. The FAQ says it is “expected behavior for it to never actually process.” &lt;a href="https://help.salesforce.com/s/articleView?id=000381137&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Help 000381137&lt;/a&gt; (publish date Jul 7, 2026) states the same limit: “The Data Export is not available in your Sandbox org.”&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Confirm objects and files, not only a zip sitting on disk
&lt;/h2&gt;

&lt;p&gt;The FAQ’s answer to “Why do I see fewer files than expected in my download?” is that you may not have selected all objects, or you may have left off &lt;strong&gt;Include images, documents, and attachments&lt;/strong&gt; and &lt;strong&gt;Include Salesforce Files and Salesforce CRM Content document versions&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;List every object you plan to migrate, then tick the matching CSV in each zip. A missing &lt;code&gt;Account.csv&lt;/code&gt; or &lt;code&gt;Contact.csv&lt;/code&gt; is a completeness failure even if the email arrived. Two documented gaps also surprise people later: formula and roll-up summary fields “are always excluded from exports,” and Recycle Bin data is not included. Archived records are included in the Data Export service.&lt;/p&gt;

&lt;p&gt;If you need the physical files, use Data Export rather than Data Loader. Help 000381137 says Data Loader “doesn’t obtain the physical files you’re attempting to back up”; it writes CSV metadata. Data Export can download attachments, images and documents as well as the CSVs. The &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/exporting_data.htm" rel="noopener noreferrer"&gt;Data Loader export guide&lt;/a&gt; repeats that Data Loader “does not support exporting attachments” and points you to the weekly export feature.&lt;/p&gt;

&lt;p&gt;After a weekly export, Chatter, content and feed attachments may still not look like the originals. &lt;a href="https://help.salesforce.com/s/articleView?id=000386370&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Help 000386370&lt;/a&gt; (publish date May 21, 2026) says those records are exported in MIME format and “named after the record ID (starting with 068) and not the original name of the file.” They need converting before you open them; a folder of &lt;code&gt;068…&lt;/code&gt; MIME files is expected. If you export &lt;code&gt;ContentDocument&lt;/code&gt; through Data Loader instead, without the &lt;strong&gt;Query All Files&lt;/strong&gt; permission you only get files you own or that are shared directly with you.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Compare each CSV to a SOQL count
&lt;/h2&gt;

&lt;p&gt;A zip can be present and still short on rows. Compare each object’s file to a &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_count.htm" rel="noopener noreferrer"&gt;SOQL &lt;code&gt;COUNT()&lt;/code&gt;&lt;/a&gt; query. &lt;code&gt;COUNT()&lt;/code&gt; “returns the number of rows that match the filtering conditions” and must be the only item in the &lt;code&gt;SELECT&lt;/code&gt; list:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;Account&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;Contact&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Run the queries as a user who can see the same records as the export. Data Loader’s guide says that with no extra conditions, “all the data to which you have read access is returned.” An extract run by a user who cannot see every Account will be smaller than an admin &lt;code&gt;COUNT()&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;Match the export mode to the query. Data Loader &lt;strong&gt;Export&lt;/strong&gt; is the default; &lt;strong&gt;Export All&lt;/strong&gt; also exports “archived activity records and soft-deleted records.” A weekly Data Export includes archived records but not Recycle Bin rows. If those choices differ, the counts will differ for a documented reason.&lt;/p&gt;

&lt;p&gt;Count data rows outside Excel when the file may be large. &lt;a href="https://help.salesforce.com/s/articleView?id=000386983&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Help 000386983&lt;/a&gt; (publish date May 4, 2026) notes that a Data Export CSV can exceed Excel’s limit of approximately 1,048,576 rows. Trusting Excel’s last row number can hide the remainder.&lt;/p&gt;

&lt;p&gt;For Data Loader extracts, keep encoding consistent. &lt;a href="https://help.salesforce.com/s/articleView?id=000387201&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Help 000387201&lt;/a&gt; (publish date Apr 2, 2026) recommends leaving &lt;strong&gt;Write all CSVs with UTF-8 encoding&lt;/strong&gt; enabled so exports keep special characters, and warns that Excel on Windows often saves CSV files as ANSI.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Example data: a three-object completeness sheet
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data (not a customer org, and not the result of a real export).&lt;/strong&gt; The counts below are invented. On a &lt;code&gt;Check&lt;/code&gt; sheet, put Object, SOQL COUNT(), CSV data rows, Difference and Status in row 1. Enter the query result in column B and the file’s data-row count (no header) in column C. In D2 and E2, then fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(OR(B2="",C2=""),"",C2-B2)
=IF(A2="","",IF(OR(B2="",C2=""),"Enter both counts",IF(C2-B2=0,"Match","Review")))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Object&lt;/th&gt;
&lt;th&gt;SOQL COUNT()&lt;/th&gt;
&lt;th&gt;CSV data rows&lt;/th&gt;
&lt;th&gt;Difference&lt;/th&gt;
&lt;th&gt;Status&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Account&lt;/td&gt;
&lt;td&gt;150&lt;/td&gt;
&lt;td&gt;150&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;td&gt;Match&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Contact&lt;/td&gt;
&lt;td&gt;400&lt;/td&gt;
&lt;td&gt;397&lt;/td&gt;
&lt;td&gt;-3&lt;/td&gt;
&lt;td&gt;Review&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Opportunity&lt;/td&gt;
&lt;td&gt;80&lt;/td&gt;
&lt;td&gt;80&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;td&gt;Match&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;In LibreOffice Calc 25.2 those formulas produced the Status values above, and a blank object name left Status blank. &lt;code&gt;=COUNTA(SampleCsv!A:A)-1&lt;/code&gt; returned 2 for a two-row example file with a header row. The Contact row is the point: the zip arrived, &lt;code&gt;Contact.csv&lt;/code&gt; exists, and the count is still short. Check a filter, a sharing limit or an unfinished download before you migrate from that file.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. What a matching count does not prove
&lt;/h2&gt;

&lt;p&gt;A matching &lt;code&gt;COUNT()&lt;/code&gt; means the file has that many rows. It does not mean formula or roll-up columns are present, that attachments arrived as openable files, or that each Contact points at the right Account. After you have complete Contact and Account files, you can still review orphans, duplicate External IDs and parent keys separately. Before you share any export outside your team, for a review or anything else, send only the objects and columns the reviewer needs. Remove or blank personal fields you don't need, such as personal emails, phone numbers, home addresses and birthdates, and never include passwords or org logins.&lt;/p&gt;

&lt;p&gt;AIL Studio is not affiliated with or endorsed by Salesforce, Inc. Salesforce is a trademark of Salesforce, Inc.&lt;/p&gt;

&lt;p&gt;If you want a second pair of eyes on Contact and Account exports (CSV or Excel, up to 25,000 rows per file), there's a &lt;a href="https://ailstudio.netlify.app/products/salesforce-export-check/?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-data-export-complete" rel="noopener noreferrer"&gt;$19 Salesforce Export Check&lt;/a&gt;; the product page explains what's included and how to send the files.&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>datamigration</category>
      <category>productivity</category>
    </item>
    <item>
      <title>How to batch-export Amazon, eBay and Etsy main product photos with ImageMagick</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Tue, 10 Nov 2026 14:17:06 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-batch-export-amazon-ebay-and-etsy-main-product-photos-with-imagemagick-1b17</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-batch-export-amazon-ebay-and-etsy-main-product-photos-with-imagemagick-1b17</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;If you sell the same item on Amazon, eBay and Etsy, you end up exporting one photo three or four times: a white square for Amazon, a slightly smaller square for eBay, maybe a wide crop, and an Etsy file with more breathing room. Doing that by hand in an editor is slow and easy to get wrong. This post does it with a few ImageMagick commands, then uses ImageMagick again to check the files before upload.&lt;/p&gt;

&lt;p&gt;The marketplace rules below are the same ones collected in &lt;a href="https://dev.to/richardc_ailstudio/how-to-make-amazon-etsy-and-ebay-main-product-photos-that-meet-the-rules-435l?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=listingphotoset-imagemagick"&gt;How to make Amazon, Etsy, and eBay main product photos that meet the rules&lt;/a&gt;, with sources there. Help pages change, so trust each site's current image policy over this summary.&lt;/p&gt;

&lt;h2&gt;
  
  
  The targets
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Amazon main image:&lt;/strong&gt; pure white background (RGB 255, 255, 255), product filling at least 85% of the frame, no text, logos, borders or watermarks. A 2000 × 2000 JPEG clears the zoom recommendation.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;eBay:&lt;/strong&gt; white or neutral background recommended, about 1600 × 1600; eBay's help also says 1:1 or 16:9 photos generally look best, so a 1600 × 900 version is optional.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Etsy:&lt;/strong&gt; at least 2000 pixels wide and high recommended; the first photo should be horizontal or square; transparent PNGs aren't supported. Etsy also requires at least one unedited photo of the actual item against a real background, which no script can make for you.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Start from a cutout, not a raw photo
&lt;/h2&gt;

&lt;p&gt;ImageMagick is not a background remover. The commands below assume you already have a &lt;strong&gt;PNG with a transparent background&lt;/strong&gt; (exported from whatever editor or remover you use).&lt;/p&gt;

&lt;p&gt;This matters more than it sounds. When I tested the same command on a JPEG shot against a light gray backdrop (RGB 246, 246, 244), the output passed the obvious check, because the canvas corners were pure white. But the gray was still there in a box around the product, since the trim step only cuts the frame down to the product's bounding box. The leftover-background check further down catches this.&lt;/p&gt;

&lt;h2&gt;
  
  
  Check your install
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;magick -version
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These commands were tested with ImageMagick 7.1.2-35. Older ImageMagick 6 installs use &lt;code&gt;convert&lt;/code&gt; instead of &lt;code&gt;magick&lt;/code&gt;; I didn't test version 6.&lt;/p&gt;

&lt;h2&gt;
  
  
  One command per size
&lt;/h2&gt;

&lt;p&gt;Amazon, 2000 × 2000, product at 85% (1,700 px on its longest side):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;magick mug.png -trim +repage -resize 1700x1700 \
  -background white -gravity center -extent 2000x2000 \
  -alpha remove -alpha off -quality 92 mug-amazon-2000.jpg
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;What each part does:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;-trim +repage&lt;/code&gt; cuts away the transparent border so the product's own edges define the size, then resets the canvas offset.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;-resize 1700x1700&lt;/code&gt; fits the longest side to 1,700 px and keeps the aspect ratio. This is the fill step: 1,700 of 2,000 is 85%.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;-background white -gravity center -extent 2000x2000&lt;/code&gt; centers the product on a 2000 × 2000 white canvas.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;-alpha remove -alpha off&lt;/code&gt; flattens any remaining transparency onto white, so nothing turns black on Etsy.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;-quality 92&lt;/code&gt; sets JPEG quality. The test files came out between about 28 KB and 79 KB, far below any upload limit, but real photos will be larger.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The other sizes only change two numbers:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;File&lt;/th&gt;
&lt;th&gt;&lt;code&gt;-resize&lt;/code&gt;&lt;/th&gt;
&lt;th&gt;&lt;code&gt;-extent&lt;/code&gt;&lt;/th&gt;
&lt;th&gt;Fill&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Amazon&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1700x1700&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;2000x2000&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;85%&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;eBay square&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1360x1360&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1600x1600&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;85%&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;eBay wide&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1360x765&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1600x900&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;85% of the height&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Etsy&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1500x1500&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;2000x2000&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;about 75%&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The Etsy margin is a practical choice so thumbnails survive Etsy's crops, not an Etsy-published percentage.&lt;/p&gt;

&lt;h2&gt;
  
  
  Batch a whole folder
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;mkdir -p out
&lt;span class="k"&gt;for&lt;/span&gt; f &lt;span class="k"&gt;in&lt;/span&gt; cutouts/*.png&lt;span class="o"&gt;;&lt;/span&gt; &lt;span class="k"&gt;do&lt;/span&gt;
  &lt;span class="nv"&gt;name&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="nv"&gt;$(&lt;/span&gt;basename &lt;span class="s2"&gt;"$f"&lt;/span&gt; .png&lt;span class="o"&gt;)&lt;/span&gt;
  &lt;span class="nv"&gt;base&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="o"&gt;(&lt;/span&gt;magick &lt;span class="s2"&gt;"$f"&lt;/span&gt; -trim +repage&lt;span class="o"&gt;)&lt;/span&gt;
  &lt;span class="s2"&gt;"${base[@]}"&lt;/span&gt; -resize 1700x1700 -background white -gravity center -extent 2000x2000 -alpha remove -alpha off -quality 92 &lt;span class="s2"&gt;"out/${name}-amazon-2000.jpg"&lt;/span&gt;
  &lt;span class="s2"&gt;"${base[@]}"&lt;/span&gt; -resize 1360x1360 -background white -gravity center -extent 1600x1600 -alpha remove -alpha off -quality 92 &lt;span class="s2"&gt;"out/${name}-ebay-1600.jpg"&lt;/span&gt;
  &lt;span class="s2"&gt;"${base[@]}"&lt;/span&gt; -resize 1360x765  -background white -gravity center -extent 1600x900  -alpha remove -alpha off -quality 92 &lt;span class="s2"&gt;"out/${name}-ebay-1600x900.jpg"&lt;/span&gt;
  &lt;span class="s2"&gt;"${base[@]}"&lt;/span&gt; -resize 1500x1500 -background white -gravity center -extent 2000x2000 -alpha remove -alpha off -quality 92 &lt;span class="s2"&gt;"out/${name}-etsy-2000.jpg"&lt;/span&gt;
&lt;span class="k"&gt;done&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Four checks before you upload
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;1. Size.&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;magick identify -format &lt;span class="s1"&gt;'%f %wx%h %b\n'&lt;/span&gt; out/*.jpg
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;2. Corner pixel is pure white.&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;magick out/mug-amazon-2000.jpg -format &lt;span class="s1"&gt;'%[pixel:p{0,0}]\n'&lt;/span&gt; info:
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You want &lt;code&gt;srgb(255,255,255)&lt;/code&gt;. Anything like &lt;code&gt;srgb(250,250,250)&lt;/code&gt; fails Amazon's main-image rule.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. No leftover background.&lt;/strong&gt; This is the check the gray-backdrop test needed. It reports the percentage of pixels that are almost white but not quite:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;magick out/mug-amazon-2000.jpg -colorspace Gray \
  -fx &lt;span class="s1"&gt;'(u&amp;gt;0.93 &amp;amp;&amp;amp; u&amp;lt;0.995)?1:0'&lt;/span&gt; -format &lt;span class="s1"&gt;'%[fx:mean*100]\n'&lt;/span&gt; info:
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;On my test files (EXAMPLE DATA: two flat shapes drawn as transparent PNGs, not real products), the clean cutouts scored 0.03% and 0.16%, which is just the soft edge of the shape. The file made from the gray-backdrop JPEG scored 17.68%. Re-running the Amazon command on that same JPEG reproduced 17.68% both times; a previously saved output of the test measures 15.28%, which that command does not reproduce. My rule of thumb, which is an opinion and not a marketplace rule: above about 1%, open the file and look for a halo or a box of off-white around the product.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;4. Product fill.&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;magick out/mug-amazon-2000.jpg -fuzz 2% -trim -format &lt;span class="s1"&gt;'%wx%h\n'&lt;/span&gt; info:
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That prints the size of the non-white area. My Amazon test file printed &lt;code&gt;1700x1417&lt;/code&gt;, so the longest side is 1,700 of 2,000 pixels: 85%.&lt;/p&gt;

&lt;h2&gt;
  
  
  What this does not do
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;It doesn't fix a small source.&lt;/strong&gt; &lt;code&gt;-resize 1700x1700&lt;/code&gt; will enlarge a 600-pixel cutout, and the result can look soft. Writing &lt;code&gt;'1700x1700&amp;gt;'&lt;/code&gt; only shrinks, never enlarges. In my test that left the product at about 604 px, roughly 30% fill, which no longer meets Amazon's 85% rule. The real fix is a bigger original photo.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It doesn't add shadows&lt;/strong&gt;, and that's deliberate. A drop shadow tints pixels Amazon wants at 255, 255, 255; keep shadows for secondary images.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It doesn't cover Etsy's real-photo rule.&lt;/strong&gt; Upload an unedited photo of the actual item in a real setting as well.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;If you'd rather not run commands, &lt;a href="https://poe.com/ListingPhotoSet?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=listingphotoset-imagemagick" rel="noopener noreferrer"&gt;Listing Photo Set&lt;/a&gt; on Poe will place a product photo on a pure white canvas and return the common marketplace sizes; you try it there and spend Poe points to run it.&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

</description>
      <category>imagemagick</category>
      <category>productivity</category>
      <category>ecommerce</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>How to upsert related Salesforce records with an External ID (load Accounts before Contacts)</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Tue, 10 Nov 2026 09:25:49 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-upsert-related-salesforce-records-with-an-external-id-load-accounts-before-contacts-3jh1</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-upsert-related-salesforce-records-with-an-external-id-load-accounts-before-contacts-3jh1</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;This walkthrough follows Salesforce Help. It was not run in a Salesforce org or in the Data Loader desktop app. Data Loader labels below are quoted from the Help article, not from a live Data Loader window.&lt;/p&gt;

&lt;p&gt;Your Contact CSV still has the source system’s Account key. Salesforce will not accept that key as &lt;code&gt;AccountId&lt;/code&gt;. Relationships are stored as record IDs, and those IDs are new in the target org.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://help.salesforce.com/s/articleView?id=000384648&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Salesforce Help 000384648&lt;/a&gt; (publish date Jul 3, 2026) puts it this way: “Record relationships in Salesforce are maintained through unique record IDs rather than record names.” Legacy IDs “must be replaced with the newly generated IDs from the target org.” A common approach is a VLOOKUP of those new IDs. The same article says that if VLOOKUP is not practical, a custom External ID field populated with the source identifier “allows related records to be imported using External ID relationships.”&lt;/p&gt;

&lt;p&gt;This how-to is that second path: fill a unique External ID on Account, load Accounts first, then upsert Contacts by mapping the Account-key column to &lt;code&gt;Account:Your_External_ID__c&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Create and fill a unique External ID on Account
&lt;/h2&gt;

&lt;p&gt;&lt;a href="https://help.salesforce.com/s/articleView?id=000383207&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Salesforce Help 000383207&lt;/a&gt; (publish date Jul 5, 2026) says to create a custom field with the data type Text on the parent object and select the External ID and Unique attributes, or use an existing External ID field.&lt;/p&gt;

&lt;p&gt;Use your own API name. The examples below use &lt;code&gt;Legacy_Account_Id__c&lt;/code&gt; as a sample name. If you pick something else, the mapping syntax later becomes &lt;code&gt;Account:That_Field__c&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;If the Accounts already exist and the External ID is blank, the same article describes exporting those Accounts (include the Salesforce IDs), filling the External ID, and updating the existing records by mapping Id and the External ID field.&lt;/p&gt;

&lt;p&gt;If the Accounts do not exist yet, map the source key into the External ID when you insert or upsert the Account file. The Help article recommends choosing values that already appear in the child file, so Contacts can match the same key.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Load Accounts before Contacts
&lt;/h2&gt;

&lt;p&gt;000384648’s recommended import order for core objects starts with Accounts, then Campaigns, then Contacts, then Opportunities (then Cases, Price Books, Products, Leads and Contracts). This tutorial stays on Accounts, then Contacts. The rest of that list was not walked through here.&lt;/p&gt;

&lt;p&gt;Always back up first. 000383207 says to back up before any Data Loader operation, then “Run a test with one or a small subset of records” and verify the records in Salesforce before you load the full file.&lt;/p&gt;

&lt;p&gt;You can insert the Account file, which is how 000383207 loads new parent records, or upsert it matching on the External ID. The SOAP API &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_upsert.htm" rel="noopener noreferrer"&gt;upsert()&lt;/a&gt; call creates a record when the external ID isn’t matched and updates the existing record when it is matched one time; only Upsert lets a later rerun update the same Account instead of creating another row, and that needs the field to be unique. Matching by external ID is case-insensitive only if the field has the Unique attribute and the Treat “ABC” and “abc” as duplicate values (case insensitive) option selected.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Upsert Contacts and map the Account relationship
&lt;/h2&gt;

&lt;p&gt;Prepare the Contact CSV with the source Account key in its own column. Do not put that key in &lt;code&gt;AccountId&lt;/code&gt;. &lt;code&gt;AccountId&lt;/code&gt; expects a Salesforce Account Id.&lt;/p&gt;

&lt;p&gt;The Data Loader steps below are the wording from 000383207, written for a parent “Object A” and a child “Object B.” Here Object A is Account and Object B is Contact:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Click &lt;strong&gt;Upsert&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Select Contact.&lt;/li&gt;
&lt;li&gt;Click &lt;strong&gt;Browse&lt;/strong&gt;, select the Contact CSV, then click &lt;strong&gt;Next&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;In Step 2, &lt;strong&gt;choose your related objects&lt;/strong&gt;, select the Account External ID field from the Account drop-down.&lt;/li&gt;
&lt;li&gt;In step 3, &lt;strong&gt;mapping&lt;/strong&gt;, map the Contact file’s Account-key column to the Salesforce field named with the syntax &lt;code&gt;[ObjectName]:[ExternalIDField__c]&lt;/code&gt;. For the sample field, that name is &lt;code&gt;Account:Legacy_Account_Id__c&lt;/code&gt;.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The same article says this “eliminates the process of importing Object A records, exporting the new IDs, matching them up in Excel and then importing the child records.”&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Example data
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data (not a customer org, and not the result of a Salesforce load).&lt;/strong&gt; Names, keys and addresses are invented. The email addresses use the reserved &lt;code&gt;example.invalid&lt;/code&gt; domain. Do not import these rows into a live org.&lt;/p&gt;

&lt;p&gt;Example Account CSV:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Legacy_Account_Id__c&lt;/th&gt;
&lt;th&gt;Name&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;AC-001&lt;/td&gt;
&lt;td&gt;Example Aster Labs&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;AC-002&lt;/td&gt;
&lt;td&gt;Example Blue Orchard&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Example Contact CSV. &lt;code&gt;Account_Legacy_Key&lt;/code&gt; is the source Account key, not a Salesforce Id:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;LastName&lt;/th&gt;
&lt;th&gt;FirstName&lt;/th&gt;
&lt;th&gt;Email&lt;/th&gt;
&lt;th&gt;Account_Legacy_Key&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;Renee&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:renee@example.invalid"&gt;renee@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;AC-001&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;Sam&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:sam@example.invalid"&gt;sam@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;AC-002&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;Nora&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:nora@example.invalid"&gt;nora@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;AC-999&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Expected mapping for the relationship: &lt;code&gt;Account_Legacy_Key&lt;/code&gt; → &lt;code&gt;Account:Legacy_Account_Id__c&lt;/code&gt;. Upsert the two Accounts first, matching on &lt;code&gt;Legacy_Account_Id__c&lt;/code&gt;. Then upsert the Contacts.&lt;/p&gt;

&lt;p&gt;Renee should resolve to Example Aster Labs (&lt;code&gt;AC-001&lt;/code&gt;). Sam should resolve to Example Blue Orchard (&lt;code&gt;AC-002&lt;/code&gt;). Nora’s key &lt;code&gt;AC-999&lt;/code&gt; is not on either Account, so Data Loader cannot resolve that lookup. That failure belongs in the error file Data Loader writes. This recipe does not invent a parent for a missing key. The exact error text was not captured here, because the rows were not loaded into an org.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. What still breaks
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Blank or duplicate External IDs. Unique is what stops two Accounts sharing the same key. Upsert is not a promise of zero duplicates if the field is not unique, or if you use Insert instead of Upsert.&lt;/li&gt;
&lt;li&gt;The parent row was not loaded yet, or you mapped the child file before the Account upsert finished.&lt;/li&gt;
&lt;li&gt;Lookup filters or validation rules can block a row. Treat those as possible causes to check in the error file, not as results tested here.&lt;/li&gt;
&lt;li&gt;000383207 notes that not every reference field can be mapped to an External ID. Polymorphic fields such as Activity &lt;code&gt;WhatId&lt;/code&gt; (Related To) and &lt;code&gt;WhoId&lt;/code&gt; (Name), and a custom object’s Owner field, “do not allow mapping to external IDs.”&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;A successful upsert also does not prove the source key itself was right. If the Contact file already pointed at the wrong company, Data Loader will faithfully attach it there.&lt;/p&gt;

&lt;p&gt;After the load, still review the parents: &lt;a href="https://dev.to/richardc_ailstudio/how-to-catch-wrong-parent-contacts-after-a-salesforce-migration-o65"&gt;wrong-parent Contacts&lt;/a&gt;, &lt;a href="https://dev.to/richardc_ailstudio/how-to-find-orphan-contacts-after-a-salesforce-migration-accountid-is-blank-4adn"&gt;orphan Contacts&lt;/a&gt;, a &lt;a href="https://dev.to/richardc_ailstudio/check-a-salesforce-contact-export-after-a-load-orphans-duplicate-external-ids-and-picklist-17d6"&gt;Contact export check&lt;/a&gt; for orphans, duplicate External IDs and picklist mismatches, and &lt;a href="https://dev.to/richardc_ailstudio/how-to-group-data-loader-error-rows-after-a-salesforce-import-4m15"&gt;grouping Data Loader error rows&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;An External ID upsert can relate Contacts to Accounts without a post-load VLOOKUP. It will not catch a Contact whose source key already pointed at the wrong company — that needs a before-and-after parent-key compare. If you want that check in one browser workbook, &lt;a href="https://ailstudio.gumroad.com/l/ufgfjf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=salesforce-upsert-external-id-load-order" rel="noopener noreferrer"&gt;RecordBridge&lt;/a&gt; (US$49, one-time) compares Account and Contact CSV exports and flags those mismatches. It does not run the load or replace your firm’s go-live sign-off.&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>productivity</category>
    </item>
    <item>
      <title>How to make Amazon, Etsy, and eBay main product photos that meet the rules</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Tue, 10 Nov 2026 05:13:09 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-make-amazon-etsy-and-ebay-main-product-photos-that-meet-the-rules-435l</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-make-amazon-etsy-and-ebay-main-product-photos-that-meet-the-rules-435l</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;AIL Studio&lt;/p&gt;

&lt;p&gt;A rejected main image is usually a small problem: the background is cream instead of white, the file is too small to zoom, the product sits like a postage stamp in the middle of the frame, or someone added a badge the marketplace does not allow. Amazon, Etsy, and eBay are close enough that one good photo can cover them, but they are not the same. This is a checklist for the &lt;em&gt;main&lt;/em&gt; product photo — the one in search and at the top of the listing.&lt;/p&gt;

&lt;p&gt;The details below come from each platform’s public help pages and Seller Central posts, checked in November 2026. Help articles move. If a listing is rejected, trust the site’s current image policy over anything written here.&lt;/p&gt;

&lt;h2&gt;
  
  
  What “white” actually means
&lt;/h2&gt;

&lt;p&gt;Amazon’s main image is the strictest of the three. Seller Support has repeated the same line in public threads: the main product image must have a &lt;strong&gt;pure white background with RGB color values 255, 255, 255&lt;/strong&gt;. That wording appears in &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/613b74cc-011c-48e9-aa3a-5df9b416d7a3" rel="noopener noreferrer"&gt;Failed to add product photo&lt;/a&gt;, in the &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/a40898ca-7fe7-43e2-bb35-7965bbb003d4" rel="noopener noreferrer"&gt;product image aspect ratio&lt;/a&gt; thread, and again when sellers cannot add a photo (&lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/0e3437dc-2b75-42f5-9aad-5f2031353113" rel="noopener noreferrer"&gt;do not let add a product&lt;/a&gt;).&lt;/p&gt;

&lt;p&gt;RGB 255, 255, 255 is not “looks white on my phone.” A warm wall, indoor bulbs on paper, or a JPEG saved from a light-gray canvas will land around 245–252. After you edit, click a corner pixel. If it is not 255, 255, 255, flatten the photo onto a true white canvas and export again.&lt;/p&gt;

&lt;p&gt;eBay is softer. &lt;a href="https://www.ebay.com/help/selling/listings/adding-pictures-listings?id=4148" rel="noopener noreferrer"&gt;Adding pictures to your listings&lt;/a&gt; and &lt;a href="https://www.ebay.com/sellercenter/listings/photo-tips" rel="noopener noreferrer"&gt;Take great photos&lt;/a&gt; say white backgrounds are generally best, with an uncluttered white or neutral backdrop. That is guidance, not Amazon’s hard RGB rule. You can reuse the Amazon file on eBay.&lt;/p&gt;

&lt;p&gt;Etsy does not require a white background. A clean white shot is fine as &lt;em&gt;one&lt;/em&gt; listing image, as long as you also follow the authenticity rule below.&lt;/p&gt;

&lt;h2&gt;
  
  
  How big the file should be
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Amazon.&lt;/strong&gt; Public support posts put the floor at &lt;strong&gt;500 pixels&lt;/strong&gt; on the longest side (sometimes written as 500 × 500). Zoom starts at &lt;strong&gt;at least 1,000 pixels&lt;/strong&gt; on the longest side; “optimal is 1600+,” and support has recommended &lt;strong&gt;2000 × 2000&lt;/strong&gt; for zoom (the &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/613b74cc-011c-48e9-aa3a-5df9b416d7a3" rel="noopener noreferrer"&gt;failed photo&lt;/a&gt; thread; also see &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/0149bdb3-2056-42ce-b0bb-9eef94e3d2b8" rel="noopener noreferrer"&gt;What’s New in 2026&lt;/a&gt; for the size floor). Amazon does not require a square, but a 2000 × 2000 JPEG clears both zoom and fill. JPEG is preferred; stay under &lt;strong&gt;10 MB&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;eBay.&lt;/strong&gt; &lt;a href="https://www.ebay.com/help/selling/listings/adding-pictures-listings?id=4148" rel="noopener noreferrer"&gt;Adding pictures&lt;/a&gt;, &lt;a href="https://www.ebay.com/sellercenter/listings/photo-tips" rel="noopener noreferrer"&gt;Take great photos&lt;/a&gt;, and the &lt;a href="https://www.ebay.com/help/policies/listing-policies/picture-policy?id=4370" rel="noopener noreferrer"&gt;picture policy&lt;/a&gt; put the minimum around &lt;strong&gt;500 × 500&lt;/strong&gt; (the policy says at least 500 pixels on the longest side). The recommendation is about &lt;strong&gt;1600 × 1600&lt;/strong&gt;. The same help page says &lt;strong&gt;1:1 or 16:9&lt;/strong&gt; photos generally look best, so a square 1600 file plus an optional 1600 × 900 companion covers both. Uploads can be up to about &lt;strong&gt;12 MB&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Etsy.&lt;/strong&gt; &lt;a href="https://help.etsy.com/hc/en-us/articles/115015663347-Requirements-and-Best-Practices-for-Images-in-Your-Etsy-Shop" rel="noopener noreferrer"&gt;Requirements and Best Practices for Images in Your Etsy Shop&lt;/a&gt; recommends a width &lt;em&gt;and&lt;/em&gt; height of at least &lt;strong&gt;2000 pixels&lt;/strong&gt;. The first photo should be at least &lt;strong&gt;635 × 635&lt;/strong&gt; so it does not sink in search, and that first photo should be &lt;strong&gt;horizontal or square&lt;/strong&gt;. Easy traps: &lt;strong&gt;transparent PNGs are not supported&lt;/strong&gt; (empty areas turn black), and files larger than about &lt;strong&gt;1 MB&lt;/strong&gt; may stall on a slow connection.&lt;/p&gt;

&lt;p&gt;A set that meets those recommendations: Amazon 2000 × 2000, eBay 1600 × 1600 (plus 1600 × 900 if you want the wide crop), and Etsy 2000 × 2000.&lt;/p&gt;

&lt;h2&gt;
  
  
  Product fill: don’t leave the item floating
&lt;/h2&gt;

&lt;p&gt;Amazon staff restated the main-image fill rule in the &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/a40898ca-7fe7-43e2-bb35-7965bbb003d4" rel="noopener noreferrer"&gt;aspect-ratio thread&lt;/a&gt;: the product must fill &lt;strong&gt;at least 85%&lt;/strong&gt; of the frame. The &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/0149bdb3-2056-42ce-b0bb-9eef94e3d2b8" rel="noopener noreferrer"&gt;2026 photography standards&lt;/a&gt; discussion puts it at &lt;strong&gt;85–100%&lt;/strong&gt;.&lt;/p&gt;

&lt;p&gt;After you place the cutout on a white canvas, the longest side of the product should reach about 85% of that canvas. On a 2000-pixel square, that is roughly 1,700 pixels, centered. If you can see a large empty field around the item, scale it up.&lt;/p&gt;

&lt;p&gt;Etsy crops thumbnails into more than one shape, so a little extra margin (closer to 75% fill) helps the item survive those crops. That is a practical choice, not an Etsy-published percentage. eBay does not publish an 85% rule; filling most of the frame still looks better in search than a distant shot.&lt;/p&gt;

&lt;h2&gt;
  
  
  What you may not add
&lt;/h2&gt;

&lt;p&gt;Amazon’s main-image list, quoted in &lt;a href="https://sellercentral.amazon.com/seller-forums/discussions/t/0e3437dc-2b75-42f5-9aad-5f2031353113" rel="noopener noreferrer"&gt;do not let add a product&lt;/a&gt;, bans text, logos, borders, watermarks, and extra graphics. Show the entire product, only what the buyer receives, and one view of one unit. Lifestyle shots and “what’s in the box” graphics belong on secondary images.&lt;/p&gt;

&lt;p&gt;eBay is in the same spirit. &lt;a href="https://www.ebay.com/help/selling/listings/adding-pictures-listings?id=4148" rel="noopener noreferrer"&gt;Adding pictures&lt;/a&gt; and the &lt;a href="https://www.ebay.com/help/policies/listing-policies/picture-policy?id=4370" rel="noopener noreferrer"&gt;picture policy&lt;/a&gt; ban badges, logos, copyright notices, watermarks, extra text, artwork, marketing material, and borders.&lt;/p&gt;

&lt;p&gt;A soft drop shadow can make a cutout look less like a sticker, but it tints pixels Amazon wants at RGB 255, 255, 255. Skip the shadow on the Amazon main image. If you want one, use it on a secondary photo or on a marketplace that does not require a measured white field.&lt;/p&gt;

&lt;h2&gt;
  
  
  Etsy’s real-photo rule
&lt;/h2&gt;

&lt;p&gt;This is the rule a white-background export cannot replace. Etsy’s &lt;a href="https://help.etsy.com/hc/en-us/articles/115015663347-Requirements-and-Best-Practices-for-Images-in-Your-Etsy-Shop" rel="noopener noreferrer"&gt;image requirements&lt;/a&gt; say a listing must include at least one &lt;strong&gt;unedited photo of the actual item against a real background&lt;/strong&gt;. Editing — including removing or swapping a background — is allowed on &lt;em&gt;other&lt;/em&gt; images, as long as the item still looks like what the buyer will receive.&lt;/p&gt;

&lt;p&gt;So Etsy needs two files, not one: keep a straight photo of the real object in its real setting, and you may also upload a clean white-background shot as another image. If every photo is an edited studio cutout, you are missing the authenticity image, even when the cutouts are honest pictures of your own work.&lt;/p&gt;

&lt;h2&gt;
  
  
  A repeatable way to shoot and export
&lt;/h2&gt;

&lt;p&gt;Photograph the actual product, not a similar stock image. Window light or two cheap lights beats a yellow lamp on one side and a blue window on the other. A white foam board opposite the window fills shadows. Shoot a little larger than you need; it is easier to crop than to invent detail.&lt;/p&gt;

&lt;p&gt;Remove the background in whatever editor you already use, paste the cutout onto RGB 255, 255, 255, and check a corner pixel. Scale the product to about 85% of the frame for Amazon and eBay; leave a bit more room if the same file will be an Etsy first photo. Export JPEGs at 2000 × 2000 (Amazon and Etsy) and 1600 × 1600 (eBay), plus 1600 × 900 if you want eBay’s wide option. Do not stamp a price, shop name, or watermark on these main files.&lt;/p&gt;

&lt;p&gt;Before you upload: Is the background truly 255, 255, 255? Is the whole product visible? Is there any text? Is the file big enough for zoom? For Etsy, is there still an unedited, real-background photo of the actual item in the same listing?&lt;/p&gt;

&lt;p&gt;If you want that export in one step, &lt;a href="https://poe.com/ListingPhotoSet?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=listingphotoset-howto" rel="noopener noreferrer"&gt;Listing Photo Set&lt;/a&gt; on Poe will place a product photo on a pure white canvas and return the common marketplace sizes; you try it there and spend Poe points to run it.&lt;/p&gt;

</description>
      <category>ecommerce</category>
      <category>amazon</category>
      <category>etsy</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>How to group Data Loader error rows after a Salesforce import</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Mon, 09 Nov 2026 21:49:21 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-group-data-loader-error-rows-after-a-salesforce-import-4m15</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-group-data-loader-error-rows-after-a-salesforce-import-4m15</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;A Salesforce Data Loader insert or update can finish with some rows loaded and others rejected. Those rejected rows land in an error CSV. Salesforce’s Data Loader guide says the tool writes two CSVs whose names begin with &lt;code&gt;success&lt;/code&gt; and &lt;code&gt;error&lt;/code&gt;. The success file lists loaded rows, including new record IDs. The error file lists rejected rows “with a column that describes why the load failed” (&lt;a href="https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/reviewing_output_files.htm" rel="noopener noreferrer"&gt;Review Data Loader Output Files&lt;/a&gt;).&lt;/p&gt;

&lt;p&gt;When the same message appears many times, group by the ERROR text, fix each group once, then reload only the corrected error file. Salesforce Help describes that pattern for a missing required field: open the error CSV, add the value, save, and import the error file again (&lt;a href="https://help.salesforce.com/s/articleView?id=000390010&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Fixing Import Errors: REQUIRED_FIELD_MISSING&lt;/a&gt;).&lt;/p&gt;

&lt;p&gt;The table below is &lt;strong&gt;EXAMPLE DATA&lt;/strong&gt;, not a customer org.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Open the error file, not only the success file
&lt;/h2&gt;

&lt;p&gt;In Data Loader, use View Successes / View Errors, or open the CSVs from the folder you chose at Finish (&lt;a href="https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/reviewing_output_files.htm" rel="noopener noreferrer"&gt;Review Data Loader Output Files&lt;/a&gt;). The success file answers how many rows landed. The error file answers which rows did not, and why.&lt;/p&gt;

&lt;p&gt;A row that loaded under the wrong Account will not appear here. This step only covers rows Data Loader rejected. If a source column is named &lt;code&gt;success&lt;/code&gt; or &lt;code&gt;error&lt;/code&gt;, Salesforce notes that output columns can display incorrectly — rename those headers first.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. EXAMPLE DATA
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;error_contacts.csv (EXAMPLE DATA)&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Data Loader often writes a code, then a longer reason. Group on the repeated prefix, not on a record Id later in the sentence.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;LastName&lt;/th&gt;
&lt;th&gt;Email&lt;/th&gt;
&lt;th&gt;AccountId&lt;/th&gt;
&lt;th&gt;Legacy_Id__c&lt;/th&gt;
&lt;th&gt;ERROR&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:renee@example.invalid"&gt;renee@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000001AAA&lt;/td&gt;
&lt;td&gt;CT-001&lt;/td&gt;
&lt;td&gt;DUPLICATE_VALUE: duplicate value found: Legacy_Id__c duplicates value on record with id: 003000000000099AAA&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:sam@example.invalid"&gt;sam@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000001AAA&lt;/td&gt;
&lt;td&gt;CT-002&lt;/td&gt;
&lt;td&gt;REQUIRED_FIELD_MISSING: Required fields are missing: [LastName]&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:jo@example.invalid"&gt;jo@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000999AAA&lt;/td&gt;
&lt;td&gt;CT-003&lt;/td&gt;
&lt;td&gt;INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY: insufficient access rights on cross-reference id&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:lee@example.invalid"&gt;lee@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000001AAA&lt;/td&gt;
&lt;td&gt;CT-001&lt;/td&gt;
&lt;td&gt;DUPLICATE_VALUE: duplicate value found: Legacy_Id__c duplicates value on record with id: 003000000000099AAA&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:kai@example.invalid"&gt;kai@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000002AAA&lt;/td&gt;
&lt;td&gt;CT-004&lt;/td&gt;
&lt;td&gt;REQUIRED_FIELD_MISSING: Required fields are missing: [LastName]&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Expected groups: two &lt;code&gt;DUPLICATE_VALUE&lt;/code&gt; rows, two &lt;code&gt;REQUIRED_FIELD_MISSING&lt;/code&gt; rows, and one &lt;code&gt;INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY&lt;/code&gt; row. The Ids are fabricated placeholders.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Group the ERROR column
&lt;/h2&gt;

&lt;p&gt;On a sheet named &lt;code&gt;Errors&lt;/code&gt;, with &lt;code&gt;ERROR&lt;/code&gt; in column E, put this in &lt;code&gt;F2&lt;/code&gt; and fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(E2="","",IFERROR(LEFT(E2,FIND(":",E2)-1),E2))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Count each group in &lt;code&gt;G2&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(F2="","",COUNTIF(F:F,F2))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Copy column F, paste values, and remove duplicates to get the unique keys. Filter column F and work one group at a time.&lt;/p&gt;

&lt;p&gt;The same extract in Python (standard library only). Change the path to your file. &lt;strong&gt;EXAMPLE DATA&lt;/strong&gt; until then.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;csv&lt;/span&gt;
&lt;span class="kn"&gt;from&lt;/span&gt; &lt;span class="n"&gt;collections&lt;/span&gt; &lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;Counter&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;group_key&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;message&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
    &lt;span class="n"&gt;message&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;message&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;).&lt;/span&gt;&lt;span class="nf"&gt;strip&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;message&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;split&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;:&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;message&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;(blank)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;

&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="nf"&gt;open&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;error_contacts.csv&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;newline&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;encoding&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;utf-8&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;handle&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;rows&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;list&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;csv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;DictReader&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;handle&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;

&lt;span class="n"&gt;counts&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nc"&gt;Counter&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;group_key&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;ERROR&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;key&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;counts&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;most_common&lt;/span&gt;&lt;span class="p"&gt;():&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;key&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;On the example file this prints &lt;code&gt;2 DUPLICATE_VALUE&lt;/code&gt;, &lt;code&gt;2 REQUIRED_FIELD_MISSING&lt;/code&gt;, and &lt;code&gt;1 INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;If two duplicate-value rows differ only by the Id at the end, text before the first colon still groups them. If there is no colon, search the column for a shared phrase such as &lt;code&gt;duplicate value found&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Fix each group from the documented cause
&lt;/h2&gt;

&lt;p&gt;Salesforce Help lists common Data Loader messages with a cause and a fix (&lt;a href="https://help.salesforce.com/s/articleView?id=000384437&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Common error messages in Data Loader&lt;/a&gt;). Map your groups to that list.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;DUPLICATE_VALUE&lt;/code&gt; / “Duplicate value found.”&lt;/strong&gt; Help says this is a duplicate in a field marked Unique. Find that mapped field and change the colliding values. In the example, &lt;code&gt;Legacy_Id__c&lt;/code&gt; value &lt;code&gt;CT-001&lt;/code&gt; appears twice.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;REQUIRED_FIELD_MISSING&lt;/code&gt;.&lt;/strong&gt; Help says a required field was missing or empty. Add the value in the error CSV and import that file again (&lt;a href="https://help.salesforce.com/s/articleView?id=000390010&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;REQUIRED_FIELD_MISSING&lt;/a&gt;). In the example, &lt;code&gt;LastName&lt;/code&gt; is blank.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;“Insufficient access rights on cross-reference id.”&lt;/strong&gt; Help lists typical causes: the referenced record is deleted, never existed (a typo in the Id), or is not shared with the running user or with the owner of the row you are loading. Reduce batch size to 1 while you find the bad Id. Do not invent an Account Id to clear the error.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;If most rows already succeeded, do not reload the original full file — you risk duplicates on the success set. Reload the corrected error rows.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. What grouping does not prove
&lt;/h2&gt;

&lt;p&gt;Rows in the success file can still have a blank or wrong &lt;code&gt;AccountId&lt;/code&gt;; they will not show up here. Two different validation-rule messages are different groups even if they feel similar. The error file names a cross-reference failure, not the sharing rule behind it. No Salesforce org was queried for this article. Re-check the steps against your firm’s load rules.&lt;/p&gt;

&lt;p&gt;AIL Studio is not affiliated with Salesforce. Salesforce is a trademark of Salesforce, Inc.&lt;/p&gt;

&lt;p&gt;If you would rather send Contact and Account exports and get a findings workbook back, AIL Studio offers a $19 Salesforce Export Check: &lt;a href="https://buy.stripe.com/cNiaEW22C2tbbv5fIu6Na07?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=payperfile-sf-check" rel="noopener noreferrer"&gt;order the $19 check&lt;/a&gt;. After you pay, email the exports to &lt;a href="mailto:madebyailstudio@gmail.com"&gt;madebyailstudio@gmail.com&lt;/a&gt; (no passwords or org logins). You will receive a findings workbook; please allow up to 3 business days after we receive a usable export.&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>dataloader</category>
      <category>csv</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Did that cancellation actually work? A cancellation check log in Google Sheets</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Mon, 09 Nov 2026 21:20:08 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/did-that-cancellation-actually-work-a-cancellation-check-log-in-google-sheets-55g</link>
      <guid>https://dev.to/richardc_ailstudio/did-that-cancellation-actually-work-a-cancellation-check-log-in-google-sheets-55g</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;The formulas below were tested in Google Sheets. They were also previously checked in LibreOffice Calc. Excel and spreadsheet UI menu steps were not tested.&lt;/p&gt;

&lt;p&gt;Cancelling a subscription is an action. Seeing that the expected charge stopped is a separate check. A confirmation email helps, but it is easy to forget to look at the next statement, especially when the service bills annually.&lt;/p&gt;

&lt;p&gt;This small log closes that loop: record when you cancelled, estimate when the next charge would have arrived, then check your statement after a buffer. It does not cancel anything, read your bank account or send alerts. You supply the evidence and decide what needs following up.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Give each cancelled service one row
&lt;/h2&gt;

&lt;p&gt;Create a sheet with these headers in row 1:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Column&lt;/th&gt;
&lt;th&gt;Header&lt;/th&gt;
&lt;th&gt;What goes here&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;A&lt;/td&gt;
&lt;td&gt;Service&lt;/td&gt;
&lt;td&gt;Your name for the cancelled service&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;B&lt;/td&gt;
&lt;td&gt;Cancelled on&lt;/td&gt;
&lt;td&gt;Date you completed cancellation&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;C&lt;/td&gt;
&lt;td&gt;Last charge date&lt;/td&gt;
&lt;td&gt;Previous charge from your statement&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;D&lt;/td&gt;
&lt;td&gt;Cycle&lt;/td&gt;
&lt;td&gt;Monthly, Yearly or Weekly&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;E&lt;/td&gt;
&lt;td&gt;Amount&lt;/td&gt;
&lt;td&gt;Previous charge amount&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;F&lt;/td&gt;
&lt;td&gt;Next charge would have been&lt;/td&gt;
&lt;td&gt;Formula estimate&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;G&lt;/td&gt;
&lt;td&gt;Check on&lt;/td&gt;
&lt;td&gt;Estimated date plus buffer&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;H&lt;/td&gt;
&lt;td&gt;Charged again?&lt;/td&gt;
&lt;td&gt;Yes/No, entered after checking&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;I&lt;/td&gt;
&lt;td&gt;Status&lt;/td&gt;
&lt;td&gt;Formula&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;J&lt;/td&gt;
&lt;td&gt;Confirmation # / notes&lt;/td&gt;
&lt;td&gt;Reference and what you checked&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Keep B and C as real spreadsheet dates, not pasted date-looking text. Format E as currency and F/G as dates. Use a dropdown for D with only the three supported cycles, and another for H with Yes and No; leave H empty until you have checked. Keep confirmation references in J without storing passwords or full payment-card details.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Estimate the missed renewal
&lt;/h2&gt;

&lt;p&gt;In F2, enter this formula and fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(C2="","",IF(D2="Monthly",EDATE(C2,1),IF(D2="Yearly",EDATE(C2,12),IF(D2="Weekly",C2+7,""))))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;a href="https://support.google.com/docs/answer/3092974" rel="noopener noreferrer"&gt;Google's EDATE help&lt;/a&gt; describes adding months to a starting date and requires a date value or date serial. Monthly means one calendar month, not thirty days; Yearly means twelve months; Weekly adds seven days. A missing last-charge date or unsupported cycle leaves F blank rather than guessing.&lt;/p&gt;

&lt;p&gt;This estimates the next date from one previous charge. It is not a prediction of a merchant's actual posting schedule. Check the service's cancellation terms and renewal date, particularly if cancellation takes effect only after a paid period ends.&lt;/p&gt;

&lt;p&gt;Put &lt;strong&gt;3&lt;/strong&gt; in L1 as an example buffer in days, with “Check buffer (days)” in K1. Pick your own nonnegative whole-number buffer: posting delays vary. In G2, then fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(F2="","",F2+$L$1)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  3. Let the status prompt a manual check
&lt;/h2&gt;

&lt;p&gt;In I2, fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(H2="Yes","Charged after cancel — contact merchant",IF(H2="No","Confirmed stopped",IF(G2="","",IF(TODAY()&amp;gt;=G2,"Check statement now","Waiting"))))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;a href="https://support.google.com/docs/answer/3092984" rel="noopener noreferrer"&gt;Google's TODAY documentation&lt;/a&gt; says it reflects the current date at the last spreadsheet recalculation. Nothing is pushed to you. Open and review the sheet yourself; this is not a scheduled notification system.&lt;/p&gt;

&lt;p&gt;Yes and No take priority over dates. “Confirmed stopped” means you entered No after checking the relevant period; it is not an independent guarantee. An empty H stays Waiting until G arrives, then becomes Check statement now. A blank G leaves the unreviewed status blank: fix missing inputs rather than reading silence as success.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Walk through four rows
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data (not real services or charges).&lt;/strong&gt; All dates and outcomes below are invented. For this table only, use a fixed &lt;strong&gt;as-of date of 2026-11-08 instead of TODAY()&lt;/strong&gt; so the example stays reproducible. The working formula above still uses TODAY(). L1 is 3.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Service&lt;/th&gt;
&lt;th&gt;Cancelled on&lt;/th&gt;
&lt;th&gt;Last charge&lt;/th&gt;
&lt;th&gt;Cycle&lt;/th&gt;
&lt;th&gt;Amount&lt;/th&gt;
&lt;th&gt;F&lt;/th&gt;
&lt;th&gt;G&lt;/th&gt;
&lt;th&gt;H&lt;/th&gt;
&lt;th&gt;I&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Example Streaming&lt;/td&gt;
&lt;td&gt;2026-10-20&lt;/td&gt;
&lt;td&gt;2026-10-01&lt;/td&gt;
&lt;td&gt;Monthly&lt;/td&gt;
&lt;td&gt;15.00&lt;/td&gt;
&lt;td&gt;2026-11-01&lt;/td&gt;
&lt;td&gt;2026-11-04&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;Confirmed stopped&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example Fitness app&lt;/td&gt;
&lt;td&gt;2026-10-25&lt;/td&gt;
&lt;td&gt;2026-10-05&lt;/td&gt;
&lt;td&gt;Monthly&lt;/td&gt;
&lt;td&gt;12.00&lt;/td&gt;
&lt;td&gt;2026-11-05&lt;/td&gt;
&lt;td&gt;2026-11-08&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;td&gt;Charged after cancel — contact merchant&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example Cloud storage&lt;/td&gt;
&lt;td&gt;2026-09-20&lt;/td&gt;
&lt;td&gt;2025-10-01&lt;/td&gt;
&lt;td&gt;Yearly&lt;/td&gt;
&lt;td&gt;99.00&lt;/td&gt;
&lt;td&gt;2026-10-01&lt;/td&gt;
&lt;td&gt;2026-10-04&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;Confirmed stopped&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example Meal kit&lt;/td&gt;
&lt;td&gt;2026-11-08&lt;/td&gt;
&lt;td&gt;2026-11-07&lt;/td&gt;
&lt;td&gt;Weekly&lt;/td&gt;
&lt;td&gt;9.00&lt;/td&gt;
&lt;td&gt;2026-11-14&lt;/td&gt;
&lt;td&gt;2026-11-17&lt;/td&gt;
&lt;td&gt;blank&lt;/td&gt;
&lt;td&gt;Waiting&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The two No entries represent completed statement checks. The Yes entry needs investigation; the blank entry has not been checked yet. Record the statement period and confirmation reference in J, not just a tick. These example outcomes are not actual cancellation results.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Make review dates visible, not automatic
&lt;/h2&gt;

&lt;p&gt;Untested Google Sheets UI instruction, based on Google's linked Help guide (not a verified UI test): Select the status cells, then use &lt;strong&gt;Format → Conditional formatting&lt;/strong&gt;. Add text-equals rules for “Check statement now” and “Charged after cancel — contact merchant”. Use a restrained fill and retain the text so meaning does not depend on color.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://support.google.com/docs/answer/78413" rel="noopener noreferrer"&gt;Google's formatting guide&lt;/a&gt; explains changing a cell's appearance when a condition matches. That is visual formatting when you view the sheet, not a notification. If you want a reminder, manually put G's date into your own calendar. No script or calendar connection is included here.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. If another charge appears
&lt;/h2&gt;

&lt;p&gt;Check the merchant description, billing period and effective cancellation date first. A final bill, delayed posting or app-store subscription may need a different explanation. Contact the merchant or app store with the confirmation reference and dates, and keep their response in your notes. This log does not promise refunds.&lt;/p&gt;

&lt;p&gt;Annual plans, pauses, changing prices and store-managed billing can depart from this estimate. One quiet statement does not prove a service can never charge again. Recheck after a new charge or disputed cancellation; the sheet remains a record, not proof supplied by your bank.&lt;/p&gt;

&lt;p&gt;These formulas were tested in Google Sheets with the Example data rows, plus blank-input, unsupported-cycle and status-priority cases. They were also previously checked in LibreOffice Calc with example fixtures, including month-end, blank-input and status-priority cases. Excel was not tested; verify it in your own copy. Some regional settings use semicolons instead of commas between formula arguments and show dates in a different order; adjust the separators and enter dates your spreadsheet recognises as dates. Other regional settings and how a spreadsheet parses typed dates were not tested. Keep the log separate from any ready-made product unless you choose to add it yourself.&lt;/p&gt;

&lt;p&gt;Before relying on a blank status, check C and D. The formula does not validate every input: a text date can produce a spreadsheet error, and an unsupported cycle intentionally produces no estimated date. Neither means cancellation succeeded. Likewise, changing H to No immediately changes the status, even before the planned check date. Only type that answer when your statement review supports it. If you discover you checked the wrong period, clear H and correct the notes instead of leaving a reassuring result behind.&lt;/p&gt;

&lt;p&gt;If you want a ready-made file for active subscriptions (renewal countdown, dashboard, six billing cycles, room for 100 rows), the &lt;a href="https://ailstudio.gumroad.com/l/qkwkix?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=cancel-check-log" rel="noopener noreferrer"&gt;Subscription Tracker&lt;/a&gt; is a pay-what-you-want &lt;strong&gt;.xlsx&lt;/strong&gt; for &lt;strong&gt;Excel and Google Sheets&lt;/strong&gt; (name your price from $1; not free). Cancelled rows can stay listed without counting in totals. It does not include this cancellation-check log, cancel services for you, or send reminders.&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>productivity</category>
      <category>personalfinance</category>
      <category>spreadsheets</category>
    </item>
    <item>
      <title>Check a Salesforce Contact export after a load: orphans, duplicate External IDs, and picklist mismatches</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Mon, 09 Nov 2026 21:19:38 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/check-a-salesforce-contact-export-after-a-load-orphans-duplicate-external-ids-and-picklist-17d6</link>
      <guid>https://dev.to/richardc_ailstudio/check-a-salesforce-contact-export-after-a-load-orphans-duplicate-external-ids-and-picklist-17d6</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;After a Salesforce insert or upsert, the success file can say every row loaded while the Contact export still has problems you can see without logging into Setup. A Contact can have a blank &lt;code&gt;AccountId&lt;/code&gt;. Two Contacts can share the same External ID. A picklist column can hold a value that is not in the field’s allowed API names.&lt;/p&gt;

&lt;p&gt;This walkthrough checks the &lt;strong&gt;files&lt;/strong&gt; you already exported. You will count orphans, find AccountIds that do not exist in the Account file, find duplicate External IDs, and compare one picklist column to an allowed-value list. Optional sections repeat the same work in Python (standard library only) and confirm the counts with SOQL.&lt;/p&gt;

&lt;p&gt;All tables below are &lt;strong&gt;EXAMPLE DATA&lt;/strong&gt;. They are not from a customer org. Replace column headers such as &lt;code&gt;Legacy_Id__c&lt;/code&gt; with the External ID your org actually uses.&lt;/p&gt;

&lt;p&gt;Salesforce Help documents that External ID values are used to match related records in Data Loader (&lt;a href="https://help.salesforce.com/s/articleView?id=000383207&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Import Related Records with an External ID&lt;/a&gt;), and that a duplicate in a unique field produces a “Duplicate value found” error (&lt;a href="https://help.salesforce.com/s/articleView?id=000384437&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Common error messages in Data Loader&lt;/a&gt;). Picklist values in a CSV must match a value in the field; restricted picklists reject values that are not in the defined set (&lt;a href="https://help.salesforce.com/s/articleView?id=000381876&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Prepare a CSV File for an Import or Update&lt;/a&gt;, &lt;a href="https://help.salesforce.com/s/articleView?id=000380951&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST&lt;/a&gt;). If the picklist is &lt;strong&gt;not&lt;/strong&gt; restricted, Data Loader can write a value that is not in the defined list (&lt;a href="https://help.salesforce.com/s/articleView?id=000381951&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Data Loader Allows Invalid Picklist Values to Be Created&lt;/a&gt;). Checking the export against the allowed API names catches both cases.&lt;/p&gt;

&lt;p&gt;SOQL results store picklist &lt;strong&gt;API names&lt;/strong&gt;, not the labels users see (&lt;a href="https://help.salesforce.com/s/articleView?id=004205234&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;Understanding Picklist Value Labels and API Names&lt;/a&gt;). Compare your file to API names, not to the label column in Setup, unless you have confirmed they are identical.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Export the two files
&lt;/h2&gt;

&lt;p&gt;Use Data Loader (or another export path your admin already approves) and include at least:&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Contacts&lt;/strong&gt; — &lt;code&gt;Id&lt;/code&gt;, &lt;code&gt;AccountId&lt;/code&gt;, &lt;code&gt;LastName&lt;/code&gt;, &lt;code&gt;Email&lt;/code&gt;, your Contact External ID, and one picklist (this example uses &lt;code&gt;LeadSource&lt;/code&gt;; replace it with the picklist you actually loaded).&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Accounts&lt;/strong&gt; — &lt;code&gt;Id&lt;/code&gt; and the Account External ID if you have one.&lt;/p&gt;

&lt;p&gt;Keep Salesforce &lt;code&gt;Id&lt;/code&gt; in every file. Prefer &lt;code&gt;Id&lt;/code&gt; later if you build an update file. Do not invent Account Ids in the sheet.&lt;/p&gt;

&lt;p&gt;A Contacts &lt;strong&gt;report&lt;/strong&gt; export is fine for a small review set. Use Data Loader (or an equivalent bulk export) when you need every row; some reports do not return the full object.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. EXAMPLE DATA
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;contacts.csv (EXAMPLE DATA)&lt;/strong&gt;&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Id&lt;/th&gt;
&lt;th&gt;LastName&lt;/th&gt;
&lt;th&gt;Email&lt;/th&gt;
&lt;th&gt;AccountId&lt;/th&gt;
&lt;th&gt;Legacy_Id__c&lt;/th&gt;
&lt;th&gt;LeadSource&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;003000000000001AAA&lt;/td&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:renee@example.invalid"&gt;renee@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000001AAA&lt;/td&gt;
&lt;td&gt;CT-001&lt;/td&gt;
&lt;td&gt;Web&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;003000000000002AAA&lt;/td&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:sam@example.invalid"&gt;sam@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;CT-002&lt;/td&gt;
&lt;td&gt;Phone Inquiry&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;003000000000003AAA&lt;/td&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:jo@example.invalid"&gt;jo@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000999AAA&lt;/td&gt;
&lt;td&gt;CT-003&lt;/td&gt;
&lt;td&gt;Phne Inquiry&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;003000000000004AAA&lt;/td&gt;
&lt;td&gt;Example&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:lee@example.invalid"&gt;lee@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;001000000000001AAA&lt;/td&gt;
&lt;td&gt;CT-001&lt;/td&gt;
&lt;td&gt;Partner Referral&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;strong&gt;accounts.csv (EXAMPLE DATA)&lt;/strong&gt;&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Id&lt;/th&gt;
&lt;th&gt;Name&lt;/th&gt;
&lt;th&gt;Legacy_Id__c&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;001000000000001AAA&lt;/td&gt;
&lt;td&gt;Aster Labs&lt;/td&gt;
&lt;td&gt;AC-001&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;001000000000002AAA&lt;/td&gt;
&lt;td&gt;Blue Orchard&lt;/td&gt;
&lt;td&gt;AC-002&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;strong&gt;allowed_leadsource.csv (EXAMPLE DATA)&lt;/strong&gt; — API names from the picklist value set:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;LeadSource&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Web&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Phone Inquiry&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Partner Referral&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Expected findings on this set:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;One orphan: Sam, because &lt;code&gt;AccountId&lt;/code&gt; is blank.&lt;/li&gt;
&lt;li&gt;One unresolved parent: Jo, because &lt;code&gt;001000000000999AAA&lt;/code&gt; is not in the Account file.&lt;/li&gt;
&lt;li&gt;One duplicated External ID: &lt;code&gt;CT-001&lt;/code&gt; appears on Renée and Lee.&lt;/li&gt;
&lt;li&gt;One picklist mismatch: Jo’s &lt;code&gt;Phne Inquiry&lt;/code&gt; is not in the allowed list.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The Ids above are fabricated placeholders, not real Salesforce Ids.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Spreadsheet checks
&lt;/h2&gt;

&lt;p&gt;Put Contacts on a sheet named &lt;code&gt;Contacts&lt;/code&gt; (headers in row 1). Put Accounts on a sheet named &lt;code&gt;Accounts&lt;/code&gt;. Put allowed picklist values in &lt;code&gt;Allowed&lt;/code&gt; starting at &lt;code&gt;A2&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;Treat a blank &lt;code&gt;AccountId&lt;/code&gt; cell as an orphan candidate. If your export tool writes &lt;code&gt;#N/A&lt;/code&gt; or the literal &lt;code&gt;null&lt;/code&gt;, normalize those to blank before you count.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Orphans (blank AccountId)&lt;/strong&gt; in &lt;code&gt;G2&lt;/code&gt;, filled down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(D2="","orphan","")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Count:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF(G:G,"orphan")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Unresolved AccountId&lt;/strong&gt; (a value is present but it does not appear in the Account &lt;code&gt;Id&lt;/code&gt; column). In &lt;code&gt;H2&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(D2="","",IF(COUNTIF(Accounts!A:A,D2)=0,"unresolved",""))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Count:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF(H:H,"unresolved")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Duplicate External ID&lt;/strong&gt; (&lt;code&gt;Legacy_Id__c&lt;/code&gt; in column E). In &lt;code&gt;I2&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(E2="","",IF(COUNTIF(E:E,E2)&amp;gt;1,"duplicate external id",""))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Count of flagged rows (each extra copy is flagged, including the first):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF(I:I,"duplicate external id")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Picklist not in the allowed API names.&lt;/strong&gt; In &lt;code&gt;J2&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(F2="","",IF(COUNTIF(Allowed!A:A,F2)=0,"picklist mismatch",""))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;COUNTIF&lt;/code&gt; here is case-insensitive in common spreadsheet tools, so &lt;code&gt;web&lt;/code&gt; and &lt;code&gt;Web&lt;/code&gt; would both match. If your org’s picklist is case-sensitive at the API-name level, use an exact-match method (&lt;code&gt;EXACT&lt;/code&gt; in an array, or the Python section below).&lt;/p&gt;

&lt;p&gt;Filter each flag column to copy the flagged rows onto a review sheet. Do not attach an orphan to the first Account that “looks similar.” A wrong &lt;code&gt;AccountId&lt;/code&gt; is a different defect from a blank one.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Optional: the same checks in Python
&lt;/h2&gt;

&lt;p&gt;This script uses only the standard library. Save it next to the three example CSVs and run &lt;code&gt;python check_export.py&lt;/code&gt;. It prints counts and the flagged External IDs or row emails. &lt;strong&gt;EXAMPLE DATA&lt;/strong&gt; only until you point it at your own files.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;csv&lt;/span&gt;
&lt;span class="kn"&gt;from&lt;/span&gt; &lt;span class="n"&gt;collections&lt;/span&gt; &lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;Counter&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;path&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
    &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="nf"&gt;open&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;path&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;newline&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;encoding&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;utf-8&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;handle&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nf"&gt;list&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;csv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;DictReader&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;handle&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;

&lt;span class="n"&gt;contacts&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;contacts.csv&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;account_ids&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Id&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="nf"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;accounts.csv&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)}&lt;/span&gt;
&lt;span class="n"&gt;allowed&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;LeadSource&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="nf"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;allowed_leadsource.csv&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)}&lt;/span&gt;

&lt;span class="n"&gt;orphans&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;AccountId&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;).&lt;/span&gt;&lt;span class="nf"&gt;strip&lt;/span&gt;&lt;span class="p"&gt;()]&lt;/span&gt;
&lt;span class="n"&gt;unresolved&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt;
    &lt;span class="nf"&gt;if &lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;AccountId&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;).&lt;/span&gt;&lt;span class="nf"&gt;strip&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="ow"&gt;and&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;AccountId&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;account_ids&lt;/span&gt;
&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="n"&gt;ext_counts&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nc"&gt;Counter&lt;/span&gt;&lt;span class="p"&gt;((&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Legacy_Id__c&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;).&lt;/span&gt;&lt;span class="nf"&gt;strip&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;dup_ext&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;value&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;ext_counts&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;value&lt;/span&gt; &lt;span class="ow"&gt;and&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="n"&gt;picklist_miss&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;contacts&lt;/span&gt;
    &lt;span class="nf"&gt;if &lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;LeadSource&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;).&lt;/span&gt;&lt;span class="nf"&gt;strip&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="ow"&gt;and&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;LeadSource&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;allowed&lt;/span&gt;
&lt;span class="p"&gt;]&lt;/span&gt;

&lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;orphan_count&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;orphans&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;unresolved_count&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;unresolved&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;duplicate_external_ids&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;dup_ext&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;picklist_mismatch_count&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;picklist_miss&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;orphans&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;orphan&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Email&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Legacy_Id__c&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;unresolved&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;unresolved&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Email&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;AccountId&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;picklist_miss&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;picklist&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Email&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;LeadSource&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;On the example files this prints one orphan, one unresolved AccountId, duplicate External ID &lt;code&gt;CT-001&lt;/code&gt;, and one picklist mismatch (&lt;code&gt;Phne Inquiry&lt;/code&gt;). Re-run it against your export after you change the file names and field headers.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Optional: confirm the counts with SOQL
&lt;/h2&gt;

&lt;p&gt;The file is not always the same as the org (filters, permissions, a report cap). If you can run SOQL, compare the file counts to the live object.&lt;/p&gt;

&lt;p&gt;Orphans:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;List:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;LastName&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Email&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Legacy_Id__c&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Replace &lt;code&gt;Legacy_Id__c&lt;/code&gt; with your field. Duplicate External IDs in the org:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Legacy_Id__c&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;Legacy_Id__c&lt;/span&gt;
&lt;span class="k"&gt;HAVING&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;GROUP BY&lt;/code&gt; / &lt;code&gt;HAVING&lt;/code&gt; needs the field to be groupable; if your External ID is not, use the spreadsheet or Python check on a full Data Loader export instead.&lt;/p&gt;

&lt;p&gt;A picklist value that is not in the allowed set will not always appear as a SOQL error after the fact. If the field was unrestricted at load time, the stored API name can be a string that Setup does not list. That is why the file-versus-allowed-list check still matters after a “successful” load.&lt;/p&gt;

&lt;p&gt;No Salesforce org was queried to write this article. Paste the queries in a tool you already have permission to use (Developer Console, VS Code, Workbench, or your approved admin path).&lt;/p&gt;

&lt;h2&gt;
  
  
  6. What these checks do not prove
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;A blank &lt;code&gt;AccountId&lt;/code&gt; can be an intentional private Contact. Treat orphans as a review list, not a delete list.&lt;/li&gt;
&lt;li&gt;An AccountId that exists but points at the &lt;strong&gt;wrong&lt;/strong&gt; company will not show up as unresolved. That needs a source-to-target key compare, which is a separate check.&lt;/li&gt;
&lt;li&gt;Duplicate names or emails are not the same as duplicate External IDs. This article only flags the External ID column you name.&lt;/li&gt;
&lt;li&gt;Permissions matter: SOQL you cannot see, you cannot count.&lt;/li&gt;
&lt;li&gt;Matching a picklist label instead of its API name will produce false mismatches (&lt;a href="https://help.salesforce.com/s/articleView?id=004205234&amp;amp;language=en_US&amp;amp;type=1" rel="noopener noreferrer"&gt;picklist labels vs API names&lt;/a&gt;).&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Do not load a “fix” file until someone has read the flagged rows.&lt;/p&gt;

&lt;p&gt;AIL Studio is not affiliated with Salesforce. Salesforce is a trademark of Salesforce, Inc.&lt;/p&gt;

&lt;p&gt;If you would rather have someone run these checks on your exports and send back a findings report, AIL Studio offers a &lt;a href="https://www.fiverr.com/ail_studio/check-salesforce-data-after-a-migration?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-postmig-qa-gig" rel="noopener noreferrer"&gt;Salesforce post-migration data check on Fiverr&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>csv</category>
      <category>soql</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>How to find orphan Contacts after a Salesforce migration (AccountId is blank)</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Mon, 09 Nov 2026 17:31:17 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-find-orphan-contacts-after-a-salesforce-migration-accountid-is-blank-4adn</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-find-orphan-contacts-after-a-salesforce-migration-accountid-is-blank-4adn</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;After a Salesforce migration, a Contact can keep every field value and still have no Account. That blank &lt;code&gt;AccountId&lt;/code&gt; is a different problem from a &lt;strong&gt;wrong parent&lt;/strong&gt;. An orphan Contact has no Account. A wrong-parent Contact still has an &lt;code&gt;AccountId&lt;/code&gt;, but it points at the wrong company — covered in &lt;a href="https://dev.to/richardc_ailstudio/how-to-catch-wrong-parent-contacts-after-a-salesforce-migration-o65"&gt;How to catch wrong-parent Contacts after a Salesforce migration&lt;/a&gt; and the SOQL parent audit in &lt;a href="https://dev.to/richardc_ailstudio/how-to-audit-contact-account-parents-after-a-salesforce-migration-with-soql-18g8"&gt;How to Audit Contact–Account Parents After a Salesforce Migration with SOQL&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;Blank &lt;code&gt;AccountId&lt;/code&gt; also changes who can see the record. Per Salesforce Help, Contacts not linked to an Account are private: only the owner and administrators with View/Modify All Data can see them, and Contact sharing rules do not apply. So an orphan query is a candidate review population for go-live — not automatic proof the migration failed, and not a list you should mass-attach without reading each row.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Why orphans show up after a load
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example causes (not exhaustive):&lt;/strong&gt; Accounts loaded after Contacts; the AccountId map missed rows; the source already had private Contacts; a parent lookup failed on insert. Salesforce’s migration guidance stresses importing related objects in a workable order and replacing legacy IDs so relationships reattach — process context, not a diagnosis of your specific file.&lt;/p&gt;

&lt;p&gt;A blank &lt;code&gt;AccountId&lt;/code&gt; in the target is one finding. Decide whether that Contact should stay private, attach to a known Account, or need investigation before anyone rewrites data.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Find them with SOQL
&lt;/h2&gt;

&lt;p&gt;Use null in the WHERE clause for the lookup. Salesforce’s SOQL null guide shows searching with the &lt;code&gt;null&lt;/code&gt; keyword; the boolean-null quirk (null matching FALSE) applies to boolean fields, not to this Account lookup Id check.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;FirstName&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;LastName&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Email&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;OwnerId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CreatedDate&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Optional count:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;To limit the count to Contacts that look migration-loaded, add your Contact External ID field (placeholder example: &lt;code&gt;Legacy_Id__c&lt;/code&gt; — replace with your own External ID field; not every org has one):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Contact&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;AccountId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;Legacy_Id__c&lt;/span&gt; &lt;span class="o"&gt;!=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Compare that count with the source system's count of Contacts with no account, so long-standing private Contacts aren't mistaken for load failures.&lt;/p&gt;

&lt;p&gt;Run this in a tool you already have permission to use (Developer Console, VS Code, Workbench, or your approved admin path). No Salesforce org was queried for this article; treat any sample rows below as Example data.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Export or report the same set
&lt;/h2&gt;

&lt;p&gt;If you prefer files, export Contacts with Data Loader including Id, name, Email, OwnerId, CreatedDate and AccountId, then keep rows where AccountId is blank. A standard Contacts report filtered to Account blank can produce the same candidate list for review. Label the export &lt;strong&gt;Example data&lt;/strong&gt; until it is your real org output.&lt;/p&gt;

&lt;p&gt;Keep Contact Id in the file. Prefer matching on Contact Id for any later update. Do not invent Account Ids in the sheet.&lt;/p&gt;

&lt;p&gt;If your export tool turns blank lookups into empty strings, treat those empties as the same candidate set as SOQL null for this review. Confirm column headers before you filter: an Account &lt;em&gt;name&lt;/em&gt; column is not AccountId. When owners differ across rows, include OwnerId so reviewers can see whether a private Contact was expected for that person.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Triage before you attach anything
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data (not real Contacts or Accounts):&lt;/strong&gt;&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;LastName&lt;/th&gt;
&lt;th&gt;Email&lt;/th&gt;
&lt;th&gt;Owner&lt;/th&gt;
&lt;th&gt;CreatedDate&lt;/th&gt;
&lt;th&gt;Decision&lt;/th&gt;
&lt;th&gt;Target Account external key&lt;/th&gt;
&lt;th&gt;Notes&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Example, Renée&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:renee@example.invalid"&gt;renee@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;Admin User&lt;/td&gt;
&lt;td&gt;2026-11-01&lt;/td&gt;
&lt;td&gt;Attach&lt;/td&gt;
&lt;td&gt;AC-001&lt;/td&gt;
&lt;td&gt;Source had parent; map missed&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example, Sam&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:sam@example.invalid"&gt;sam@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;Sales Rep&lt;/td&gt;
&lt;td&gt;2026-10-28&lt;/td&gt;
&lt;td&gt;Leave private&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;Intentional private Contact&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Example, Jo&lt;/td&gt;
&lt;td&gt;&lt;a href="mailto:jo@example.invalid"&gt;jo@example.invalid&lt;/a&gt;&lt;/td&gt;
&lt;td&gt;Admin User&lt;/td&gt;
&lt;td&gt;2026-11-02&lt;/td&gt;
&lt;td&gt;Investigate&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;Email domain unclear&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Optional tally (engine-agnostic arithmetic; not claimed as a Google Sheets test):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF(E2:E4,"Attach")
=COUNTIF(E2:E4,"Leave private")
=COUNTIF(E2:E4,"Investigate")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Blank &lt;code&gt;AccountId&lt;/code&gt; alone does not mean “attach to the first Account that looks similar.” Wrong AccountId creates a wrong-parent problem. Leave intentional private Contacts alone unless the business owner says otherwise.&lt;/p&gt;

&lt;p&gt;Work the Investigate rows before Attach. A missing email, a shared mailbox, or a source note that said “no company” can all look like load defects when they are not. Record who approved Leave private so a later orphan re-query does not reopen settled decisions.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Attach safely when the decision is Attach
&lt;/h2&gt;

&lt;p&gt;Salesforce Help documents a Data Import Wizard path to mass-update Account on existing Contacts, and states that a blank Account leaves the Contact private. Prefer Contact Salesforce Id for matching. Map Account by Salesforce Id or by an External ID your org already uses. Data Loader update on Contact Id with AccountId filled is the same idea: update only reviewed Attach rows.&lt;/p&gt;

&lt;p&gt;Do not auto-attach the whole orphan export. Do not delete orphans to “clean” the count. After an Attach update, re-run the null query and spot-check a few Contact pages. If you attach the wrong Account, you have traded an orphan for a wrong parent — go back to the wrong-parent checks linked above.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Honest limits
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Some Contacts are intentionally private; privacy after a blank Account is documented Salesforce behaviour, not automatically a defect.&lt;/li&gt;
&lt;li&gt;Sharing rules do not surface private Contacts to ordinary users.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;AccountId = null&lt;/code&gt; does not detect a non-null wrong parent.&lt;/li&gt;
&lt;li&gt;Permissions matter: without View All Data (or ownership), you may under-count orphans you cannot see.&lt;/li&gt;
&lt;li&gt;No org execution or fabricated import results are claimed here. Re-check against your firm’s go-live rules and sandbox tests.&lt;/li&gt;
&lt;li&gt;The product line below is limited to wrong-parent CSV compare on shared stable keys. It does not detect blank AccountId orphans.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;An orphan query catches a blank AccountId. It will not catch a Contact that still has an AccountId pointing at the wrong company — that needs a parent-key compare. If you want that wrong-parent check in one browser workbook, &lt;a href="https://ailstudio.gumroad.com/l/ufgfjf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=salesforce-orphan-contacts-accountid-null" rel="noopener noreferrer"&gt;RecordBridge&lt;/a&gt; (US$49, one-time) compares before-and-after Account and Contact CSVs and flags those mismatches. It does not cancel loads or replace your firm's go-live sign-off.&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>data</category>
      <category>soql</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Before a Salesforce CSV load: check dependent picklist combinations by record type</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Sat, 07 Nov 2026 22:49:47 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/before-a-salesforce-csv-load-check-dependent-picklist-combinations-by-record-type-3nbh</link>
      <guid>https://dev.to/richardc_ailstudio/before-a-salesforce-csv-load-check-dependent-picklist-combinations-by-record-type-3nbh</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;By Richard C, AIL Studio&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  1. The combination is the unit of review
&lt;/h2&gt;

&lt;p&gt;A country can be allowed and a region can be allowed, yet the pair can be outside the intended target UI configuration. Checking each column against a global list misses that relationship. Record type matters too: a value available for one type need not be available for another.&lt;/p&gt;

&lt;p&gt;This is a pre-load configuration/business-conformance review, not source-versus-target reconciliation. An import path may accept values that the UI would not offer together. A finding here is a reason to review the mapping, not proof Salesforce will reject a CSV row.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Freeze the intended target configuration
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data and schema throughout:&lt;/strong&gt; &lt;code&gt;Market__c&lt;/code&gt; and &lt;code&gt;Region__c&lt;/code&gt; are illustrative custom Account fields, not built-in Salesforce fields. This tutorial handles one confirmed dependency: a non-blank single-select controlling picklist and a single-select dependent picklist. It does not support multi-select fields, checkbox controllers or multiple dependency levels.&lt;/p&gt;

&lt;p&gt;Identify your real field API names, intended target record types and appropriate read permissions. Confirm the dependency in Setup or Object Info: inspect object &lt;code&gt;dependentFields&lt;/code&gt; and field &lt;code&gt;controllingFields&lt;/code&gt;. &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.uiapi.meta/uiapi/ui_api_features_records_dependent_picklist.htm" rel="noopener noreferrer"&gt;Salesforce's UI API guide&lt;/a&gt; warns that empty &lt;code&gt;controllerValues&lt;/code&gt; does not establish independence: field-level security can hide the controller. Stop and resolve access or configuration uncertainty rather than treating missing metadata as permission.&lt;/p&gt;

&lt;p&gt;Using an already-approved authenticated API client, save the dependent field response for each intended target record type. The relative request is:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;GET /ui-api/object-info/{objectApiName}/picklist-values/{recordTypeId}/{fieldApiName}
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Record the target org/environment, retrieval date, API version used, object, fields, record type and access scope alongside the snapshot. Do not substitute the reader's default record type for every row. Resolve the target type the migration intends for that row.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Keep API values, not translated labels
&lt;/h2&gt;

&lt;p&gt;The &lt;a href="https://developer.salesforce.com/docs/atlas.en-us.uiapi.meta/uiapi/ui_api_responses_picklist_value.htm" rel="noopener noreferrer"&gt;Picklist Value reference&lt;/a&gt; distinguishes API &lt;code&gt;value&lt;/code&gt;, display &lt;code&gt;label&lt;/code&gt; and integer controller indexes in &lt;code&gt;validFor&lt;/code&gt;. The &lt;a href="https://developer.salesforce.com/docs/platform/lwc/guide/reference-wire-adapters-picklist-values.html" rel="noopener noreferrer"&gt;picklist adapter documentation&lt;/a&gt; explains record-type scope and translated labels; &lt;code&gt;value&lt;/code&gt; remains the untranslated API name.&lt;/p&gt;

&lt;p&gt;Keep source-to-target business mappings explicit. Do not silently trim, uppercase or relabel input. Target record-type IDs are org-specific: map logical aliases to real target IDs, not copied source IDs. A saved response covers one field and one record type, not all org values.&lt;/p&gt;

&lt;p&gt;For the offline input, wrap each saved per-field response body under its logical type alias. Do not pass a whole batch response directly. Keep a separate alias-to-target-ID mapping with the snapshot so reviewers can trace which response was selected.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Example data:&lt;/strong&gt; &lt;code&gt;Business&lt;/code&gt; and &lt;code&gt;Partner&lt;/code&gt; below are invented type aliases. These reduced UI API-shaped fragments are not real API responses. Save as &lt;code&gt;metadata.json&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="nl"&gt;"Business"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="nl"&gt;"controllerValues"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"EMEA"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"AMER"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="nl"&gt;"values"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="w"&gt;
      &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"label"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"United Kingdom"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"value"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"UK"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"validFor"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]},&lt;/span&gt;&lt;span class="w"&gt;
      &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"label"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"United States"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"value"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"US"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"validFor"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]}&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="nl"&gt;"Partner"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="nl"&gt;"controllerValues"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"EMEA"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"AMER"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="nl"&gt;"values"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="w"&gt;
      &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="nl"&gt;"label"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"United Kingdom"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"value"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="s2"&gt;"UK"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="nl"&gt;"validFor"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="w"&gt; &lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]}&lt;/span&gt;&lt;span class="w"&gt;
    &lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="w"&gt;
  &lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="w"&gt;
&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="w"&gt;
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  4. Run a local CSV preflight
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data:&lt;/strong&gt; save these six invented rows as &lt;code&gt;input.csv&lt;/code&gt;. &lt;code&gt;row_key&lt;/code&gt; is only an output locator.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;row_key,record_type,Market__c,Region__c
A01,Business,EMEA,UK
A02,Business,EMEA,US
A03,Partner,AMER,US
A04,Business,APAC,UK
A05,Business,EMEA,
A06,Unknown,EMEA,UK
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Save this complete Python 3 standard-library checker as &lt;code&gt;preflight.py&lt;/code&gt;. It has no network or authentication code, needs no pip packages and never writes to Salesforce. Adapt the column constants only after confirming your real schema. It preserves exact input strings.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;csv&lt;/span&gt;
&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;json&lt;/span&gt;
&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;

&lt;span class="n"&gt;COLUMNS&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;row_key&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;record_type&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Market__c&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;Region__c&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;


&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;checked_metadata&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;metadata must be an object&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;controllers&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;controllerValues&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;values&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;values&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;controllerValues missing, empty or inaccessible&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="nf"&gt;any&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;k&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="n"&gt;k&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="nf"&gt;type&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;is&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nb"&gt;int&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
           &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;k&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;()):&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;bad controller index&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;set&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;values&lt;/span&gt;&lt;span class="p"&gt;()))&lt;/span&gt; &lt;span class="o"&gt;!=&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;duplicate controller index&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;values missing&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;lookup&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;value&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
            &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;bad value object&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;api&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;value&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;valid&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;validFor&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;api&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="n"&gt;api&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="n"&gt;api&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;lookup&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;missing or duplicate dependent API value&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;valid&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="nf"&gt;any&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
                &lt;span class="nf"&gt;type&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="ow"&gt;is&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nb"&gt;int&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;values&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;valid&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
            &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;missing or malformed validFor&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;lookup&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;api&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;valid&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;lookup&lt;/span&gt;


&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;main&lt;/span&gt;&lt;span class="p"&gt;():&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;argv&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;!=&lt;/span&gt; &lt;span class="mi"&gt;3&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;usage: python3 preflight.py metadata.json input.csv&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="nf"&gt;open&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;argv&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt; &lt;span class="n"&gt;encoding&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;utf-8-sig&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;stream&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;metadata&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;json&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;load&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;stream&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;isinstance&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;metadata&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
        &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;metadata root must be a type-alias object&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="c1"&gt;# Validate each type once; malformed known types never auto-pass.
&lt;/span&gt;    &lt;span class="n"&gt;prepared&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;item&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;metadata&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;():&lt;/span&gt;
        &lt;span class="k"&gt;try&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;prepared&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;checked_metadata&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="k"&gt;except&lt;/span&gt; &lt;span class="nb"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;prepared&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt;
    &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="nf"&gt;open&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;argv&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt; &lt;span class="n"&gt;newline&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;encoding&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;utf-8-sig&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;stream&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;reader&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;csv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;DictReader&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;stream&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="nf"&gt;if &lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;reader&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;fieldnames&lt;/span&gt; &lt;span class="ow"&gt;is&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt;
                &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;reader&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;fieldnames&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;!=&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;set&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;reader&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;fieldnames&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt;
                &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="nf"&gt;set&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;COLUMNS&lt;/span&gt;&lt;span class="p"&gt;).&lt;/span&gt;&lt;span class="nf"&gt;issubset&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;reader&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;fieldnames&lt;/span&gt;&lt;span class="p"&gt;)):&lt;/span&gt;
            &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;missing or duplicate CSV headers&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;findings&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[]&lt;/span&gt;
        &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;reader&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="nf"&gt;any&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;k&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="ow"&gt;is&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;k&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;COLUMNS&lt;/span&gt;&lt;span class="p"&gt;):&lt;/span&gt;
                &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;malformed CSV row&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="n"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;controller&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;dependent&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;k&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;k&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;COLUMNS&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;:])&lt;/span&gt;
            &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;alias&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;prepared&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;UNKNOWN_RECORD_TYPE&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
            &lt;span class="k"&gt;elif&lt;/span&gt; &lt;span class="n"&gt;prepared&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="ow"&gt;is&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;METADATA_REVIEW&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
            &lt;span class="k"&gt;elif&lt;/span&gt; &lt;span class="n"&gt;controller&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt; &lt;span class="ow"&gt;or&lt;/span&gt; &lt;span class="n"&gt;dependent&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="sh"&gt;""&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;REVIEW_BLANK&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
            &lt;span class="k"&gt;else&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                &lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;prepared&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
                &lt;span class="n"&gt;index&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;controllers&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;controller&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
                &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;index&lt;/span&gt; &lt;span class="ow"&gt;is&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                    &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;UNKNOWN_CONTROLLER&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
                &lt;span class="k"&gt;elif&lt;/span&gt; &lt;span class="n"&gt;dependent&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                    &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;VALUE_NOT_AVAILABLE_FOR_TYPE&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
                &lt;span class="k"&gt;elif&lt;/span&gt; &lt;span class="n"&gt;index&lt;/span&gt; &lt;span class="ow"&gt;not&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;dependent&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
                    &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;COMBINATION_NOT_AVAILABLE&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
                &lt;span class="k"&gt;else&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
                    &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;CONFIG_MATCH&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;
            &lt;span class="n"&gt;findings&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;append&lt;/span&gt;&lt;span class="p"&gt;([&lt;/span&gt;&lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;k&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;k&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;COLUMNS&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;status&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt;
    &lt;span class="n"&gt;writer&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;csv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;writer&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;stdout&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;lineterminator&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="se"&gt;\n&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;writer&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;writerow&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;COLUMNS&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;status&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt;
    &lt;span class="n"&gt;writer&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;writerows&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;findings&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;


&lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;__name__&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;__main__&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;try&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="nf"&gt;main&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
    &lt;span class="nf"&gt;except &lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;OSError&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;ValueError&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;csv&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Error&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;error&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;ERROR: &lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="nf"&gt;str&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;error&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="nb"&gt;file&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;stderr&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;exit&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Run locally:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;python3 preflight.py metadata.json input.csv
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Optionally redirect stdout to a local findings file. An unreadable file, malformed JSON or missing CSV headers produces an explicit stderr error and exit code 2, without findings. A known type with incomplete or malformed metadata produces &lt;code&gt;METADATA_REVIEW&lt;/code&gt;; duplicate dependent API values are rejected rather than overwritten. An empty controller map is also review-only in this confirmed-dependent scope.&lt;/p&gt;

&lt;p&gt;For usable metadata, priority is unknown type, blanks, unknown controller, unavailable dependent value, unavailable combination, then match. Metadata safety checks come before value checks for malformed known types. Controller index zero is valid: the script tests &lt;code&gt;is None&lt;/code&gt;, not truthiness. A well-formed empty &lt;code&gt;validFor&lt;/code&gt; list allows no controller for this dependent-field check.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Read the result, then resolve deliberately
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Example data — expected findings:&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;row_key,record_type,Market__c,Region__c,status
A01,Business,EMEA,UK,CONFIG_MATCH
A02,Business,EMEA,US,COMBINATION_NOT_AVAILABLE
A03,Partner,AMER,US,VALUE_NOT_AVAILABLE_FOR_TYPE
A04,Business,APAC,UK,UNKNOWN_CONTROLLER
A05,Business,EMEA,,REVIEW_BLANK
A06,Unknown,EMEA,UK,UNKNOWN_RECORD_TYPE
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A01 matches the configured pair, including controller index 0. A02 has a known dependent value but the wrong controller index. A03's dependent value is absent for its type. A04 has an unknown controller; A05 needs blank-value review; A06 has no saved type entry. That is exactly &lt;strong&gt;one configuration match and five rows to review&lt;/strong&gt;, not five demonstrated Salesforce rejections.&lt;/p&gt;

&lt;p&gt;The accompanying local fixture tests also replace A01's dependent input with the display label &lt;code&gt;United Kingdom&lt;/code&gt;; it becomes &lt;code&gt;VALUE_NOT_AVAILABLE_FOR_TYPE&lt;/code&gt;, not a match. Missing files, malformed metadata and broken CSV headers are negative cases, not successful preflights.&lt;/p&gt;

&lt;p&gt;Have the business owner compare the source mapping with the intended target configuration. Resolve which is wrong before altering either. Do not blanket-enable values, disable restrictions, change record types, mass-update records or automatically rewrite the CSV. Keep the snapshot and findings together; re-fetch and re-run when target metadata changes.&lt;/p&gt;

&lt;p&gt;Assign each review finding an owner and retain the mapping decision with the load plan. If the intended target type is uncertain, resolve that uncertainty first; testing the row against a convenient different type would answer the wrong question.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. What this does not prove
&lt;/h2&gt;

&lt;p&gt;This snapshot describes UI-available combinations for the reading user's access, not full server validation. Separately review required fields, validation rules, flows/triggers, FLS, import-path behavior, restricted/unrestricted picklist enforcement and historical inactive values. Use approved tooling for separate sandbox tests. Blank values are review cases, not automatically invalid.&lt;/p&gt;

&lt;p&gt;No real org execution was performed here. The executed tests use only the labelled example fixtures. A configuration match is not a migration certification or assurance that an import will succeed.&lt;/p&gt;

&lt;p&gt;For a separate post-load check, I make &lt;a href="https://ailstudio.gumroad.com/l/ufgfjf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=salesforce-picklist-preflight" rel="noopener noreferrer"&gt;RecordBridge&lt;/a&gt;, which compares Account, Contact and Opportunity CSV exports on shared stable keys; it does not validate picklist metadata.&lt;/p&gt;

</description>
      <category>salesforce</category>
      <category>tutorial</category>
      <category>data</category>
      <category>python</category>
    </item>
    <item>
      <title>How to find duplicate records after a Salesforce data migration (before users do)</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Fri, 06 Nov 2026 13:25:22 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/how-to-find-duplicate-records-after-a-salesforce-data-migration-before-users-do-25ej</link>
      <guid>https://dev.to/richardc_ailstudio/how-to-find-duplicate-records-after-a-salesforce-data-migration-before-users-do-25ej</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt; · Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;The load finished, the record counts are close enough, and sign-off is tomorrow. Then a sales rep finds "Northwind Traders" twice, with Contacts split between the two. Here's how to find those duplicates first.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Why matching counts can hide duplicates
&lt;/h2&gt;

&lt;p&gt;A count check only tells you the totals line up. If one record failed and another was loaded twice, the count still matches. Duplicates usually come from:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;a load that was re-run after a partial failure,&lt;/li&gt;
&lt;li&gt;an insert used where an upsert on an External ID was needed,&lt;/li&gt;
&lt;li&gt;keys that differ only by case, spaces or leading zeros (&lt;code&gt;ACC-00412&lt;/code&gt;, &lt;code&gt;acc-412&lt;/code&gt;, &lt;code&gt;ACC-412&lt;/code&gt;), so a Data Loader or API upsert treated them as different records (Salesforce matches External IDs case-sensitively unless the field is set as Unique and case-insensitive).&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Salesforce duplicate and matching rules help, but they're tuned for name, email and address matches on new records, and they don't look at your legacy key at all unless you build a rule for it.&lt;/p&gt;

&lt;p&gt;If your keys are clean and you only need exact matches, my earlier post covers that: &lt;a href="https://dev.to/richardc_ailstudio/how-to-find-missing-and-duplicate-records-after-a-salesforce-migration-match-on-an-external-key-mbf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-duplicate-records-internal"&gt;How to find missing and duplicate records after a Salesforce migration (match on an external key)&lt;/a&gt;. This post is for the messier case, where the same record hides behind a slightly different key.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Normalise the key
&lt;/h2&gt;

&lt;p&gt;Export the Accounts from Salesforce with Id, the legacy key field, Name, CreatedDate and a count of Contacts (a report with a Contacts summary works). Put the key in column B and add a normalised key in F2:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=UPPER(LEFT(TRIM(B2),FIND("-",TRIM(B2))))&amp;amp;VALUE(MID(TRIM(B2),FIND("-",TRIM(B2))+1,20))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This trims spaces, makes the key upper case and drops leading zeros from the number after the dash, so &lt;code&gt;acc-412&lt;/code&gt;, &lt;code&gt;ACC-00412&lt;/code&gt; and &lt;code&gt;ACC-412&lt;/code&gt; all become &lt;code&gt;ACC-412&lt;/code&gt;. It assumes keys shaped like &lt;code&gt;PREFIX-number&lt;/code&gt;; adjust it to your format. Add a normalised name in G2 with &lt;code&gt;=UPPER(TRIM(C2))&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F82k13o7ludeu6zo9t0jc.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F82k13o7ludeu6zo9t0jc.png" alt="Example data: three Account keys that look different but normalise to the same value"&gt;&lt;/a&gt;&lt;br&gt;
&lt;em&gt;Example data: &lt;code&gt;ACC-00412&lt;/code&gt; and &lt;code&gt;acc-412&lt;/code&gt; both normalise to &lt;code&gt;ACC-412&lt;/code&gt;.&lt;/em&gt;&lt;/p&gt;
&lt;h2&gt;
  
  
  3. Group and count by key
&lt;/h2&gt;

&lt;p&gt;In H2, count each normalised key, and in I2 count the raw key exactly:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=COUNTIF($F$2:$F$13,F2)
=SUMPRODUCT(--EXACT($B$2:$B$13,B2))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Change 13 to your last row. A key count above 1 is a duplicate. If the exact count is also above 1, the raw key repeats as-is (usually a re-run). If only the normalised count is above 1, it's a hidden duplicate that an exact check would miss.&lt;/p&gt;

&lt;p&gt;In the org itself, the same check is one SOQL query (swap in your field's API name):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Legacy_Key__c&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Account&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;Legacy_Key__c&lt;/span&gt; &lt;span class="k"&gt;HAVING&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;In most English-locale orgs, SOQL's text comparison here is case-insensitive, so it catches &lt;code&gt;ACC-412&lt;/code&gt; vs &lt;code&gt;acc-412&lt;/code&gt; but not leading zeros or trailing spaces. Use the spreadsheet for those.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Separate true duplicates from legitimate repeats
&lt;/h2&gt;

&lt;p&gt;Not every repeat is a duplicate. In J2:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(H2=1,"Unique",IF(COUNTIFS($F$2:$F$13,F2,$G$2:$G$13,G2)&amp;lt;H2,"Review: key collision",IF(I2&amp;gt;1,"Exact duplicate","Hidden duplicate")))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjqd0hd1sccos2meoxzo2.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjqd0hd1sccos2meoxzo2.png" alt="Example data: rows classified as hidden duplicate, exact duplicate, key collision or unique"&gt;&lt;/a&gt;&lt;br&gt;
&lt;em&gt;Example data: two hidden duplicates, one exact duplicate, one key collision, and two "Granite Works" rows that are unique.&lt;/em&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Exact / Hidden duplicate:&lt;/strong&gt; same normalised key, same name. Safe to merge.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Review: key collision:&lt;/strong&gt; same normalised key, different names ("Delta Freight" vs "Delta Freight Ltd"). It might be one company, or two source records whose keys collided after normalising. Check the source before touching it.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Same name, different keys&lt;/strong&gt; ("Granite Works" twice): these stay Unique. Two branches can share a name. Don't merge on name alone.&lt;/li&gt;
&lt;/ul&gt;
&lt;h2&gt;
  
  
  5. Choose the survivor and repoint children first
&lt;/h2&gt;

&lt;p&gt;Deleting a duplicate Account deletes or orphans what hangs under it, so pick the survivor and move the children before deleting anything. In K2, keep the record with the most Contacts:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(OR(J2="Unique",LEFT(J2,6)="Review"),"",IF(E2=MAXIFS($E$2:$E$13,$F$2:$F$13,F2),"Keep (survivor)","Repoint "&amp;amp;E2&amp;amp;", then delete"))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If two records tie, pick the older CreatedDate by hand. Then repoint Contacts, Opportunities and Cases from each loser to its survivor (Data Loader update on AccountId, or Salesforce's merge for Accounts, which moves related records for you). Only then delete. Fix the key on the survivor so the next upsert finds it.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fqoxfdmdgswp6wdh9z87r.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fqoxfdmdgswp6wdh9z87r.png" alt="Example data: summary of duplicates, records to delete and Contacts to repoint"&gt;&lt;/a&gt;&lt;br&gt;
&lt;em&gt;Example data: a one-screen summary for the sign-off call.&lt;/em&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Pre-go-live checklist
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Run the key count on every object you loaded, parents first.&lt;/li&gt;
&lt;li&gt;Normalise keys the same way in the source and in Salesforce.&lt;/li&gt;
&lt;li&gt;Review every key collision against the source; don't merge them automatically.&lt;/li&gt;
&lt;li&gt;Repoint child records before deleting a duplicate.&lt;/li&gt;
&lt;li&gt;Mark the External ID field Unique so the next load can't insert the same key twice.&lt;/li&gt;
&lt;li&gt;Re-run the count after the cleanup and keep the summary with your sign-off.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Formulas were checked in LibreOffice Calc. The same functions exist in Excel and Google Sheets, but I haven't tested them there, so check them once in your own copy.&lt;/p&gt;

&lt;p&gt;For the exact-key side of this check, RecordBridge is a browser tool that compares before-and-after Account, Contact and Opportunity exports and reports missing and extra records, blank or duplicate keys, and wrong-parent links. It matches keys exactly, so normalise them first: &lt;a href="https://ailstudio.gumroad.com/l/ufgfjf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=salesforce-duplicate-records-after-migration" rel="noopener noreferrer"&gt;RecordBridge&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>data</category>
      <category>tutorial</category>
      <category>beginners</category>
      <category>salesforce</category>
    </item>
    <item>
      <title>Salesforce data migration testing: how to reconcile record counts and field values before go-live</title>
      <dc:creator>Richard C</dc:creator>
      <pubDate>Thu, 05 Nov 2026 13:40:10 +0000</pubDate>
      <link>https://dev.to/richardc_ailstudio/salesforce-data-migration-testing-how-to-reconcile-record-counts-and-field-values-before-go-live-3k0b</link>
      <guid>https://dev.to/richardc_ailstudio/salesforce-data-migration-testing-how-to-reconcile-record-counts-and-field-values-before-go-live-3k0b</guid>
      <description>&lt;p&gt;&lt;strong&gt;AI-Assisted&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By Richard C, AIL Studio&lt;/p&gt;

&lt;p&gt;The data load finished with no errors, so the migration worked, right? Not necessarily. "No errors" means Salesforce accepted what it was sent. It doesn't prove that every source row was sent, or that the values arrived the way they left. Before go-live you want evidence, object by object, that you can hand to whoever signs off.&lt;/p&gt;

&lt;p&gt;This post walks through four checks in a spreadsheet: record counts, field values on an external key, nulls and truncation, and a sign-off table.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Reconcile record counts per object
&lt;/h2&gt;

&lt;p&gt;For each object, get the row count from your source export (or the source system's own query) and the count in Salesforce:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Account&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Account&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;External_Key__c&lt;/span&gt; &lt;span class="o"&gt;!=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The second query is the more useful one. It counts only records that came from the migration, not ones users created by hand in the sandbox.&lt;/p&gt;

&lt;p&gt;Before comparing, subtract the records you left out on purpose (for example inactive users or old cases) from the source count, so you compare against what you meant to load.&lt;/p&gt;

&lt;p&gt;Put the counts side by side. Difference is &lt;code&gt;=C2-B2&lt;/code&gt;, and Status turns it into words:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(D2=0,"Match",IF(D2&amp;lt;0,"Short "&amp;amp;-D2,"Extra "&amp;amp;D2))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fpmklifig343oyyvx9nzg.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fpmklifig343oyyvx9nzg.png" alt="Counts tab: Account short 1, Contact match, Opportunity short 5. Example data." width="800" height="123"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;A match isn't proof that nothing is wrong: one missing record plus one duplicate still matches. Totals (below) catch changed amounts; step 2 then finds which records changed.&lt;/p&gt;

&lt;h3&gt;
  
  
  Compare totals
&lt;/h3&gt;

&lt;p&gt;Counts tell you how many records arrived. Totals tell you whether the money did. For each object that has an amount field, sum it on both sides:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Opportunity: &lt;code&gt;Amount&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Opportunity Product (OpportunityLineItem): &lt;code&gt;TotalPrice&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Account: &lt;code&gt;AnnualRevenue&lt;/code&gt;, if you migrated it
&lt;/li&gt;
&lt;/ul&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;StageName&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Amount&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;Opportunity&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;External_Key__c&lt;/span&gt; &lt;span class="o"&gt;!=&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt; &lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;StageName&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Run the same grouping on the source export (a pivot table works) and put the two totals side by side. Grouping by stage tells you where a difference sits. In a multi-currency org, SOQL returns a grouped &lt;code&gt;SUM(Amount)&lt;/code&gt; in the org's default (corporate) currency. For per-currency totals, export &lt;code&gt;Amount&lt;/code&gt; and &lt;code&gt;CurrencyIsoCode&lt;/code&gt; and sum them in a pivot on both sides. If you compare converted totals instead, both sides must use the same exchange rates.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Pass rule:&lt;/strong&gt; the totals must match exactly, per object and per group. The only allowed difference is one you can trace to rounding, for example a source with four decimals loaded into a field with two. Write that down on the sign-off sheet. Any other difference, even when the counts match, means a record is missing, duplicated or changed. Go to step 2 to find it.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Compare field values on an external key
&lt;/h2&gt;

&lt;p&gt;Export the same fields from both sides into two tabs, Source and Target, each starting with the external key (the ID from the old system that you loaded into an External ID field). Salesforce record IDs are new after the load, so the external key is the only thing you can match on.&lt;/p&gt;

&lt;p&gt;On a Fields tab, pull each target value next to its source row:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFERROR(INDEX(Target!$B$2:$B$100,MATCH($A2,Target!$A$2:$A$100,0)),"(missing)")&amp;amp;""
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The &lt;code&gt;&amp;amp;""&lt;/code&gt; at the end matters. Without it, an empty target cell comes back as 0, and a blank phone number looks like a real value.&lt;/p&gt;

&lt;p&gt;Then compare with EXACT, which is case-sensitive (a plain &lt;code&gt;=&lt;/code&gt; isn't):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(B2="(missing)","Missing",IF(EXACT(Source!B2,B2),"OK","Changed"))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For a large object, don't compare every row by hand. Take a sample, say 100 records across different record types and owners, and widen it if anything turns up.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Catch nulls and truncation
&lt;/h2&gt;

&lt;p&gt;Two failures hide inside "Changed", and they're worth naming because they have different fixes.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Null in target:&lt;/strong&gt; the source had a value and the target is blank. It's usually an unmapped field or a picklist value that was rejected.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(B2="(missing)","Missing",IF(AND(Source!C2&amp;lt;&amp;gt;"",D2=""),"Null in target",IF(EXACT(Source!C2,D2),"OK","Changed")))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Truncated:&lt;/strong&gt; the target is the start of the source value, cut short. The field length in Salesforce is shorter than the source column.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(B2="(missing)","Missing",IF(EXACT(Source!D2,F2),"OK",IF(AND(LEN(F2)&amp;lt;LEN(Source!D2),LEFT(Source!D2,LEN(F2))=F2),"Truncated","Changed")))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2pyx6yp5bc8gz8pf78ti.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2pyx6yp5bc8gz8pf78ti.png" alt="Fields tab: one truncated description, one changed name (an encoded ampersand), one null phone, one missing account. Example data." width="800" height="150"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;In the example, &lt;code&gt;Cedar &amp;amp; Pine Co&lt;/code&gt; arrived as &lt;code&gt;Cedar &amp;amp;amp; Pine Co&lt;/code&gt;. That's an encoding problem in the load file, and it's exactly what EXACT catches.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Sign off per object
&lt;/h2&gt;

&lt;p&gt;Finally, one row per object, counted from the checks above:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Missing: &lt;code&gt;=COUNTIF(Fields!C2:C9,"Missing")&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Changed: &lt;code&gt;=COUNTIF(Fields!C2:G9,"Changed")&lt;/code&gt; (do the same for "Null in target" and "Truncated")&lt;/li&gt;
&lt;li&gt;Ready?: &lt;code&gt;=IF(B2&amp;lt;&amp;gt;"Match","Not ready",IF(C2="n/a","Counts only",IF(C2+D2+E2+F2=0,"Ready","Not ready")))&lt;/code&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ft0vmoej4ec94qvv3mdht.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ft0vmoej4ec94qvv3mdht.png" alt="Sign-off tab: Account not ready, Contact counts only, Opportunity not ready, with a signed-off-by line. Example data." width="800" height="160"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;"Counts only" is an honest status. The formula gives it when the totals match but the value checks are marked n/a because nobody has compared values yet. Don't let it get rounded up to "Ready".&lt;/p&gt;

&lt;h2&gt;
  
  
  Next checks
&lt;/h2&gt;

&lt;p&gt;Counts and field values are the first pass. For row-level problems, see &lt;a href="https://dev.to/richardc_ailstudio/how-to-find-missing-and-duplicate-records-after-a-salesforce-migration-match-on-an-external-key-mbf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-migration-reconcile-internal"&gt;how to find missing and duplicate records on an external key&lt;/a&gt; and &lt;a href="https://dev.to/richardc_ailstudio/how-to-catch-wrong-parent-contacts-after-a-salesforce-migration-o65?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-migration-reconcile-internal"&gt;how to catch Contacts attached to the wrong Account&lt;/a&gt;. For key collisions and choosing a survivor before you delete, see &lt;a href="https://dev.to/richardc_ailstudio/how-to-find-duplicate-records-after-a-salesforce-data-migration-before-users-do-25ej?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=sf-reconcile-next-duplicates"&gt;how to find duplicate records after a Salesforce data migration (before users do)&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;These formulas were checked in LibreOffice Calc. INDEX/MATCH, EXACT and COUNTIF exist in Excel and Google Sheets, but I haven't tested them there, so check them once in your own copy.&lt;/p&gt;

&lt;p&gt;For the wrong-parent check, RecordBridge is a browser tool that compares your Account, Contact and Opportunity exports: &lt;a href="https://ailstudio.gumroad.com/l/ufgfjf?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=salesforce-data-migration-testing-reconcile" rel="noopener noreferrer"&gt;RecordBridge&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>data</category>
      <category>tutorial</category>
      <category>salesforce</category>
      <category>beginners</category>
    </item>
  </channel>
</rss>
