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:

 

DV13167

DV13347

DV13562

 

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?

Outcomes