- UNIFYBroker Forum
-
Questions
Reverse Join, Conditional on Not-Null
Related to my ticket Join Condition on Not-Null, I need some processing on the connector side of the adapter to correctly join.
I want to join the position table back to the person table to resolve a positionReportsToPosition attribute to a personnumber but the person table contains two attributes with position numbers. One has a value or is null, the other always has a value. I want to use the first if it has a value, otherwise the second. Since I can't join adapters, there's nowhere to process this logic on the employee side.
Do I need to implement a PowerShell connector that queries the IdB DB like Carols script for flattening Org Units. It could just contain the employeeID and the calculated positionnumber.
Answer
Hey Daniel,
Querying the IdB DB is not a supported or endorsed operation. Doing this is at your own risk and has the potential to cause problems.
From what I understand, you're trying to get the person number of the manager based on someones position. Is that correct?
If so, as discussed in your other ticket we already have a join to that persons position. Using that join, we can get the positionReportsToPosition as you've mentioned above. You can then use that to join back to the employee connector onto one of the position fields. Tell it to pull back the person number into a new field, such as reportsPersonTemp Then add a second join again to the employee connector, but on the position field that's always full. Push this into a field such as reportsPersonPerm. You can then use a powershell transformation to determine which value to use - if the reportsPersonTemp is populated, then push that value into reportsPersonNumber. Otherwise, use the value from reportsPersonPerm.
When you configure a join, if there's no value to join on then the field won't be populated, so you'll be able to easily tell which value to use.
Customer support service by UserEcho
Hey Daniel,
Querying the IdB DB is not a supported or endorsed operation. Doing this is at your own risk and has the potential to cause problems.
From what I understand, you're trying to get the person number of the manager based on someones position. Is that correct?
If so, as discussed in your other ticket we already have a join to that persons position. Using that join, we can get the positionReportsToPosition as you've mentioned above. You can then use that to join back to the employee connector onto one of the position fields. Tell it to pull back the person number into a new field, such as reportsPersonTemp Then add a second join again to the employee connector, but on the position field that's always full. Push this into a field such as reportsPersonPerm. You can then use a powershell transformation to determine which value to use - if the reportsPersonTemp is populated, then push that value into reportsPersonNumber. Otherwise, use the value from reportsPersonPerm.
When you configure a join, if there's no value to join on then the field won't be populated, so you'll be able to easily tell which value to use.