complex lookup

Noname shared this question 3 years ago
Answered

i have 3 data masters:


1. customers - contains User ID, First Name, Last Name etc


2. orders - contains User ID, Order Number etc


3. Carts - contains Order Number, Item Name, Price etc


I need to create a lookup = user enters first or last name and I show data grid from Carts master that shows all items user purchased. So i need to locate User ID -> search Order Numbers by user ID, then lookup all matching Items by Order Numbers.


Is this even doable?

Comments (1)

photo
1

One thing we've done to help with data master joins is to denormalize the data masters and to use spreadsheets to manage the data. While not ideal, it makes the creation of the wireframe a lot easier. In the end these are wireframes not fully functional apps, so (at least for me) it is not critical to have everything fully normalized.


In your case for the denormalization you would specifically put the name fields in the orders DM as well. This way you don't need to join the customers DM and the orders DM when filtering based on name. It's not clear to me what the relationship between order and cart is, but if you can put the cart information in the order DM as well it would be very very easy to do.


It really helps to store all of the prototype data in a spreadsheet and then you can import/export whenever you need to make changes. You can use vLookup in either excel or Google docs to make the data denormalization easy.


If you really wanted to keep everything completely normalized then you can use the 'has' statement with a filter to effectively join tables. It is a bit tricky to do, but it works.