2 Replies Latest reply on Oct 10, 2014 11:34 AM by AdamReed

    find request in script not working

    AdamReed

      Title

      find request in script not working

      Post

      I'm relatively new to scripting...

      I have three tables - a payment table (with payment id), a disbursement table (with disbursement id), and a table that links payments and disbursements (with payment ids and disbursement ids).

      I'd like to find those records in the payment table which do not have an associated disbursement id in the link table.  My guess is that I need to copy the disbursement id into the payment table (so it exists in all three tables) -- is that right, or is there an easy way to find records which do not have a related record in another table?  Or have I got the wrong idea here?

      Thanks,

      Adam

        • 1. Re: find request in script not working
          philmodjunk

          On your Payment layout:

          Enter Find Mode[]---> clear the pause check box
          Set Field [Payment_Disbursement::_fkDisbursementID ; "*" ] ---> finds all payment records that have a join record with a disbursementID
          Omit Records----> changes this to an "omit" request that omits all records where the related join has a disbursementID
          Set Error Capture [on]
          Perform find []

          But I see a problem here. You have set up a many to may relationship so there could be many records in your join table. This script will find all payment records that either have no join table records or that have at least one join table record with an empty disbursementID record.

          That may or may not be what you actually want.

          The above script uses methods found in this thread of scripted find examples: Scripted Find Examples

          • 2. Re: find request in script not working
            AdamReed

            Thank you, and thank you for the link.  Very helpful!

            I do have this set up as a many-to-many, and the results you describe are exactly what I want.  Thanks again.