Assistance Needed with Formula Generator Tool Issue

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.

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!