Creating Junction Table

[alconnor]

Hi Everyone, Thanks for your help in advance and ill try articulate this as best i can.

Im trying to create a junction table from 2 tables.

One being a table that contains "new leads" exported from a CRM. It contains a persons name, contact details  product and product reference, etc

Second table is a list of vendors/suppliers that have that particular product with product reference. 

The data is not entirely clean (structured) on the "new leads" table as the leads contain messages/text containing the reference number within it. 

First question: Is it possible to group and filter the new lead table with the vendor list in a way that would show the vendors that could supply to the following lead, using the product reference.

Example:

Lead Column: New Lead - Hi i am interested in "Item 123123-001" 

Vendor Column: Reference: 123123-001

Would and could i need to use a formula to identify the reference within the lead? and then Could i link and group the lead with the vendors who have that reference?

Thanks again

[TheTimeSavingCo]

re: First question: Is it possible to group and filter the new lead table with the vendor list in a way that would show the vendors that could supply to the following lead, using the product reference.

Yeap, you'd use the junction table you mentioned and so you'd end up with:
1. Leads table
2. Products table where each record is a single product and can be linked to multiple vendors.  This will be linked to the Leads table as well
3. Vendors table

And so you'd make a lookup field in Leads to pull over the linked Vendors from Products
---
re: Would and could i need to use a formula to identify the reference within the lead?

Yeap, you'd need a formula field to pull the reference out of that lead.  If it's completely free form text field this seems difficult to do though.  Is there any pattern to the text that we can use to extract the reference?