Enabled role based look up tables formula | Compensation

[aformisano]

Dear Community,

Based on our client's requirement, we have enabled the role based permission for Lookup Tables. The rational is:

Group A can access lookup table "File_A"
Group B can access lookup table "File_B"

Both files have the same input,i.e. Person ID, and the same output,i.e. Pay Grade.

The difference is that File_A contains employees of country Italy and the second of country USA.

We need a formula to merge these files within 1 column. That is, to have 1 column where the formula can read both File_A and File_B. If the person_id is in File_A then it will return the corresponding value, else to read File_B, etc

We tested if(country = ITA, lookup(File_A,person_if,1), if( country= USA, lookup(File_B,person_id,1))

but the system returns N/A even if the person ids exist in the files.

Do we know how to approach the issue?

Thanks in advanced

[pmacgovern]

Make two custom columns, one for each lookup. Then a third column that is like your formula. Make sure there is a 'default' row for each lookup table

[aformisano]

Thank you Philip for your input. Honestly, we try to avoid this scenario, because this is just the test. In reality, there will be 9 different lookup tables. So with this method we will need 10 columns for every information stemming from a lookup

[pmacgovern]

It may be just that you are not putting a default row in each table. If any lookup doesn't find a match, it returns NA. If any value in an if condition returns NA, then the whole thing is NA.

[aformisano]

This worked indeed. Thank you so much Philip

[pmacgovern]

I should comment that it is not a great practice to make userID driven lookup tables. If the organisation is very large, this could result in multiple megabyte lookup tables that load for every manager, slowing down the load of compensation worksheets. Custom MDF objects are a better solution and offer the same level of RBP control (and there would be no need to make any formulas). If you must use userID lookups, then please ensure the "Encrypt lookup tables" option is selected in Company Settings to give some level of protection.

[xavierlegarrec]

a.formisano22 We need to be careful with these kind of designs using UserID as per Phil's comment as it can easily boomerang back to us when customers use the system in production and complain about latency. Please see this blog: https://blogs.sap.com/2021/06/08/how-to-optimize-screen-loading-times-in-the-compensation-module/