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?