.png){kind=logo}
Assistance Needed with Formula Generator Tool Issue
I'm having an issue with the formula generator tool and would appreciate your assistance.
I'm having an issue with the formula generator tool and would appreciate your assistance.
Here's how to use the Formula Generator tool: 1. Add a new column and select "Formula" 2. In the AI Formula Generator box: • Type your desired formula or operation • Use "/" to reference specific columns you want to include • Click "Generate Formula" 3. Review the sample output that appearsIf you're not getting the expected results, you can: ** Try rewording your prompt ** Adjust the formula wording ** Modify the generated code directly ** Use the preview feature to verify the results before applyingFor conditional formulas, make sure to clearly state your condition after typing "/" to reference the relevant column (e.g., "Only run if /headcount is greater than 40"). Sources: Formulas Conditional Formulas AI Formulas + Conditional Runs [Automated Inbound] - Clay University
Open jobs table - https://app.clay.com/workspaces/331716/workbooks/wb_fRKamRWguPoB/tables/t_CkqzTmKceaDz/views/gv_7AXtwPbJmg6s Candidates table https://app.clay.com/workspaces/331716/workbooks/wb_fRKamRWguPoB/tables/t_xPJxYJjXWSRJ/views/gv_gqn6ireiF5uJ My candidate array is coming in from the "all candidate lookup" I have this working for levels i am matching on but I can't get it to work for the function fields i am trying to match I'm having an issue with the formula generator tool and would appreciate your assistance. Here are the details: Data Structure: - I have an array of candidates that I'm pulling from a "Candidate" table - I'm then trying to write a formula in the "Open Jobs" table to match each candidate to the open job - Each candidate record has the following key/property names: - "Candidate Function Key" - the function or role for the candidate - "LinkedIn Profile" - the unique identifier for the candidate - The "Open Job Job function for match" value is a separate field in the "Open Jobs" table, not part of the candidate array Expected Matching Logic: 1. Loop through each candidate in the array from the "Candidate" table 2. For each candidate: a. Get the "Candidate Function Key" value b. Convert the "Candidate Function Key" to uppercase to normalize capitalization c. Compare the uppercase "Candidate Function Key" to the uppercase "Open Job Job function for match" d. If they match exactly, return an array with: i. "Function Match" ii. The candidate's "LinkedIn Profile" iii. A score of 1.0 to indicate a full match e. If they don't match exactly, return an array with: i. "Function Match" ii. The candidate's "LinkedIn Profile" iii. A score of 0.0 to indicate no match Issue: I have a candidate with "General Accounting" as the "Candidate Function Key", and the "Open Job Job function for match" is also "General Accounting". However, when I run the formula in the "Open Jobs" table, it's returning a score of 0.0 instead of the expected 1.0. I've tried modifying the formula to convert both values to uppercase before comparing, but I'm still seeing the same issue.
Looks like you're still looking for help. We're escalating this over to our support team and someone should be in touch soon. In the meantime, feel free to include any other information that may be helpful in solving your issue!
Hey there Leti thanks for reaching out, taking a look at your table I do see that there is a column that was removed in the Formula generator, is this value supposed to be the "Open Job States" column? https://downloads.intercomcdn.com/i/o/w28k1kwz/1303497074/4d8de8e48b7ae56fa9ad2553489b/image.png?expires=1734558300&signature=99e37eac8db00165579f0a36bfac90c4e3bc80d4522e4535d62d8ff40ef0cdba&req=dSMnFc13moFYXfMW1HO4zW9tv2g%2BsMWaAzCjMWb7Rs6dkGJ%2F%2FzpEL%2Fr77BMv%0AtUk3%0A
hi LuisArturo I'm a new clay builder so my tables are not very clean Originally I was trying to do all my matching in one column and formula but now have decided to approach each category separately. I've hidden columns to make this easier to understand. The column level match function is working properly this is matching my candidate array level keys to the open job level for match. I want to replicate this for the next category which is job function I have tried to build the formula multiple times so i have these 3 columns highlighted in yellow which are attempting to do it
this column is attempting to match and score with a full match being 1.0 and a partial match being .5 But then in testing it is not working for all use cases so I then built this formula which is just trying to find full matches and score them 1 and for no match 0 it is working for some of the candidates in my array but not all
Hey there Leti, the formula generator that we use for formula columns can be easily confused when given particular long prompts. Have you tried using an AI column, besides having a better processing capability, it also has a meta-prompter which can be used to be engineer a prompt for the column to use. Simply give a description that you wish the column to perform and it will help write a prompt for you. https://downloads.intercomcdn.com/i/o/w28k1kwz/1303610295/17bd7ebfc7e6dfd49bd0991dca20/image.png?expires=1734566400&signature=046b96b614cbd0ba47f670142bcce85a1749b20725e67294eaa2c3942fa0d528&req=dSMnFc9%2FnYNWXPMW1HO4zXnI1lIiiQI3e9eDE8Y%2FkM%2Bqi6YkzVBNFns26Y4I%0AE7UL%0A
okay yes i think i will try using AI column and see if that works!