Creating a Recursive Script

[robertmueller]

Can anybody advise what script to use to be able to help build a report based on the users "Level 1" Company.

We have over 1000 companies that can be selected for our users, however we only have 5 "Level 1" companies. The remaining companies will then have a parent company that leads into the 5 “Level 1” companies.

I would like to run a report that uses a recursive script that runs through the parent companies up the levels until it finds the parent that is a “Level 1” company. Note that they are identified by a tick box that is ticked if they are a “Level 1” company.

The complexity comes in that some users could have their company as the “level 1” company, others it maybe the parent company, and others may need to go through 2 or more levels of parent companies before it reaches the “Level 1” company. Ie

User	Company	Parent Company	Parent Company	Parent Company
User 1	Level 1 Company
User 2	Level 2 Company	Level 1 Company
User 3	Level 4 company	Level 3 company	Level 2 Company	Level 1 Company

[Mark Stanger]

What is the field name of the reference field linking a child company to its parent?  What is the field name of the tick box (which I assume is a true/false field)?

[Mark Stanger]

Also, I assume all of your companies reside in the 'core_company' table?

[robertmueller]

Yes they are all in the core company table.

Field Label is 'Parent'

Tick box is 'u_level_1'

Yeah true/false field

Thank you for your help

[Mark Stanger]

So you just want to iterate up the chain until you find the L1 company and return that value, right?  If so, this function should work.  You'll need to set it up in a script include.  The name of the script include should be 'getLevel1Company' and the 'Client callable' checkbox should be checked.

You can call this function from any filter, etc. with 'javascript:getLevel1Company()'.

function getLevel1Company(companyID) {
	// Allow a company sys_id to be passed in. If empty, get user company.
	if (!companyID) {
		// Query for the current user's company
		companyID = gs.getUser().getRecord().getValue('company');
	}
	var l1Company = recursCompany(companyID);
	return l1Company;
}

function recursCompany(companyID) {
	var answer = '';
	// Query for the company
	var company = new GlideRecord('core_company');
	company.get(companyID);
	if (company) {
		if (company.u_level_1) {
			answer = company.sys_id;
		}
		else {
			// Move up a level and try again
			answer = recursCompany(company.parent);
		}
	}
	return answer;
}