Need a tweak for the City field

[21st Century Man]

Howdy, all;

I think there's something amiss with the MAIN::CityIDsMultiKey__lct field's calculation:

 

Case (

// If Country, State and County have not been selected, show ID's for all City records

IsEmpty ( id_Country ) and IsEmpty ( id_State ) and IsEmpty ( id_County ) ;

ExecuteSQL ( "SELECT id FROM \"ESQL_Cities|Sel\" " ; "" ; ¶ ) ;

 

// If a State has been entered but not a County, show all City ID's for the State

IsEmpty ( id_County ) ;

ExecuteSQL ( "SELECT \"id\" FROM \"ESQL_Cities|Sel\"  

INNER JOIN \"ESQL_Counties|Sel\" ON \"ESQL_Cities|Sel\".\"id_County\" = \"ESQL_Counties|Sel\".\"id\"

WHERE \"ESQL_Counties|Sel\".\"id_State\" = ? " ; "" ; ¶ ; id_State ) ;

 

//Else: Show all ID's for the selected County

ExecuteSQL ( "SELECT id FROM \"ESQL_Cities|Sel\"

WHERE id_County = ?" ; "" ; ¶ ; id_County )

     )

 

When I choose a State in the Main Data Entry layout, I should be able to see the cities (within the chosen state) but I get the dreaded <No Values Defined> message in the MAIN::id_City field. The only help I can offer is that when using the Data Viewer to examine the calculation, it has the Result: "The column named "id" appears in more than one table in the column reference's scope."

Admittedly, I have about this > < much SQL knowledge so I don't know what the message is telling me that I need to fix in the calculation; what needs to be fixed in the calc?

Cheers,

Rich

Hierarchical CVL - ESQL - 4 Level.fmp12Fetching info...

[comment]

  On 4/10/2025 at 7:16 PM, 21st Century Man said:

"The column named "id" appears in more than one table in the column reference's scope."

Expand  

Well, yes - if you are joining 2 tables, and both tables have an id field, then you must specify from which table the values of id should be fetched, e.g.

SELECT \"ESQL_Cities|Sel\".id

instead of just:

SELECT id 

 

As an aside, I doubt ExecuteSQL() is a good tool to use here. Do a search for "dwindling value list" to learn some FM native methods.

 

[21st Century Man]

Thanks, and good advice. On the surface, the schema looks simpler using ESQL but at least with more T.O.'s--using the "native" method--it would make it much more understandable (and less breakable.)