Linking of sharepoint lists cross-referenced

[fietstasss]

Hi all, 

I'm all new to sharepoint and trying to create a site/portal which will be used as some kind of database for products, vendors, and possibly later also storage-locations within our company. 
To shape this, i'd like to take the following approach: 
First, a List about products used in our company, in this example a screwdriver is listed: 
PRODUCTLIST

Product	ProductNumber internal	Manufacturer	Manufacturer ProductNumber	Notes
PZ2 Screwdriver	EX123	Gedore	6684190	Good screwdriver for this or that Lorum Ipsum

 
Than, a seperate list with all vendors used/known within our company: 
VENDORLIST

Vendor	City	Email	Delivery-agreements	Contact Person
ABC-Toolmaster.com	Stockholm	ABC@Example.com	Delivery always carriage-paid	Elvis Presley
XYZ-Tools.com	New York	XYZ@example.com	Delivery always within 24h	Kelly Monroe
SuperOfficeSupplies.com	Los Angeles	STU@Example.com	Delivery always on Friday	John Doe

Now the tricky part, i'd like to make a connection between these two lists. Example situation: 
Our purchaser sees need to buy (internal)product number EX123 (which is a Gedore 6684190 screwdriver). For this, he looks at the productlist and then should be able to see that this product is available either from vendor ABC-Toolmaster.com, with their price and vendor-productnumber, but also from vendor XYZ-Tools.com with their respective price and vendor-productnumber.  

That said, i'd like to make a reference containing data from both tables, and some extra info (vendor-productnumber, price, etc.). Offcourse this info should be entered when creating a new product. 

I'm not really getting how to implement this without having double-data or limited amount of vendors per product, hoping anyone over here could point me in the right direction on how to accomplish this. 


Thanks in advance!

[#2]

I'm still struggling with this, anyone who could give some clue on how to achieve this linking? 

[#3]

@fietstasss I suggest doing this with Power Apps.

Rob
Los Gallardos
Intranet, SharePoint, Website and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#4]

Could you clarify on this? How would PowerApps help me in saving these multiple vendor+uniqueInfo per product?
Thanks!

[#5]

@fietstasss because you can have multiple data sources - the 2 SharePoint lists in your scenario. Then with a gallery or data table you can select the item from the first list and filter the second list by your selection. Much like Access used to do.

Rob
Los Gallardos
Intranet, SharePoint, Website and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#6]

@RobElliott 

Thanks for your reply. 

I get your point, but the issue is that we need some kind of 3rd data/info to be stored. 

At the 'crossing point' between a Vendor and Product, we want to store info like the Vendor-specific retail-id/purchase number, and perhaps also the price and URL they use for this product. 

This means that 1 product can have multiple vendors, with each their own price, retail-id and URL/weblink. 

Plain said, i should make a new list for each product which then lists all combinations applicable for that product, containing the available vendors with each their own dataset (containing retail-id, price, URL). 

Another option would be to add a fixed number of columns to each product in the 'ProductList' to make it possible to list each vendor and their dataset, but this would result in a limited number of vendors per product. 

Both don't seem a valid option, so there's the gap i'm looking for to fill in. 
Hope this makes the option a bit clearer, any help is appreciated!

[#7]

Thanks for sharing this info! It really helped me out