• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

BR to update am_asset location field when Main User field is populated
#1
So I'm trying to update location and department fields automatically after the main user field is populated which will be done via sccm.  I am able to get an email generated when the field changes but that is all I got working so far.  This is the first BR I'm trying to create so there are some unknowns I'm hoping to get answered.

I figured I'd start with location ID and by reverse engineering some other BR's we have, I came up with the Conditional Step below.  I'm not exactly sure what (@ID@) is but since it was used in other BR's I figured the answer would reveal itself.

Conditional Step
select AM_EMPLOYEE.LOCATION_ID from AM_EMPLOYEE

INNER JOIN AM_ASSET on AM_EMPLOYEE.EMPLOYEE_ID = AM_ASSET.EMPLOYEE_ID where AM_ASSET.EMPLOYEE_ID in (@ID@) 

Instance Variable Name = EMPID
Value Type = Variable 
Varuable/Value = @ ResulT

From there, I'm simply sending an email to myself with #[VAR.EMPID]# in the message to see the results.  The results are coming back as NULL.
jvolltrauer, proud to be a member of EV CONNECT FORUM since Apr 2018.

#2
I figured out where I was going wrong. where AM_ASSET.EMPLOYEE_ID in (@ID@) this wasn't needed. Still curious about the questions I had.
jvolltrauer, proud to be a member of EV CONNECT FORUM since Apr 2018.

#3
(04-10-2020, 05:45 PM)jvolltrauer Wrote: So I'm trying to update location and department fields automatically after the main user field is populated which will be done via sccm.  I am able to get an email generated when the field changes but that is all I got working so far.  This is the first BR I'm trying to create so there are some unknowns I'm hoping to get answered.

I figured I'd start with location ID and by reverse engineering some other BR's we have, I came up with the Conditional Step below.  I'm not exactly sure what (@ID@) is but since it was used in other BR's I figured the answer would reveal itself.

Conditional Step
select AM_EMPLOYEE.LOCATION_ID from AM_EMPLOYEE

INNER JOIN AM_ASSET on AM_EMPLOYEE.EMPLOYEE_ID = AM_ASSET.EMPLOYEE_ID where AM_ASSET.EMPLOYEE_ID in (@ID@) 

Instance Variable Name = EMPID
Value Type = Variable 
Varuable/Value = @ ResulT

From there, I'm simply sending an email to myself with #[VAR.EMPID]# in the message to see the results.  The results are coming back as NULL.

What table is your business rule running off of, AM_ASSET? The @ID@ variable is the Parent Key of whatever table you're working with. In this case, if you are working on AM_ASSET, the variable would return the ASSET_ID of the Asset that triggered this business rule. This is crucial so that your SQL queries don't blanket update all of your assets, just the one that caused the Business Rule to run.

If you're looking to copy the assigned user's location/dept down to the asset, your query would look something like this below. Make sure the "keep a trace of the execution" checkbox is checked in the related process configuration so a log is captured of the running SQL statements.

UPDATE AST
SET AST.LOCATION_ID = EMP.LOCATION_ID, AST.DEPARTMENT_ID = EMP.DEPARTMENT_ID
FROM AM_ASSET AST
INNER JOIN AM_EMPLOYEE EMP ON EMP.EMPLOYEE_ID = AST.EMPLOYEE_ID
WHERE AST.ASSET_ID IN (@ID@)
cmiller, proud to be a member of EV CONNECT FORUM since Oct 2015.

#4
(04-13-2020, 08:03 AM)cmiller Wrote:
(04-10-2020, 05:45 PM)jvolltrauer Wrote: So I'm trying to update location and department fields automatically after the main user field is populated which will be done via sccm.  I am able to get an email generated when the field changes but that is all I got working so far.  This is the first BR I'm trying to create so there are some unknowns I'm hoping to get answered.

I figured I'd start with location ID and by reverse engineering some other BR's we have, I came up with the Conditional Step below.  I'm not exactly sure what (@ID@) is but since it was used in other BR's I figured the answer would reveal itself.

Conditional Step
select AM_EMPLOYEE.LOCATION_ID from AM_EMPLOYEE

INNER JOIN AM_ASSET on AM_EMPLOYEE.EMPLOYEE_ID = AM_ASSET.EMPLOYEE_ID where AM_ASSET.EMPLOYEE_ID in (@ID@) 

Instance Variable Name = EMPID
Value Type = Variable 
Varuable/Value = @ ResulT

From there, I'm simply sending an email to myself with #[VAR.EMPID]# in the message to see the results.  The results are coming back as NULL.

What table is your business rule running off of, AM_ASSET? The @ID@ variable is the Parent Key of whatever table you're working with. In this case, if you are working on AM_ASSET, the variable would return the ASSET_ID of the Asset that triggered this business rule. This is crucial so that your SQL queries don't blanket update all of your assets, just the one that caused the Business Rule to run.

If you're looking to copy the assigned user's location/dept down to the asset, your query would look something like this below. Make sure the "keep a trace of the execution" checkbox is checked in the related process configuration so a log is captured of the running SQL statements.

UPDATE AST
SET AST.LOCATION_ID = EMP.LOCATION_ID, AST.DEPARTMENT_ID = EMP.DEPARTMENT_ID
FROM AM_ASSET AST
INNER JOIN AM_EMPLOYEE EMP ON EMP.EMPLOYEE_ID = AST.EMPLOYEE_ID
WHERE AST.ASSET_ID IN (@ID@)


That is ultimatly much neater than what I did with pulling asset tag steps and splitting this up in to 3 different steps as I'm also making the machine active.   Thanks for the tips here.  This was very helpful.
jvolltrauer, proud to be a member of EV CONNECT FORUM since Apr 2018.






Users browsing this thread: 1 Guest(s)