comparing a ref based column in Table 2 to another ref based column in Table 3 (within a select)

[Segolene_Sansom]

Hi,

I've got Table 1: "HR Basic Info" where the Key column is an employee email.

Table 2: "Basic Leave Info" has a ref type column to Table 1 called "BL Employee"

Table 3: "Annual Leave" has a ref type column to Table 1 called "AL Employee"

When i try putting in a select formula (below) on table 3 it comes back as Blank. Would anyone know why?

select(
Base Leave Info[Actual Worked Days],
[BL Employee]=[_THISROW].[AL Employee]
)

I've tried the same formula with different columns in the select-row formula and it works. (i actually need the formula to return the values based on date and employee and troubleshooting led me to this component not working).

Thanks in advance for your help!

[jrullan]

@Segolene_Sansom ,

I'm not entirely sure if I understand your issue correctly, but I will share some thoughts that come to mind when I have to do troubleshooting.

When designing your data tables you typically will select the names of the tables based on the entities they represent since later on it is easier to keep track of complex relationships between tables if you follow a naming convention. For example, a better approach IMHO would be to have an Employees table that represents the basic info about the employee. 

Related tables

Then you would have a column named Employee_ID in any table that references a particular employee (Basic Leave or Annual Leave for example). Employee_ID would be a column of type Ref that points to Employees. Also, note that in your case both (Basic and Annual) tables hold information that belongs to the Employee exclusively, so Employee_ID has to have the option "Is a Part Of" marked.

After having considered these "design concerns" then there is no reason for a properly configured Select statement to provide you with the requested data. 

In your case, for example, to request a column value from Basic Leave Info in your Annual Leave Info table, the following statement should work:

# From Annual Leave Info table...

Select(
  Basic Leave Info[Actual Worked Days],
  AND(
    [Employee_ID] = [_ThisRow].[Employee_ID],
    [Basic_From_Date] = [_ThisRow].[Annual_From_Date],
    [Basic_To_Date] = [_ThisRow].[Annual_To_Date]
  )
)

Remember also that Select will give you a List of values of SomeColumn for records in Basic Leave Info that match the Annual Leave Info record's Employee_ID and some date.  So check if wherever you are using this formula is expecting a List.  If you are expecting only one result in [Actual Worked Days] then you must enclose the formula with Index ( ..... , 1). 

I hope that something here could help you troubleshoot your issue. 

[Segolene_Sansom]

Thanks for your reply!

That is the way i have setup my information. I have just made the email address the key for that Employees table.

I have used BL and AL in the column names to make sure i'm referencing the right column/table when using in formulas with multiple table connections.

Thanks for the Index trick however i'm still not getting anything coming back when troubleshooting the Employees section of the formula.

[jrullan]

@Segolene_Sansom ,

Everytime I stumble upon a seemingly awkward behavior from the app, I create a separate sheet and prototype app just to troubleshoot and validate my understanding.

I strongly recommend you do the same. Create a new Sheet with your three tables, enter some dummy data in each and create a prototype app just for this purpose.

It helps when asking for help in the forum since you can share dummy data instead of your real data or application screenshots and it helps the community members to better understand the problem. You can always delete it later, but I tend to keep them for future reference.

Good luck!