Field mapping when the two apps disagree

<h2>Two apps, one value, three opinions</h2><p>Drag a line between two fields and the hard part starts. A payments provider reports forty nine dollars as the integer 4900. A database wants a number with two decimal places. A chat message wants a string that reads like money. A CRM wants a currency object with a code attached. Same value, four representations, and every one of them is correct inside its own app.</p><p>The mapper sits in the middle of that. This is what it does, and more usefully, what it refuses to do.</p><h2>The conversion table</h2><table><thead><tr><th>Source</th><th>Destination</th><th>What we do</th><th>What can still go wrong</th></tr></thead><tbody><tr><td>Integer minor units</td><td>Decimal number</td><td>Divide by the currency exponent, never a hardcoded 100</td><td>Zero decimal currencies if you wrote your own division</td></tr><tr><td>Text</td><td>Single select</td><td>Exact match first, then case insensitive, then stop</td><td>An option renamed in the destination app</td></tr><tr><td>Full name</td><td>First and last name</td><td>Split on the last space, keep the remainder as the surname</td><td>Two word surnames and names with a single token</td></tr><tr><td>Local datetime</td><td>UTC datetime</td><td>Apply the connection timezone, store UTC, display local</td><td>A source that sends no offset and changes it in March</td></tr><tr><td>Rich text</td><td>Plain text</td><td>Strip tags, keep line breaks, convert lists to hyphens</td><td>Tables, which flatten into unreadable runs of text</td></tr><tr><td>File</td><td>Attachment</td><td>Stream through, or pass a link past the size limit</td><td>Links that expire before the destination fetches them</td></tr></tbody></table><h2>Money, in painful detail</h2><p>A charge of forty nine dollars arrives as an integer with a currency code beside it. Here is the same value landing in four places:</p><pre><code>source: amount = 4900, currency = usd Airtable: Amount (number, 2dp) -> 49.00 Notion: Amount (number) -> 49 Slack: message text -> $49.00 HubSpot: Deal amount (currency) -> 49.00 USD </code></pre><p>The exponent comes from the currency code, never from an assumption. Japanese yen has no minor unit, so 4900 yen is 4900 yen, and a flow that divides it by a hundred quietly loses ninety nine percent of your revenue reporting. Currencies with three decimal places exist too. The run history prints both the raw integer and the converted value so a finance person can check the arithmetic without asking anybody.</p><h2>Dates and the timezone trap</h2><p>Everything is stored in UTC. Everything is displayed in the timezone on your user profile. Between those two sentences sits every date bug we have ever shipped.</p><p>The trouble is a source that sends a date with no offset. A row created at 23:40 on the last day of the month, read by an account in a zone nine hours ahead, belongs to a different month depending on which rule you apply, and a monthly report can be wrong by one row forever. When a source gives us no offset we use the timezone on the connection, we say so in the run history, and we do not guess a second time.</p><p>Date only fields get their own treatment. A due date of the fourteenth is the fourteenth in every timezone, so we deliberately do not convert it. Running a plain date through a timezone conversion is the single most common way to move a deadline by a day.</p><h2>Select options that move under you</h2><p>Somebody renames In progress to In Progress on a Tuesday afternoon and eleven flows have an opinion about it. Three behaviours are possible and we picked the third.</p><ol><li>Write the text anyway, creating a new option. Cheap, and within a month the field has four spellings of the same state.</li><li>Write nothing and continue. Cheap, and the record is silently incomplete.</li><li>Fail that record with a named reason, keep the rest of the batch running, and show the exact value that had no home.</li></ol><p>The third costs you an error in the history and saves a data cleanup. You can override it per field when you genuinely want new options created, which is common for tags and rare for status.</p><h2>People are the hardest field</h2><p>An assignee is a person in one app and a string in another. Matching by email works when both sides have one, which is less often than you would expect. Matching by display name works until two people share a first name.</p><p>Our order is email, then exact username, then display name, then stop. We never fall back to the first partial match, because assigning a card to the wrong colleague is worse than assigning it to nobody. When no match exists the record is created unassigned with a note, since an unassigned card in the right list is a recoverable problem.</p><blockquote><p>The mapper telling me it could not find a person saved us a genuinely awkward week. Two Sams. One of them does not work on that account.</p><p>Yuki Sandoval, revenue operations at Northbank Collective</p></blockquote><h2>Defaults instead of guesses</h2><p>Any destination field with no source value can take a default you write once. Use it. A default of Inbound web on a lead source column is a decision you made deliberately, whereas an empty column is a decision you will make repeatedly and differently for the next six months.</p><ul><li>Set defaults for required fields before your first run, since required fields are how a batch fails at record forty.</li><li>Leave optional fields empty rather than filling them with placeholder text, which is unfilterable later.</li><li>Store the source record identifier in one destination field. It costs one column and makes every future update find its target.</li></ul>