Unable to infer employee-region relation given employees have accounts and accounts belong to region

Hello all,

I am building a standard org. hierarchy consisting of employees, accounts and regions with the following rules:

1) Each account can be assigned with only 1 employee.

2) Each account belongs to only 1 region.
3) Each account for one employee is within one region.

For some calculation purposes, I need to know, to which region each employee belongs. Given the relations described above,
this should be makeable since we know the account-employee and account-region relationship.

I have tried functions like Lookup and Firstnonblank, but both failed as there is no list-formatted line item

in the target module (Employee) which would match the dimensions of the source module (Accounts).

I depicted the schema below:

szechovsky_0-1604345024912.png

 

Thanks for any hints,

Stepan

 

Best Answer

  • What does your Employee.Region formula look like? I think I was able to achieve your goal using Firstnonblank.

     

    Accounts module (I combined the two accounts modules in your schema for simplicity) - 'Employee' and 'Region' are both list-formatted

    dameyer01_0-1604347666595.png

    Employee module - 'Region' is list-formatted

    dameyer01_1-1604347741569.png

     

Answers

  • Dameyer, you nailed it!

    The trick was to use the list-formatted employee line item from the source, not the target module.

    Thanks a lot!

    Stepan