Left Join on multiple fields to view consolidated data (but not pivot)

1

This is my use-case:

I have 2 tables, one with leads and one with lead-deals similar to this:

Deals (raw data)

DealId LeadId Request RequestDate ExpectedAmount
1 alice I need a new car 23-jan 15.000 EUR
2 bob I want a new PC 28-jan 1.000 EUR
3 charlie I’d like some flowers 2-feb 300 EUR
4 alice I want a new home 5-may 80.000 EUR
5 bob Please some flowers 15-jun 300 EUR
6 alice I’d like to go in parachute, for 5 friends 20-jul 1.500 EUR

Leads (raw data)

LeadId Name LastName Phone Email Address Country
alice Alice Abraska 666 66 66 66 alice@some.example.com Some Street, 66 Spain
bob Bob Bobodoro 777 77 77 77 bob@company.example.com New Place, 77 France
charlie Charlie Chamuscado 888 88 88 88 charle@nice.example.com Potato Avenue, 88 Italy

I would like a “page” that shows this:

Deals (in page)

Lead Country Phone Email Request RequestDate
Alice Abraska Spain 666 66 66 66 alice@some.example.com I need a new car 23-jan
Bob Bobodoro France 777 77 77 77 bob@company.example.com I want a new PC 28-jan
Charlie Chamuscado Italy 888 88 88 88 charle@nice.example.com I’d like some flowers 2-feb
Alice Abraska Spain 666 66 66 66 alice@some.example.com I want a new home 5-may
Bob Bobodoro France 777 77 77 77 bob@company.example.com Please some flowers 15-jun
Alice Abraska Spain 666 66 66 66 alice@some.example.com I’d like to go in parachute, for 5 friends 20-jul

In short: A classic LEFT JOIN Leads ON Deals.LeadId == Leads.LeadId

How can I do this?

2

Hi @Xavi_Montero have you considered using 2 table layout - Deals on the top, then Leads on the bottom connected using SELECT BY which is linked by a Reference Column from Deals to Leads - this is very similar to LEFT join. Just that you will not see all that data in one table.

If you definitely want to see all the data in one table, then once you set up the reference column, you can use the Creator Panel (or formulas) to add the columns from Leads to Deal table view. I hope you can understand what I am saying. Please let me know if you need further help.