ZohoRecruit: Create Linking Table for Candidates and Associated Job Openings

: Joel Lipman; Zoho; 08 February 2023; Hits: 2637

What?
This article is just in case it comes up again (has twice now) where a client wants a report on candidates and associated job openings in Zoho Recruit.

Why?
We're creating a custom module where our client wants to query Zoho Recruit via the API but couldn't find a database table to determine who has been associated to what job. In Zoho Recruit reports (Analytics), you can include the "Associate" module which then allows you to link the Candidates to the Job Openings; but my client needed to be able to query this via API and the "Associate" table or whatever it is, was not visible in API...

How?
So we created a custom module called "Candidates x JobOpenings" (with alternative API name as "CustomModule6"). Just to add to the complexity of the task, the client has their own reference for Candidates which they called "Candidate Ref" and their own reference for the JobOpening called "Vacancy Ref". The below outlines the custom module we created and how we populated with associated candidates:

The CustomModule6:

Candidate: Lookup to the Candidates module
Job Opening: Lookup to the Job Opening module (called "Vacancies" in client's system)
Candidate Ref: A single line text field
Vacancy Ref: Another single line text field
Candidate x JobOpening Name: Mandatory name field that we will use so as to create unique records

The connection
Within any syntax editor of ZohoRecruit (where you type the deluge code), there will be a "Connections" in the top grey bar that you should click on and create a connection with the scopes that you want (I select nearly all of them), and I'm going to call this one "jl_recruit".

The Deluge code for workflow

copy raw

/* *******************************************************************************
Function:       void fn_AB_AssociateVacancy2Candidate(int p_CandidateID) 
Workflow:       AB001: Associate Job Openings with Candidate
Trigger:        Workflow run whenever a candidate record is modified
Purpose:		Associates Job Openings with a Candidate when their record is modified.

Date Created:   2023-02-02 (JoelLipman.com - Joel Lipman)
                - Initial release
Date Modified:	????-??-?? (??? - ???)
                - 
******************************************************************************* */

v_CandidateZohoID = ifnull(p_CandidateID,0);
r_Associated = invokeurl
[
	url :"https://recruit.zoho.com/recruit/v2/Candidates/" + v_CandidateZohoID + "/associate"
	type :GET
	connection:"my_connection"
];
//info r_Associated;
if(!isnull(r_Associated.get("data")))
{
	for each  r_Data in r_Associated.get("data")
	{
		if(!isnull(r_Data.get("id")))
		{
			v_SearchEndpoint = "https://recruit.zoho.com/recruit/v2/Candidates_x_JobOpenings/search?criteria=(CustomModule6_Name:equals:" + v_CandidateZohoID + r_Data.get("id") + ")";
			l_CheckExists = invokeurl
			[
				url :v_SearchEndpoint
				type :GET
				connection:"my_connection"
			];
			v_CountExists = 0;
			if(!isnull(l_CheckExists.get("data")))
			{
				for each  r_Check in l_CheckExists.get("data")
				{
					if(!isnull(r_Check.get("id")))
					{
						v_CountExists = v_CountExists + 1;
						break;
					}
				}
			}
			if(v_CountExists == 0)
			{
				// 
				// prevent duplicates by updating record with existing name if exists
				v_Name = v_CandidateZohoID + "" + r_Data.get("id");
				// 
				// retrieve client's own candidate ref from candidate record
				r_CandidateDetails = zoho.recruit.getRecordById("Candidates",v_CandidateZohoID);
				v_CandidateRef = if(!isnull(r_CandidateDetails.get("Candidate Ref")),r_CandidateDetails.get("Candidate Ref"),"");
				// 
				// start building addrecord request
				m_Create = Map();
				// 
				// specify unique name to ensure we don't duplicate these
				m_Create.put("Candidate x JobOpening Name",v_Name);
				// 
				// specify the Zoho Recruit Candidate ID
				m_Create.put("Candidate_ID",v_CandidateZohoID.toLong());
				// 
				// specify the client's own Candidate Ref
				m_Create.put("Candidate Ref",v_CandidateRef);
				// 
				// specify the Zoho Recruit Job Opening ID
				m_Create.put("Job Opening_ID",r_Data.get("id"));
				// 
				// specify the client's own Job Opening Ref
				m_Create.put("Vacancy Ref",r_Data.get("Job_Opening_ID"));
				// 
				// update the record (note the API name being used here)
				r_Create = zoho.recruit.addRecord("CustomModule6",m_Create,0,false,"my_connection");
				//info r_Create;
			}
		}
	}
}

/* *******************************************************************************
Function: void fn_AB_AssociateVacancy2Candidate(int p_CandidateID)
Workflow: AB001: Associate Job Openings with Candidate
Trigger: Workflow run whenever a candidate record is modified
Purpose: Associates Job Openings with a Candidate when their record is modified.
Date Created: 2023-02-02 (JoelLipman.com - Joel Lipman)
- Initial release
Date Modified: ????-??-?? (??? - ???)
-
******************************************************************************* */
v_CandidateZohoID = ifnull(p_CandidateID,0);
r_Associated = invokeUrl
[
url :"https://recruit.zoho.com/recruit/v2/Candidates/" + v_CandidateZohoID + "/associate"
type :GET
connection:"my_connection"
];
//info r_Associated;
if(!isnull(r_Associated.get("data")))
{
for each r_Data in r_Associated.get("data")
{
if(!isnull(r_Data.get("id")))
{
v_SearchEndpoint = "https://recruit.zoho.com/recruit/v2/Candidates_x_JobOpenings/search?criteria=(CustomModule6_Name:equals:" + v_CandidateZohoID + r_Data.get("id") + ")";
l_CheckExists = invokeUrl
[
url :v_SearchEndpoint
type :GET
connection:"my_connection"
];
v_CountExists = 0;
if(!isnull(l_CheckExists.get("data")))
{
for each r_Check in l_CheckExists.get("data")
{
if(!isnull(r_Check.get("id")))
{
v_CountExists = v_CountExists + 1;
break;
}
}
}
if(v_CountExists == 0)
{
//
// prevent duplicates by updating record with existing name if exists
v_Name = v_CandidateZohoID + "" + r_Data.get("id");
//
// retrieve client's own candidate ref from candidate record
r_CandidateDetails = zoho.recruit.getRecordById("Candidates",v_CandidateZohoID);
v_CandidateRef = if(!isnull(r_CandidateDetails.get("Candidate Ref")),r_CandidateDetails.get("Candidate Ref"),"");
//
// start building addrecord request
m_Create = Map();
//
// specify unique name to ensure we don't duplicate these
m_Create.put("Candidate x JobOpening Name",v_Name);
//
// specify the Zoho Recruit Candidate ID
m_Create.put("Candidate_ID",v_CandidateZohoID.toLong());
//
// specify the client's own Candidate Ref
m_Create.put("Candidate Ref",v_CandidateRef);
//
// specify the Zoho Recruit Job Opening ID
m_Create.put("Job Opening_ID",r_Data.get("id"));
//
// specify the client's own Job Opening Ref
m_Create.put("Vacancy Ref",r_Data.get("Job_Opening_ID"));
//
// update the record (note the API name being used here)
r_Create = zoho.recruit.addRecord("CustomModule6",m_Create,0,false,"my_connection");
//info r_Create;
}
}
}
}

For historical records
The below snippet is what I used to bring all the past records into the custom module. The client would be able to determine what jobs have started/expired based on their Candidate reference and Vacancy reference:

copy raw

/* *******************************************************************************
Function:       void fn_AB_GenerateVacanciesCandidatesAssociations()
Workflow:       -
Trigger:        Standalone function triggered on execute
Purpose:		Loops through all candidates and creates records referring to the Candidate Ref and Job Opening

Date Created:   2023-02-02 (JoelLipman.com - Joel Lipman)
                - Initial release
Date Modified:	????-??-?? (??? - ???)
                - 
******************************************************************************* */
//
// list of pages to loop through (note: issues with multiple pages, do 1 at a time)
l_Pages = {1};
v_PerPage = 200;
for each  v_Page in l_Pages
{
	v_CountTotal = 0;
	v_CountProcessed = 0;
	v_FromIndex = (v_Page - 1) * v_PerPage;
	v_ToIndex = v_FromIndex + v_PerPage - 1;
	//
	// note that ordering here seemed to do nothing... it's in an order, not sure what order.  Let's loop through every page anyway.
	l_Candidates = zoho.recruit.getRecords("Candidates",v_FromIndex,v_ToIndex,"Modified_Time","CANDIDATEID","asc","my_connection");
	//info l_Candidates;
	//
	// sanity check to output that it did something
	info "Done: " + zoho.currenttime.toString("HH:mm:ss");
	//
	// loop through our list for this page
	for each  r_Candidate in l_Candidates
	{
		v_CountTotal = v_CountTotal + 1;
		v_CandidateZohoID = ifnull(r_Candidate.get("CANDIDATEID"),0);
		r_Associated = invokeurl
		[
			url :"https://recruit.zoho.com/recruit/v2/Candidates/" + v_CandidateZohoID + "/associate"
			type :GET
			connection:"my_connection"
		];
		//info r_Associated;
		if(!isnull(r_Associated.get("data")))
		{
			for each  r_Data in r_Associated.get("data")
			{
				if(!isnull(r_Data.get("id")))
				{
					// 
					// search by record name to see if we've already added this record before
					// note the endpoint and then the field comparison here
					v_SearchEndpoint = "https://recruit.zoho.com/recruit/v2/Candidates_x_JobOpenings/search?criteria=(CustomModule6_Name:equals:" + v_CandidateZohoID + r_Data.get("id") + ")";
					l_CheckExists = invokeurl
					[
						url :v_SearchEndpoint
						type :GET
						connection:"my_connection"
					];
					v_CountExists = 0;
					if(!isnull(l_CheckExists.get("data")))
					{
						for each  r_Check in l_CheckExists.get("data")
						{
							if(!isnull(r_Check.get("id")))
							{
								v_CountExists = v_CountExists + 1;
								break;
							}
						}
					}
					// 
					// doesn't exist, so let's add it
					if(v_CountExists == 0)
					{
						// 
						// prevent duplicates by updating record with existing name if exists
						v_Name = v_CandidateZohoID + "" + r_Data.get("id");
						// 
						// retrieve client's own candidate ref from candidate record
						r_CandidateDetails = zoho.recruit.getRecordById("Candidates",v_CandidateZohoID);
						v_CandidateRef = if(!isnull(r_CandidateDetails.get("Candidate Ref")),r_CandidateDetails.get("Candidate Ref"),"");
						// 
						// start building addrecord request
						m_Create = Map();
						// 
						// specify unique name to ensure we don't duplicate these
						m_Create.put("Candidate x JobOpening Name",v_Name);
						// 
						// specify the Zoho Recruit Candidate ID
						m_Create.put("Candidate_ID",v_CandidateZohoID.toLong());
						// 
						// specify the client's own Candidate Ref
						m_Create.put("Candidate Ref",v_CandidateRef);
						// 
						// specify the Zoho Recruit Job Opening ID
						m_Create.put("Job Opening_ID",r_Data.get("id"));
						// 
						// specify the client's own Job Opening Ref
						m_Create.put("Vacancy Ref",r_Data.get("Job_Opening_ID"));
						// 
						// update the record (note the API name being used here)
						r_Create = zoho.recruit.addRecord("CustomModule6",m_Create,0,false,"my_connection");
						//info r_Create;
						v_CountProcessed = v_CountProcessed + 1;
					}
				}
			}
		}
	}
	info "Page #" + v_Page + ": Processed " + v_CountProcessed + " of " + v_CountTotal;
}

/* *******************************************************************************
Function: void fn_AB_GenerateVacanciesCandidatesAssociations()
Workflow: -
Trigger: Standalone function triggered on execute
Purpose: Loops through all candidates and creates records referring to the Candidate Ref and Job Opening
Date Created: 2023-02-02 (JoelLipman.com - Joel Lipman)
- Initial release
Date Modified: ????-??-?? (??? - ???)
-
******************************************************************************* */
//
// list of pages to loop through (note: issues with multiple pages, do 1 at a time)
l_Pages = {1};
v_PerPage = 200;
for each v_Page in l_Pages
{
v_CountTotal = 0;
v_CountProcessed = 0;
v_FromIndex = (v_Page - 1) * v_PerPage;
v_ToIndex = v_FromIndex + v_PerPage - 1;
//
// note that ordering here seemed to do nothing... it's in an order, not sure what order. Let's loop through every page anyway.
l_Candidates = zoho.recruit.getRecords("Candidates",v_FromIndex,v_ToIndex,"Modified_Time","CANDIDATEID","asc","my_connection");
//info l_Candidates;
//
// sanity check to output that it did something
info "Done: " + zoho.currenttime.toString("HH:mm:ss");
//
// loop through our list for this page
for each r_Candidate in l_Candidates
{
v_CountTotal = v_CountTotal + 1;
v_CandidateZohoID = ifnull(r_Candidate.get("CANDIDATEID"),0);
r_Associated = invokeUrl
[
url :"https://recruit.zoho.com/recruit/v2/Candidates/" + v_CandidateZohoID + "/associate"
type :GET
connection:"my_connection"
];
//info r_Associated;
if(!isnull(r_Associated.get("data")))
{
for each r_Data in r_Associated.get("data")
{
if(!isnull(r_Data.get("id")))
{
//
// search by record name to see if we've already added this record before
// note the endpoint and then the field comparison here
v_SearchEndpoint = "https://recruit.zoho.com/recruit/v2/Candidates_x_JobOpenings/search?criteria=(CustomModule6_Name:equals:" + v_CandidateZohoID + r_Data.get("id") + ")";
l_CheckExists = invokeUrl
[
url :v_SearchEndpoint
type :GET
connection:"my_connection"
];
v_CountExists = 0;
if(!isnull(l_CheckExists.get("data")))
{
for each r_Check in l_CheckExists.get("data")
{
if(!isnull(r_Check.get("id")))
{
v_CountExists = v_CountExists + 1;
break;
}
}
}
//
// doesn't exist, so let's add it
if(v_CountExists == 0)
{
//
// prevent duplicates by updating record with existing name if exists
v_Name = v_CandidateZohoID + "" + r_Data.get("id");
//
// retrieve client's own candidate ref from candidate record
r_CandidateDetails = zoho.recruit.getRecordById("Candidates",v_CandidateZohoID);
v_CandidateRef = if(!isnull(r_CandidateDetails.get("Candidate Ref")),r_CandidateDetails.get("Candidate Ref"),"");
//
// start building addrecord request
m_Create = Map();
//
// specify unique name to ensure we don't duplicate these
m_Create.put("Candidate x JobOpening Name",v_Name);
//
// specify the Zoho Recruit Candidate ID
m_Create.put("Candidate_ID",v_CandidateZohoID.toLong());
//
// specify the client's own Candidate Ref
m_Create.put("Candidate Ref",v_CandidateRef);
//
// specify the Zoho Recruit Job Opening ID
m_Create.put("Job Opening_ID",r_Data.get("id"));
//
// specify the client's own Job Opening Ref
m_Create.put("Vacancy Ref",r_Data.get("Job_Opening_ID"));
//
// update the record (note the API name being used here)
r_Create = zoho.recruit.addRecord("CustomModule6",m_Create,0,false,"my_connection");
//info r_Create;
v_CountProcessed = v_CountProcessed + 1;
}
}
}
}
}
info "Page #" + v_Page + ": Processed " + v_CountProcessed + " of " + v_CountTotal;
}

Additional Note(s):

To create a connection in ZohoRecruit:
1. Go to Setup > Developer Space > Functions
2. Create or Edit a function
3. At the top of the IDE there will be a "Connections" link to click on.
4. Click on "Create Connection".
5. Pick the service (eg. "Zoho OAuth"). Enter the connection details and scope.

Error(s) Encountered:

{"code":"4401","message":"Unable to populate data, please check if mandatory value is entered correctly."}: Check your connection string.
{"code":"9832","message":"Mandatory parameter(s) missing"}: Not sure why as the only mandatory field I had was the name field. Create a sample record with just the name field (placed at top of request) and comment out the other fields from the rest of the request to test. Once you have successfully created a record using only the name field (no underscores - as displayed on the record), uncomment other fields to add to the addrecord request.
Lookup field isn't being populated: Find the lookup field suffixed with _ID and send the correct record ID to this field instead. The lookup field (name only) can be omitted from the request.

Category: Zoho :: Article: 830