AnsweredAssumed Answered

SQL calculation fields of Child records (for Excel export)

Question asked by jbryan on Aug 25, 2017
Latest reply on Aug 25, 2017 by jbryan

I want to export all fields from one table to Excel for reporting purposes. But I need field values from specific child records as well. A friend suggested I create additional fields in the parent record that pull values from the child records with SQL statements.

 

Child Table: Milestone

Child Record: Asset Request (ID = 50) NOTE: only one record of this type can be related to the parent

Child Fields: Forecast Date, Scheduled Date, Actual Date

Parent Table: Job

Parent Fields: Asset Request Forecast, Asset Request Scheduled, Asset Request Actual

 

Here's the SQL statement that does not work consistently:

Let ( [

@query = 

"SELECT ''|| \"Actual Date\"

  FROM \"Milestone\"

WHERE \"ID Job\" = ? and \"ID Milestone Type\" = ?

"];

 

ExecuteSQL ( @query; ""; ""; Job::ID ; 50 )

)

 

Any suggestions why this does not work consistently? Sometimes I get the value from the Milestone field (as I hope), sometimes a "?", sometimes no value (when there should be a value). Can someone guide me on my SQL? Or recommend another method to accomplish the same thing?

Outcomes