I may be doing this the hard/wrong way but in my goal to map a custom ADSAccount's "CCC_Manager" field to match the associated UID_Person's manager's (UID_PersonHead) Distinguished name from their (first found, ADS account) I was trying to do this via the value calculation for the custom field via something like this:
UID_Manager = Query.From(Table.Person).Select(Table.Person.UID_PersonHead).Where(Table.Person.UID_Person = $UID_Person$)
Value = Query.From(Table.ADSAccount).Select(Table.ADSAccount.DistinguishedName).Where(Table.ADSAccount.UID_Person = UID_Manager)
A normal query that works via a process orchestration (I don't like doing it here) looks like:
UPDATE ADSAccount SET CCC_Manager = (SELECT TOP 1 DistinguishedName FROM ADSAccount WHERE UID_Person = (SELECT TOP 1 UID_PersonHead FROM Person WHERE UID_Person ='"+$UID_Person$+"')) WHERE UID_Person='"+$UID_Person$+"'")
I have not yet figured out the syntax for doing SQL Queries from the Value Calculation piece. Or do you have a better method/idea how to map this?