Hi,
We have a requirement to generate PIN number for external employees via either SAV to SAV or via the Create User Form (Dynamic Attributes) (Recommended)
Use Case :
If an employee joins a company, his PIN will be generated based on [CompanyCode][Maximum Number of the last number of an user in system of that company +1 ]
Employee 1 belongs to company ABC Pvt Ltd, will have PIN as ABC1
Employee2 joins company ABC Pvt Ltd, will have PIN value as ABC2
Employee3 joins company XYZ Corporation will have PIN as XYZ1
We have the Company names and Company Codes stored in Dataset. Company name is stored in customproperty12 and PIN value in cp28.
Via SQL query I am able to generate the maximum number of all users but not based on company to company.
Attaching my query for generating the highest number and incrementing by 1. Let me know if you need additional information. Any help is appreciated.
Query1:
select case when u.customproperty12 =dataset_values.attribute1 then (MAX(CAST(substring(u.customproperty28,5,3) as UNSIGNED) )) +1 END as ID, u.username from users u, dataset_values where u.customproperty28 !='' AND dataset_values.datasetname = 'CustomerSupplier' AND u.customproperty12 = dataset_values.attribute1
Returns the highest integral number, incremented by 1 after analysing all users' cp28
*dataset_values.attribute1 is the column in dataset that returns the Company name
*dataset_values.attribute2 is the column in dataset that returns the Company Code
*dataset_values.datasetname is the name of the dataset
Query2
select case when u.customproperty12 ='XYZ' then (MAX(CAST(substring(u.customproperty28,5,3) as UNSIGNED) )) +1 END as ID, u.username from users u, dataset_values where u.customproperty28 !='' AND dataset_values.datasetname = 'CustomerSupplier' AND u.customproperty12 = 'XYZ'
Returns the required result, but we do not wish to hardcode the company name in the query and automate it completely.
