Field mappings are at the heart of any data integration. They control how data moves from one system to another and what happens to it along the way. Rapidi gives you a set of built-in formulas that let you convert, filter, look up, and transform your data without writing custom code.
This guide covers best practices for both platforms and shows you, step by step, how to set up your field mappings in MyRapidi.
In the 5th Session of our Open Office Hours series, we will explain how to set up Field Mappings via Rapidi.
How to Set Up Field Mappings
Quick Summary:
In this Open Office Hours session, we walk through how to use formulas in Rapidi's field mappings for situations where a simple 1-to-1 mapping isn't enough.
Topics covered include:
- Conversions – Aligning data formats between source and destination systems (e.g., date to date-time)
- Conditional logic – Using the CASE formula to map values that differ between systems, with default fallbacks
- Lookups – Pulling data from other tables or views using destination (DDS), source (SourceDS), and link storage (LS) lookups
- Text manipulation – Trimming field lengths with the COPY formula to prevent destination errors
- Blank value handling – Skipping or replacing empty fields so transfers run without issues
- Logging – Using the MESSAGE formula to record field values in transfer logs for easier troubleshooting
We also share practical tips: start with simple formulas, test them, then add complexity through nesting as needed. The full formula reference is available on the Rapidi wiki.
Watch the Replay of our fifth session: Field Mappings
Field Mappings in RAPIDI: Formulas, Lookups, and Best Practices
Field mappings can be simple 1-to-1 relationships between a source field and a destination field. In many integrations, that approach is not enough because data may need to be pulled from different places, reformatted, validated, shortened, or mapped conditionally. RAPID formulas (also called expressions) cover these cases.
Where formulas are added in RAPID
Formulas are added in Field List Mappings.
Required syntax rules
- Every formula starts with two hashes:
##
- Text strings use single quotes:
'text'
- Field names use double quotes:
"FieldName"
This structure applies to formulas and to hardcoded values.
Formula categories used for field mappings
RAPID formulas are grouped into categories that match common mapping needs.
1) Conversions (format alignment)
Conversions handle cases where two systems store the same data in different formats. The goal is to align formats so the destination receives the expected structure.
A common example is converting a date from the source into a date-and-time format required by the destination.
2) Conditional logic and defaults
Conditional logic is used when:
- source data is missing or blank
- a field must be mapped differently depending on its value
- a default must be sent when no match exists
CASE formula (value mapping with a default)
A common scenario is mapping values that do not match between systems.
Example pattern:
- Source field: department code with values like
east, west, north
- Destination expects numeric values:
east → 1west → 2north → 3
- A default value is required when none of the expected values are found
The default is placed at the end to ensure the transfer runs correctly.
Blank value handling (fallback actions)
A blank value formula is often paired with lookups:
- If a lookup result is empty, a second action is taken
- Common fallback actions include:
- sending a default value
- skipping the field using a skip-field formula
Example behavior:
- If a language code field is blank, the mapping skips the field
- Otherwise, the existing value is sent
3) Lookups (querying other tables, views, or link storage)
Lookups retrieve data from other tables, lists, or views and send it to the destination.
Lookup types
- DB lookups: run a query on the source or destination
- LS lookup: queries link storage
Key requirement: a shared identifier
A lookup requires something in common between:
- the table being mapped
- the table being queried
Example structure (destination lookup):
- Query destination table/object
- Match a field such as External ID to a field in the transfer table such as Owner ID
- If they match, return a value such as Code and send it to the destination field
Destination vs. source vs. link storage
DDS is used for lookups against the destinationSourceDS is used for lookups against the sourceLS lookup is used for link storage values, which must be kept up to date to be reliable
Lookup examples used in mappings
- Owner assignment: lookup a destination user by alias, confirm the user is active, then return the user ID; if blank, send a default user ID or skip
- Reference fields: lookup a destination object (such as a location) to retrieve the destination system ID required by a reference field
- ERP enrichment: retrieve related values (such as outstanding invoices) from another ERP page/table using a common identifier (such as customer number), then send the value to a destination field; updates in the source table propagate to the mapped destination field on the next run
4) Text manipulation (length control, splitting, concatenation)
Text manipulation formulas adjust strings to match destination constraints.
COPY formula (truncate to a maximum length)
A common use is preventing destination errors caused by long strings.
Example:
- Source field
name allows up to 50 characters
- Destination field allows only 6 characters
- COPY grabs only the first 6 characters so the destination does not reject the record
Hardcoding values in mappings
Hardcoded values follow the same syntax rules:
- Start with
##
- Put the value in single quotes
Hardcoding is used to always send consistent values or to provide defaults.
Operational formulas for troubleshooting and control
Message formula (log record-level context)
The message formula logs selected field values during transfer runs.
Example behavior:
- A customer number field is included in the message formula
- Each transferred record writes its customer number into the logs
- Logs then show which record failed when errors occur, improving troubleshooting when systems provide limited error detail
Error formula (hard stop)
The error formula stops a transfer from running. It is used when a strict validation is required, and missing or invalid data must block processing.
Validation formulas
Validation formulas can check field formats before sending data:
- Email validation formula checks whether an email value is valid
- Phone validation formula checks whether a phone number matches the expected format
Best practices for using formulas in field mappings
- Start with simple formulas and minimal conditions
- Test behavior, then add more conditions and nesting as needed
- Use defaults when mappings depend on value matching
- Use blank value handling with lookups to control what happens when results are empty
- Ensure lookups have a reliable shared identifier between the mapped table and the queried table
- Use message logging to capture record identifiers in logs for faster troubleshooting
- Use the error formula only when a transfer must stop on validation failure
- Follow syntax consistently:
## prefix, single quotes for strings, double quotes for field names
- Nest formulas when multiple steps are required (lookup + blank handling + default/skip)
Summary
RAPID field mappings support more than 1-to-1 field transfers. Formulas enable format conversions, conditional mapping with defaults, lookups across source/destination/link storage, text length control, validation, logging, and hard-stop error handling. Correct syntax and incremental build-up of logic reduce mapping errors and improve transfer reliability.
What's Next
Session covers timestamps and RTI, formulas, and transformations. Learn how to configure how your data moves between systems.
Can't attend live? Register anyway, and we'll send you the recording and materials afterward.
Frequently Asked Questions
What is field mapping in data integration?
Field mapping is the process of connecting a field in one system to a corresponding field in another. It tells the integration which data goes where. A simple example: the "Customer Name" field in your ERP needs to map to the "Account Name" field in Salesforce. Without correct mapping, data ends up in the wrong place or doesn't transfer at all.
What's the difference between a 1-to-1 mapping and a formula-based mapping?
A 1-to-1 mapping sends a value directly from a source field to a destination field without any changes. A formula-based mapping transforms the data along the way. You'd use a formula when the two systems store data in different formats, use different values for the same thing, or when you need to pull data from a related table that isn't part of the main transfer.
What happens when a field in my source system is empty?
If you don't account for blank fields, your transfer may either fail or push empty values into the destination, which can overwrite existing data. In Rapidi, you can use a Blank Value formula combined with Skip Field to tell the system: "If this field is empty, leave the destination field as it is." This is especially useful for fields that aren't always filled in by end users.
Why do I get errors about field length or data type mismatches?
Systems often define the same kind of data differently. A name field might allow 50 characters in your ERP, but only 30 in Salesforce. A date field might use one format (DD/MM/YYYY) in one system and another (YYYY-MM-DD) in the other. These mismatches cause validation errors on the receiving end. Rapidi's COPY and FORMAT formulas let you trim field lengths and convert formats before data reaches the destination.
When should I use a lookup instead of a direct mapping?
Use a lookup when the data you need isn't available on the table you're transferring. For example, if you're syncing customer records but need to include the outstanding invoice amount, that value might live in a separate statistics table. A lookup lets you query another table, match on a shared identifier (such as a customer number), and pull the value into your transfer. In Rapidi, you can run lookups on the source system, the destination system, or the link storage.
