Applicant Import Specification in PICSWeb
Instructions on using this specification to create an import file are available in Uploading a New Import File.
A CSV/XLSX file import can be used to bulk import and update Applicant data from other data sources. The table below shows what types of import are available for applicants.
Supported Options |
|
---|---|
CSV file format accepted | |
XLSX file format accepted | |
User Defined Fields supported | |
Record Handling |
|
Single record in file | User redirected to the New Applicant window |
Multiple records in file | Records added direct to database |
For the import to work, the file must be in the following format. If no data is found for the mandatory columns, no records will be imported.
When multiple rows of data are provided the importer looks for matching a matching Applicant record in PICS using the APIDENT field. Any matches found in PICS will be updated with data from the CSV.
To prevent duplicate Applicant records from being imported data must be supplied in a combination of the the following columns:
OTHER_SYS_ID - if a value is supplied and a Live applicant with a matching surname is found the record is not imported.
or
APP_ULN - if a value is supplied and a Live applicant with a matching surname is found the record is not imported.
or
APP_BIRTHDATE / APP_NI_NO - if either or both value is supplied and a Live applicant with a matching surname is found the record is not imported.
Further information is also available on the following:
Column Name | Required? | Format | Example | Notes |
---|---|---|---|---|
APIDENT |
| 5 alphanumeric | 0005Z | Used for matching existing Applicant records in PICS |
APP_SURNAME | Yes | string | Smith | Used for matching existing Applicant records in PICS |
APP_FIRSTNAMES | Yes | string | Roberta | Used for matching existing Applicant records in PICS |
APP_PREFFIRSTNAME expected mid October 2023 |
| string | Ro | Preferred First Name |
TAG_IDS |
| string |
| INTERNAL USE ONLY |
TAG_NAMES |
| string | April 2022*Engineers* | Names of the tags to create * separated |
APP_ADDR1 |
| string | Flat A | First line of Home Address |
APP_ADDR2 |
| string | 123 Acacia Avenue | Second line of Home Address |
APP_ADDR3 |
| string | Suburb | Third line of Home Address |
APP_ADDR4 |
| string | Townville | Fourth line of Home Address |
APP_POSTCODE |
| string | TO1 1WN | Home Postcode |
APP_PHONE |
| numeric | 01603492620 | Home Phone number |
APP_MOBPHONE |
| numeric | 07799000001 | Mobile Phone number |
APP_EMAIL |
| string |
| |
APP_NI_NO |
| string | JB010101A |
|
APP_BIRTHDATE |
| date | 30/10/2000 |
|
APP_SEX |
| string | F | 1: Male |
APP_ETHNICITY |
| numeric | 34 | |
APP_TITLE |
| numeric | 4 | |
APP_LSCDIFFIC |
| string | 2 | ILR Learner’s Self Assessed Disability status |
APP_ALL_LLDD |
| start separated list of codes | 12*13*96 | |
APP_PRIMARY_LLDD |
| numeric | 12 | ILR Primary LLDD |
CONT_METH |
| string | PETS | Allowed Contact Methods between Provider and Learner. E: Email P: Post T: Telephone S: SMS |
PREF_CONTACT_METH |
| string | E | Preferred Contact Method between Provider and Learner. |
APP_EMPCODE |
| 6 alphanumeric | EMP001 | Organisation record internal code |
APP_CANSTART |
| date | 01/01/2022 | Date of probably the start of programme |
APP_CURSTATUS |
| 2 alphanumeric | 01 | Status code from Applicant Status Workflow configuration |
APP_CURSUBSTATUS |
| 5 alphanumeric | 10000 | Sub-status code from Applicant Status Workflow configuration |
APP_CURSTATUS_NOTES |
| string |
| Note to be attached to the Status change record created from APP_CURSTATUS or the Default Status for New Applicants |
APP_OFF_TRAIN |
| PICS Officer code | FREDDY01 | The Main Officer for the Applicant |
APP_MENTOR |
| PICS Officer code | MENTOR01 | The Mentor Officer for the Applicant |
APP_RECRUITED_BY |
| PICS Officer code | RECRUI01 | The Recruitment Officer for the Applicant |
APP_QUALPLAN |
| 8 alphanumeric | QUALPL01 | Valid Qualification Plan code |
APP_PLANNED_HOURS |
| numeric |
| Planned Hours for ILR Programme |
APP_ULN |
| numeric |
| Learner Record Service - Unique Learner Number |
APP_LRS_VERIFTYPE |
| numeric code |
| Learner Record Service - Identity Verification Type
0: None 1: Relationship with School 2: Passport 3: Driving licence 4: ID card (national identification) 5: National Insurance card 7: Bank credit/debit card |
LRS_PRIVCYNOTICESEEN |
| numeric code |
| Learner Record Service - Privacy Notice Seen |
APP_NOK_NAME |
| string |
| Next of Kin Name |
APP_NOK_REL |
| string |
| Next of Kin Relationship |
APP_NOK_PHONE |
| string |
| Next of Kin Phone 1 |
APP_NOK_PHONE2 |
| string |
| Next of Kin Phone 2 |
APP_NOK_EMAIL |
| string |
| Next of Kin Email |
APP_HHS |
| star separated list of codes | 1*2* | ILR House Hold Situation (HHS) |
APP_GCSEENGGRADE |
|
|
| ILR English GCSE Grade |