AnsweredAssumed Answered

ExecuteSQL Help

Question asked by fmdataweb on Feb 26, 2018
Latest reply on Feb 27, 2018 by user19752

I've got a basic ExecuteSQL function which finds all Deliveries that match a ConNote number and a DeliveryType working as follows:


ExecuteSQL ( "SELECT " & GetSQLFieldName ( Deliveries::_kp_DeliveryID ) & " FROM Deliveries WHERE " & GetSQLFieldName ( Deliveries::_kf_ConNoteID ) & " = ? AND " & GetSQLFieldName ( Deliveries::DeliveryType ) & " = ?" ; "" ; "" ; Assets::_kf_ConNoteID ; "Incoming Shipment" )


I now need to further add another query to this based on DeliveryItems table - the Deliveries table is related to the DeliveryItems table as follows (parent child one to many):


Deliveries::kp_DeliveryID = DeliveryItems::kf_DeliveryID


I need to only show the results where the DeliveryItems::ProductID matches a value in the Assets::TrackingNumber field for any DeliveryItems child records for the matching Deliveries records. For example at the moment the ExecuteSQL function returns these values:






but I need to constain this to the DV13562 record only as that is the only Deliveries record that has a child DeliveryItems record where the ProductID matches the Assets::TrackingNumber value.


Anyone know if this is possible in ExecuteSQL?