cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

creating custom column from multiple tables with condition

I am trying to create custom column, trying to populate vlaues from two diffrent tables based on condition.

 

Table 1   to Table 2  relations ship created(active) on workflowid

 

Table     to Table 3  relation ship created but not active on workflow id

 

Table 1

Workflowid MonitorNew Colum
1true  
2FALSE 
3true  
4FALSE 

 

Table 2

 

Workflowid MP ID
112
314

 

Table 3

 

Workflowid MP ID
213
415

 

Expecting out put 

 

Workflowid MP IDNew Colum
1true 12
2FALSE13
3true 14
4FALSE15
2 ACCEPTED SOLUTIONS

Accepted Solutions
dilumd Established Member
Established Member

Re: creating custom column from multiple tables with condition

how about appending your table 2 and 3 together first and then do a relation between appended table and table no1?

Super User
Super User

Re: creating custom column from multiple tables with condition

Hi,

 

The best solution has been suggested to you by @dilumd.  If you do not wish to follow that solution, here is a calculated formula you can write in Table1

 

=if(ISBLANK(RELATED(Table2[MD ID])),LOOKUPVALUE(Table3[MD ID],Table3[Workflowid],[Workflowid]),RELATED(Table2[MD ID]))

For this formula to work, there does not need to be any relationship between Table1 and Table3 - the LOOKUPVALUE() function does not require any relationship - active or not.

 

Hope this helps.

2 REPLIES 2
dilumd Established Member
Established Member

Re: creating custom column from multiple tables with condition

how about appending your table 2 and 3 together first and then do a relation between appended table and table no1?

Super User
Super User

Re: creating custom column from multiple tables with condition

Hi,

 

The best solution has been suggested to you by @dilumd.  If you do not wish to follow that solution, here is a calculated formula you can write in Table1

 

=if(ISBLANK(RELATED(Table2[MD ID])),LOOKUPVALUE(Table3[MD ID],Table3[Workflowid],[Workflowid]),RELATED(Table2[MD ID]))

For this formula to work, there does not need to be any relationship between Table1 and Table3 - the LOOKUPVALUE() function does not require any relationship - active or not.

 

Hope this helps.