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 ( [
"SELECT ''|| \"Actual Date\"
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?