Lookup from multiple tables

#1

Hi

I have a few tables that list a number of services/products. I would love to put all the services/products into one table but each service/product has different requirements and therefor different columns.

I am trying to build a table that will help me price up jobs. I would like to use a dropdown list to take services/products from different tables which will add the cost based on the selection, I’ll then continue to add a row for each service and finally calculate the price.

I can do this based on one table but I can’t work out how to get data from multiple tables.

How to dynamically choose table to lookup from?
#2

Would you be able to share a sample version of your sheet (i.e. nothing confidential in it)? I am trying to make sure I understand your use case.

#3

Here’s a dummy table i’ve setup to give you an idea of what I’m trying to do.

I’ve narrowed down the columns to keep it simple and I have manually filled out table 1 for the moment but basically I am trying have a dropdown selection in table 1 where I can select one of the products or services from table 2 or table 3, it will then fill out the supplier and price based on the chosen selection.

#4

Would something like this help?

You specify a product or service first which then filters the supplier dropdown accordingly. By selecting a Supplier it then filters the Item column accordingly. There are many issues that would need to be solved!

#5

Hi

I’m just playing about with it now but I think that could work nicely.

Thanks

1 Like
#6

Thanks @Nick_Milner :handshake:

A nice way, how you made this design, to be honest for me it seems to be the hardest part.

:globe_with_meridians:
If you have any recommendation where to learn about “schema design” with practical samples / user cases it would be appriciated

best regards,
//JP

#7

It’s been a long time since I learned this stuff - it mostly comes through long experience of making and learning from mistakes! :wink: Maybe someone who’s more recently out of education could help with a go-to book or site for schema design. My university days are a long way behind me at this point.

To be frank, I’m actually not a fan of my own design here! For example, if you go back and change one of the key fields in the Sales table you can break the entire row. E.g. if you change the Type from Product to Service all of a sudden the Supplier and Item fields can become invalid and the calculation throws an exception - or even worse, they can remain valid but wrong! These are known as “update anomalies” and demonstrate that the design “could be improved” (if I may say so myself!)

It works on the surface, and it might be ok for certain small implementations, but I wouldn’t open a system like this up to customers.

It would probably be better to have a single “offering” table with all products and services and their common attributes, then the Sales table becomes much simpler and is protected from update anomalies. You would then have separate Service and Product tables joined to the Offering table to cover attributes specific to each type.

1 Like
#8

Hi @Nick_Milner

I’ve been playing about with your table and trying to incorporate it into my own format but I can’t make it work if the source table has more than 1 row.

For instance, when you select your options on the quick quote table, if you select ‘design’ for the type and supplier it allows you to add the brand, I’ve noticed that if the ‘design’ table has another row it won’t work.

If you try doing the same for installation you won’t be able to select a brand, this is because the ‘brand’ table has more than 1 row.

I can’t understand why it won’t work with more than 1 row?

#9

The problem is the way you’re addressing Supplier in your filter formula in the Brand column.

You’re saying:

Switch(thisRow.Type, "Design",[Design Fees].Filter([Design Fees].Supplier=thisRow.Supplier).Brand, "Installation",[Installation Fees].Filter([Installation Fees].Supplier=thisRow.Supplier).Brand, "Media",Media.Filter(Media.Supplier=thisRow.Supplier).Brand, "Sheet Material",[Sheet Material].Filter([Sheet Material].Supplier=thisRow.Supplier).Brand)

…and it should be…

Switch(thisRow.Type, "Design",[Design Fees].Filter(Supplier=thisRow.Supplier).Brand, "Installation",[Installation Fees].Filter(Supplier=thisRow.Supplier).Brand, "Media",Media.Filter(Supplier=thisRow.Supplier).Brand, "Sheet Material",[Sheet Material].Filter(Supplier=thisRow.Supplier).Brand)

Notice that I’m saying “…Filter(Supplier…” whereas you’re saying “…Filter([TABLE].Supplier…” It’s the difference between looking for a particular supplier or looking for all the suppliers.

/edit - here’s a fixed version - I’ve fixed this specific mistake, not completed the rest of the job. :wink:

#10

Thank you @Nick_Milner

That works a treat.

#11

Before you go too much further I wanted to say that I’m going to revisit this design. It’s a bit nasty when considering two domains (products and services) but it becomes positively awful when you extend it to six! :wink:

#12

@Nick_Milner

After your last comment, I started to play about with it a little and I think I’ve come up with a new way.

 SwitchIf(thisRow.[Service Type] = [Design Fees 2].[Service Type], [Design Fees 2].[Service], thisRow.[Service Type] = [Vinyl Fees].[Service Type], [Vinyl Fees].[Service])

This works but like before, if there are 2 rows in the source table, it won’t work.

I’ve tried to look at your previous code but I can’t figure it out.

#13

Just figured it out I think

SwitchIf(thisRow.[Service Type] = [Design Fees 2].[Service Type], [Design Fees 2].Service, thisRow.[Service Type].unique() = [Vinyl Fees].[Service Type].unique(), [Vinyl Fees].Service)

#14

Still playing about with this and I cannot get a formula I think works perfectly.

I’m now using:

SwitchIf(thisRow.Service = "Design", lookup(Table, [Column 1], thisRow.Service).[Column 2],thisRow.Service = "Installation", lookup(Table, [Column 1], thisRow.Service).[Column 2], thisRow.Service = "Manufacture", lookup([Application Types],Service, thisRow.Service).Application)

But I’d rather not have to manually add the services in as new services can be added over time.