Menu

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

0 votes

More a configuration question than an installation question.

Consider a scenario where you have three tables: sales, product, productspecification. Product contains the high-level information about a product and productspecification 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

asked Nov 15, 2017 in Installation by StuartMiddleton (280 points)
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).

1 Answer

+1 vote
 
Best answer

dbFront will automatically create cascading dropdowns when you have multi-key relationships. A multi-key relationship is any single relationship that involves multiple fields joining two tables.

e.g.

ProductTable {

  • ProductID (PK)
  • Product Name

}

ProductSpecification {

  • ProductID (PK and (FK => ProductTable))
  • ProductSpecificationID (PK)
  • Color
  • Size
  • ...

}

InvoiceTable

  • InvoiceID (PK)
  • ProductID (FK => ProductSpecification)
  • ProductSpecificationID ( (FK => ProductSpecification))
  • Quantity
  • Price

}

It is important that you create the relationship from InvoiceTable to ProductSpecification as follows:

ALTER TABLE [InvoiceTable]  
ADD CONSTRAINT [FK_InvoiceTable_to_ProductSpecification] 
FOREIGN KEY([ProductID],[ProductSpecificationID])
REFERENCES [ProductSpecification] ([ProductID],[ProductSpecificationID])

Because the fields [ProductID] and [ProductSpecificationID] are part of a single relationship, dbFront will manage them together instead of treating the fields as individual lookups.

The result of this configuration is that InvoiceTable will have two dropdowns. dbFront will realize that the ProductID dropdown should be a view on the ProductTable, and once you fill out that dropdown it will allow you to pick from the second dropdown on ProductSpecificationID which will be filtered by the previous dropdown to only show matching records.

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

answered Nov 16, 2017 by AnthonyV (14,470 points)
selected Mar 14 by AnthonyV
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.
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
 |
Powered by Question2Answer
...