how can we avoid creation of users but not updation from import based on some condition

[sampath18]

hi,

We have a requirement where we need not to import new users if date(NEWUSERDATA.startdate) < SUBDATE(curdate(), INTERVAL 7 DAY).

I have tried below logic in modifyuserdatajson but its still creating the user.We dont want any effect on the updation for the existing user having date(NEWUSERDATA.startdate) < SUBDATE(curdate(), INTERVAL 7 DAY).

{
"ADDITIONALTABLES": {
"USERS": "SELECT Employeeid,location,startdate,customproperty32 FROM USERS",
"TATTR": "SELECT attribute1,datasetname,attribute3 FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY49"
],
"PREPROCESSQUERIES": [
"DELETE t1 FROM NEWUSERDATA t1 JOIN CURRENTUSERS t2 ON t1.employeeid = t2.employeeid WHERE date(t1.startdate) < SUBDATE(curdate(),INTERVAL 14 DAY) and t2.employeeid is null",
"DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.customproperty32 not in ('Confirmed','Activated','Accepted','Created')",
"DELETE FROM NEWUSERDATA WHERE date(NEWUSERDATA.startdate) > ADDDATE(curdate(), INTERVAL 21 DAY)",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY49=(select attribute3 from CURRENTTATTR where datasetname = 'Active_Directory_OUs' and attribute1 = NEWUSERDATA.location)"
]
}

I also tried below but it didnt worked.

{
"ADDITIONALTABLES": {
"USERS": "SELECT Employeeid,location,startdate,customproperty32 FROM USERS",
"TATTR": "SELECT attribute1,datasetname,attribute3 FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY49"
],
"PREPROCESSQUERIES": [
"DELETE FROM NEWUSERDATA WHERE date(NEWUSERDATA.startdate) < SUBDATE(curdate(),INTERVAL 14 DAY) and NEWUSERDATA.employeeid not in (select distinct(cu.employeeid) from currentusers cu)",
"DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.customproperty32 not in ('Confirmed','Activated','Accepted','Created')",
"DELETE FROM NEWUSERDATA WHERE date(NEWUSERDATA.startdate) > ADDDATE(curdate(), INTERVAL 21 DAY)",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY49=(select attribute3 from CURRENTTATTR where datasetname = 'Active_Directory_OUs' and attribute1 = NEWUSERDATA.location)"
]
}

[This message has been edited by moderator to merge reply comment]

[sk]

Try this

DELETE NU FROM NEWUSERDATA NU WHERE NU.startdate < SUBDATE(curdate(),INTERVAL 14 DAY)

[sampath18]

using DELETE NU FROM NEWUSERDATA NU WHERE NU.startdate < SUBDATE(curdate(),INTERVAL 14 DAY) new users will not get created but it will also not do update for the existing users.

From Solved: How to prevent creating a user profile from SAV sc... - Saviynt Forums - 58969 its not working for me .

{
"ADDITIONALTABLES": {
"USERS": "SELECT Employeeid,location,startdate,customproperty32 FROM USERS",
"TATTR": "SELECT attribute1,datasetname,attribute3 FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY49"
],
"PREPROCESSQUERIES": [
"DELETE FROM NEWUSERDATA WHERE date(NEWUSERDATA.startdate) < SUBDATE(curdate(),INTERVAL 14 DAY) and EMPLOYEEID not in (select EMPLOYEEID from CURRENTUSERS)",
"DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.customproperty32 not in ('Confirmed','Activated','Accepted','Created')",
"DELETE FROM NEWUSERDATA WHERE date(NEWUSERDATA.startdate) > ADDDATE(curdate(), INTERVAL 21 DAY)",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY49=(select attribute3 from CURRENTTATTR where datasetname = 'Active_Directory_OUs' and attribute1 = NEWUSERDATA.location)"
]
}

[sk]

Why existing users will not get updated? I thought you don't want to bring users whose start date is not more than 7/14 days from current date. Above statement will only remove the records whose start date is more than 7/14 days from current date and it will not touch any other records

[sampath18]

Yes, what happens if there is an update for existing users from the target whose start date is older than 14 days in the past?

For instance, if we use the logic: DELETE NU FROM NEWUSERDATA NU WHERE NU.startdate < SUBDATE(curdate(), INTERVAL 14 DAY), then users with a start date of 29th February 2024 will not receive any updates due to this criteria.

Also,seems like below logic is also not working.

"DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.Employeeid NOT IN (SELECT DISTINCT cu.Employeeid FROM CURRENTUSERS cu)",  

as i was planning to use this:-

"DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.Employeeid NOT IN (SELECT DISTINCT cu.Employeeid FROM CURRENTUSERS cu) and date(NEWUSERDATA.startdate) < SUBDATE(curdate(),INTERVAL 14 DAY)"

[rushikeshvartak]

Do you have update date column coming from HR Source

[sampath18]

Hi @rushikeshvartak i guess not but we are getting start date for Revisions/changes utilization. Is this what you were asking?

{
            "Consolidated Worker ID": "BNCOWM00000012",
            "First Name": "Michael",
            "Last Name": "Annington",
            "Work Order/Work Order Revision Owner Employee ID": "41105",
            "Request Office Code": "135",
            "Case Code Code": "K8MR",
            "Start Date": "01/01/2024",
            "End Date": "06/30/2024",
            "Original Revision Start Date": "01/01/2024",
            "Work Order Status": "Confirmed",
            "Worker Status": "Active",
            "Closed Reason": "",
            "Custom Fields": [
                {
                    "name": "RTN Sponsor",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Contractor Operating Office",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Office Access Needed",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Bain Hardware Needed",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Alteryx",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Tableau",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "IRIS",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Springboard",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Graphics Training Needed",
                    "module": "Work Order",
                    "value": ""
                },
                {
                    "name": "Candidate email",
                    "module": "Candidate",
                    "value": ""
                }
            ]
        },

-Original Revision Start Date field will be used for import based on date(NEWUSERDATA.customproperty35) < SUBDATE(curdate(),INTERVAL 14 DAY))
-Revisions/changes to utilize the Start Date field

[PinkyChau]

issue is resolved after using below json.

{

"ADDITIONALTABLES": {

"USERS": "SELECT employeeid FROM USERS"

},

"COMPUTEDCOLUMNS": [

"employeeid"

],

"PREPROCESSQUERIES": [

"DELETE FROM NEWUSERDATA WHERE employeeid not in (select employeeid from CURRENTUSERS where  employeeid is not null)"

]

}

[rushikeshvartak]

Great

Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.