I am trying to write a calculated field to pull a custom field created in 3-6 that we plan to use when a customer wants invoices/statements emailed.  My current formula works, but repeats the result (I believe for the number of jobs created under this client number?)  Does anyone have a suggestion that works in Sage100 Contractor 2017 (v 20.5)?

My custom fields are designated as follows:

recc_u.udf001 = the client # (I created this because otherwise I did not know how to join the 3-6 custom fields table to the 3-6 table

recc_u.udf002 = the client's email address

[SELECT recc_u.udf002 FROM acrinv, actrec, reccln, recc_u, acrinv INNER JOIN actrec ON acrinv.jobnum = actrec.recnum, actrec INNER JOIN reccln ON actrec.clnnum =reccln.recnum, reccln INNER JOIN recc_u ON reccln.recnum = recc_u.udf001 WHERE acrinv.jobnum ={actrec.recnum} AND actrec.clnnum = {reccln.recnum} AND recc_u.udf001 ={reccln.recnum} ]

Thank you!!