I want to extract an employee range value and map it to a target range to enrich records in Salesforce. How can I do this in the cleanest way in Clay?
Use Clay's Extract Values from Data integration to pull employee range values from your data sources. Then use the Formula Tool to map these ranges to your target format - Clay's AI formula generator makes it easy to transform data points. Finally, push the mapped values to Salesforce using the Update Record or Create Record actions, ensuring field types match between Clay and Salesforce to avoid errors.
The AI formula generator is failing to write a script for this
When the AI formula generator fails, you can iterate on your prompts or directly modify the generated code. Try adjusting your wording - be more specific about your employee range mapping (e.g., "Map '1-10 employees' to 'Small', '11-50' to 'Medium', '51-200' to 'Large'"). Use the preview feature to test your formula before applying it. In the Formula Tool, type "/" to reference your employee range column, then describe exactly how you want to transform the values. If you need further help troubleshooting the specific formula, request human assistance by clicking on "Talk to a person."
Tommy C. 45 sec on how I solved this for my use cases
For mapping employee range values to target ranges in Clay, use the Formula Tool with specific mapping logic. Create a formula that transforms your source ranges (like "1-10 employees") to your target format (like "Small"). Reference your employee range column using "/" in the Formula Tool, then write conditional logic to map each range category. Once mapped, push the transformed values to Salesforce using Create Record or Update Record actions, ensuring field types match between Clay and Salesforce. What specific employee range format are you starting with and what target ranges do you need to map to?
get {{employee range}} and convert ranges into one number: 1, say 1 2-10, say 10 11-50, say 50 51-200, say 200 201-500, say 500 501-1,000, say 1000 1,001-5,000, say 5000 5,001-10,000, say 10000 Set this as A. Look at {{employee count}}. Set this as B. Print the higher number.
Use the Formula Tool with AI generator. Reference your columns with "/" and provide this prompt: "Convert /employee_range to numbers: '1' → 1, '2-10' → 10, '11-50' → 50, '51-200' → 200, '201-500' → 500, '501-1,000' → 1000, '1,001-5,000' → 5000, '5,001-10,000' → 10000. Compare this converted value with /employee_count and return the higher number." The AI will generate conditional logic to map your ranges and use comparison operators to return the maximum value. Use the preview feature to test before applying the formula.
this gives you the higher number of employee range or employee count. If you don’t need that, just use the formula without the A and B thing
Use the Formula Tool with AI generator to map employee ranges to numbers. Reference your columns with "/" and provide this prompt: "Convert /employee_range to numbers: '1' → 1, '2-10' → 10, '11-50' → 50, '51-200' → 200, '201-500' → 500, '501-1,000' → 1000, '1,001-5,000' → 5000, '5,001-10,000' → 10000. Compare this converted value with /employee_count and return the higher number." The AI will generate conditional logic to map your ranges and use comparison operators to return the maximum value. Use the preview feature to test before applying. Once mapped, push the transformed values to Salesforce using Update Record or Create Record actions, ensuring field types match between Clay and Salesforce to avoid errors. If you only need the mapping without comparison, omit the employee count comparison from your prompt.