ExecuteSQL Relationship Issue?

[scain101]

I need some help with ExecuteSQL.

I have a TO “Sales” that is related to Estimates, Inspections, and Proposals via ID_SALES:id_sales.

I created some simple ExecuteSQL calculations that work on Estimates and Inspections, but the same calculations will not work with the related Proposals TO. 

·         I know that the relationship is good because I can get the SUM and COUNT from Proposals with a basic calculation (not ExecuteSQL).

·         I know there is not a problem with the name on the related Proposals TO (like a space between words). I've copied and pasted the working calculations and I have started from scratch, and neither works.

This statement works:

ExecuteSQL ( "SELECT COUNT

(ID_ESTIMATES) FROM TO40_ESTIMATES

WHERE ID_SALES = ? “ ;

"" ; "" ;

TO13_sales_ESTIMATES||id_sales::id_sales )

---

This statement does not work:

ExecuteSQL ( " SELECT COUNT

(ID_PROPOSAL) FROM TO26_PROPOSALS

WHERE ID_SALES = ? ";

 "" ; "" ;

TO13_sales_PROPOSALS||id_sales::id_sales)

 

Any help would be appreciated.

SC

[Wim Decorte]

The fact that the relationships work on the FMS side is completely irrelevant once you use ExecuteSQL().  The SQL query does not use nor honor the FM relationships.  Which is a good thing because it gives all the flexibility.

When you say "this statement does not work", what is the result?  If the result is "?" then you have a syntax error in your query.  If the result is empty or a number that you don't expect then the query does something else than you expect.  Possibly because the context where you are on (the layout) when you run the query has no access to this field: TO13_sales_PROPOSALS||id_sales::id_sales

 

 

[beverly]

you can use EvaluationError() around your ExecuteSQL() to see if a number value is returned. another method to test (if you have FMPAdvanced (for using the DataViewer):

https://www.teamdf.com/blogs/unlocking-hidden-error-messages-in-the-executesql-function/

beverly

[scain101]

Thanks to both of you for that information.  After reading the referenced information and using Daniel Woods' custom function, I renamed the TO for Proposals and the statement worked.