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 has the ability to create cascading dropdowns when you have multi-key relationships.

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

}

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 (13,020 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.
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
...