Hi all, I need to obtain all customer details (including matching addresses in the address table) that have been modified since a certain date/time. I am trying to write a join query in Axapta to achieve this, but since there is no concept of a left join I’m not having much luck. Specifically I need to do Select all CustTable join Address records for which either custTable’s or address’s (or both) modifiedDate and modifiedTime are greated than a specified date. CustTable records without matching address records altered after this date/time should also be returns. Basically I’m looking for an Axapta SQL version of the following: SELECT custTable.Name, CustTable.recid, Address.address, Address.recid FROM CustTable left join address ON custTable.recid = address.addrrecid AND address.addrtableid = 77 AND address.addresstype = ‘Invoice’ AND ((custTable.modifiedDate >= ‘5/7/2005’) OR (address.modifiedDate > ‘5/7/2005’)) Any help would be greatly appreciated! Thanks, Matt
Hi Matt, You should try with outer join clause, something like: while select CustTable outer join address where custTable.recid == address.addrrecid && address.addrtableid == 77 && … Also, be sure to have enabled the modifiedDate property on each table (cust and address). Regards, Ciprian