Postcode Distance Configuration
Data Source
The postcode location data will be kept up to date by the Static Data Download facility of IQXHub. Ensure that you have a hub configured to do this.
Columns in Vacancy & Person Search Results
In Maintenance | Database Setup | Custom Grid Columns choose Department: DEFAULT, Form: VACANCY, View: SEARCHRESULT
and then Add the details shown:
the Descriptor should read:
round(DistanceBetweenPostCodes(Person.PostCode,:Q_SitePostCodeForMap,'Mi'),1)
Then click Update To add a distance column to the Person Vacancy Search Results table, choose Department: DEFAULT, Form: PERSON, View: SEARCHRESULT and then click Add again. Then complete the details below:
This time the Descriptor should be:
round(DistanceBetweenPostCodes(:Q_PostCode,isnull(vacancy.postcode, company.postcode) ,'Mi'),1)
Column in Shift Matcher
Set the Database Function: Vacancy Shift Matcher Numeric Custom Column to
( in @PersonID char(20),in @VacancyID char(20) ) returns double begin declare rv double; set rv = DistanceBetweenPostCodes( (select Person.PostCode from Person where Person.PersonID = @PersonID), (select isnull(vacancy.postcode,company.postcode) from Vacancy key join Employment key join Company where Vacancy.VacancyID = @VacancyID),'Mi'); if rv < 1 then return round(rv,1) else return round(rv,0) end if end
Set the General Setting 'Caption for Shift Matcher Custom Column' to 'Distance'.
Searchable Criteria
This SQL will add 'Radius from Postcode' to candidate & company searchable criteria, 'Radius from Vacancy' to vacancy requirements, 'Radius from Person' to vacancy searchable criteria.
insert into dictionary(minstep,dictionaryid,dictionarylocation,ItemType,databaseItem,ComboSelections,Description,SortOrder,Units,ComplexExpression) values( 1,'PRD','P','S','Not Used','','Radius from Postcode',310,'Miles','DistanceBetweenPostCodes(Person.PostCode,''<SVALUE>'',''Miles'') between <N1> and <N2>'); insert into dictionary(minstep,dictionaryid,dictionarylocation,ItemType,databaseItem,ComboSelections,Description,SortOrder,Units,ComplexExpression) values( 1,'PRR','P','B','Not Used','','Radius from Vacancy',300,'Miles','DistanceBetweenPostCodes(Person.PostCode,(select isnull(Vacancy.PostCode,Company.PostCode) from Vacancy key join Employment key join Company where Vacancy.VacancyID = <XID>),''Mi'') between <N1> and <N2>'); insert into dictionary(minstep,dictionaryid,dictionarylocation,ItemType,databaseItem,ComboSelections,Description,SortOrder,Units,ComplexExpression) values( 1,'CRD','C','S','Not Used','','Radius from Postcode',310,'Miles','DistanceBetweenPostCodes(Company.PostCode,''<SVALUE>'',''Miles'') between <N1> and <N2>'); insert into dictionary(minstep,dictionaryid,dictionarylocation,ItemType,databaseItem,ComboSelections,Description,SortOrder,Units,ComplexExpression) values( 1,'VRR','V','B','Not Used','','Radius from Person',300,'Miles','DistanceBetweenPostCodes((select Person.PostCode from Person where Person.PersonID = <XID>),isnull(Vacancy.PostCode,Company.PostCode),''Mi'') between <N1> and <N2>');