So, I need to be able to use SQL to provide options in a series of pulldowns related to an employee's space/location. They are:
Pulldown #1: Building
Pulldown #2: Floor
Pullown #3: Room Number
The user would select an option in each pulldown but do them in sequential order (Building pulldown first, then Floor pulldown, then Room Number pulldown). There are two key tables, FMA1 & FMB1 that contain the data needed for the 3 pulldowns:
Table FMA1 with columns BLDGCODE , BLDGDESC:
Table FMB1 with columns BLDGCODE , FLOORCD, ROOMNM:
So, pulldown #1, the Building pulldown would be pretty easy, as they would literally just select their Building from the full list of available buildings from the FMA1 table. We would display both BLDGCODE & BLDGDESC from the FMA1 table, as some users are familiar with the alphabetical name of the building (A, B, C) but others know it more by the address (101 Main Street Building, etc.). We would ORDER by BLDGCODE. So, I expect it would just be a simple SELECT statement:
SELECT BLDGCODE, BLDGDESC
ORDER BY BLDGCODE;
Pulldown #2, the Floor Code pulldown, is where the curveball for me begins, as the options here would be dictated based on what Building they selected in Pulldown #1. If they selected BLDGCODE "A" in Pulldown #1, then they would have FLOORCD "01" and "02" as options here. If they selected BLDGCODE "B" in Pulldown #1, then they would have FLOORCD "01" , "02" & "03" as options here. And, if they selected BLDGCODE "C" in Pulldown #1, then they would have FLOORCD "01" , "02" , "03" & "04" as options here. We would ORDER BY FLOORCD and only need to display the FLOORCD in the pulldown. Table FMB1 would be used here. How would you build this one?
Pulldown #3, the Room Number pulldown, is another curveball, as you have to take into account the BLDGCODE that was selected in Pulldown #1 & the FLOORCD that was selected in Pulldown #2. For example, if they selected BLDGCODE "A" in Pulldown #1 and FLOORCODE "02" in Pulldown #2, then the only options that should be available here are ROOMNM "A2001" & "A2002". Only the ROOMNM would be visible in this pulldown and we would ORDER BY ROOMNM. Table FMB1 would be used here. How would you build this one?
Sorry, I imagine this is probably an easy one to solve but I am in the very early stages of slogging through SQL and don't know how to building a SQL code that takes into account the user selections in the prior pulldown(s).
- This topic was modified 5 months, 2 weeks ago by mm92280.