Lookup from large table / pre-filtering a field based upon another field

Question

More a configuration question than an installation question.

Consider a scenario where you have three tables: sales, product, product_specification. Product contains the high-level information about a product and product_specification contains different permutations of that product (e.g. colour, size, etc.). Product is a big table, but product_specification is a HUGH table.

You want to be able to reference a specific product specification in the sales table, but looking it up directly is difficult due to the size of the table (and product_specification does not contain information in the product table, which might help you to select the correct product).

Is there any way that DBFRONT supports making this more manageable? For example, by allowing the user to select the product in one field and a second field (product_specification) is then pre-filtered based upon what was entered into the product field?

Stuart

Comments

Morning Stuart, could you tell me what you mean by big and what is huge?

---

Okay, I'm exaggerating. I mean to say "bigger than users want to work through". In the region of 100 products (manageable) and a few thousand product_specifications (not manageable).

Answer 1

For detailed instructions on creating a cascading dropdown or lookup see: Cascading Lookups/Dropdowns

As an added note: The dropdowns in dbFront are searchable and can handle 1 million + rows.

Comments

Thanks Anthony. Based upon your response, I added a composite primary / foreign key to the database design and that did the job.

---

I'm trying to replicate this functionality with continent, country, state, for a list of sites, but can't get it working correctly. I've tried several configurations, and based on the above I currently have:
tbl.continents (seems silly, but we do some reporting by continent)
 - continent_id (PK)
 - continent

tbl.countries
 - country_id (PK)
 - continent_id (FK:continents.continent_id)

tbl.states
 - state_id (PK)
 - country_id (FK:countries.country_id)
 - state

tbl.sites
 - site_id (PK)
 - continent_id (FK:contients.continent_id)
 - country_id (FK:countries.country_id)
 - state_id (FK:states.state_id)

---

@tlheidemann, The trick is that the FK on the [sites] table should be a multifield relationship (states.country_id , states.state_id) pointing to [states] and the FK on [states] should be a multifield relationship (countries.continent_id ,countries.country_id) pointing to [countries].  Otherwise dbFront will see them all as individual lookups.