Selection based on selection

Applications Forms
#1

Have form with 2 selection boxes: CATEGORY (with primary Keyon ID(Int) and SUBCATEGORY (with the primary KEy on ID(Int). The second table also has CategoryID field.
The lin between the bables is maed through Category(ID) = Subcategory(CategoryID)

The first dropdown list works fine. I need the second dropdown list to show only records filtered by selection in a first dropdown box. Another words user selects category then subcategory.
AFAIK this is called dependable drop boxes (and it is common feature)

Unfortunately seems like lookup property does not allow to enter full SQL statements manually. SC also has no Field Events so there is no way to assign the Local Variable after the first selection is completed so the second one can be filtered based on first one. Id there any way to implement this ?

I tried to use in a second dropdown list SQL like:

SELECT ID, ProductCategoryId, SubcategoryName 
FROM edb_nPRODUCTSUBCAT
WHERE
    ProductCategoryId={Category.ID}
ORDER BY
    SubcategoryName

but I get errors

Arthur

SelectBoxIssue.jpg

#2

Is more easy than that. Use the field in where clause and check ajax fields.

There are a sample but I’m from mobile right now and I don’t find it. Give me some minutes

#3

https://www.youtube.com/watch?v=aDeUvgqksh0

From 00:54

#4

Thanks but this example is confusing. The reason is that the presenter does not show how the data tables are structured and how the relationship between the tables is build. I watched it few times but still cannot grasp their idea. Selection field (and the display field) in SC is really very confusing and not the best way to grab the lookup data. I also was not able to find suggested “From 00:54”

Here is my situation (I simplified it so it is easier to talk about the issue)

TABLES

  1. Prodcuts
    UID(Int) PK
    ProductName(vchar)
    Category(vchar)
    Subcategory(vchar)

  2. Category
    CategoryId(Int) PK
    CategoryName

  3. Subcategory
    SubcategoryId(Inte) PK
    CategoryID
    SubcategoryName

Table 2 and 3 are linked by CategoryId

I wan to select the CATEGORY first (the Category field in Products is filled), then base on this field I want to list in the dropdown subcategories. I suspect that example App stored both columns in the same table so it is completely different scenario. Notice that I’m storing strings (CatergoryName - not CategoryId) and SubcategoryName (not SubcategoryId)

Art

#5

OK, I got it working now but it is not exactly the way I want it. I have a 2 selection dropdown lists. Second is basewd on first one but,…
In order to make it working I had to add 2 columns to the tables Products

  1. CatID(INT) (for category)
    2.SubcatId(INT) (for subcategory)

Instead of storing Category name I store CatId (numeral). Now in a grid I need to select Category (before the records are fetched into the grid). Of course the Category field is empty, or I can only select records based on CatID (not the Category). What would be the best solution to this ?