7 Replies Latest reply on Mar 26, 2012 3:07 PM by MattLeach

    Two Level Scripted Find

    MattLeach

      I have a database with two related tables. I would like to script a find, using a field from each table in order to do an export

       

       

      I have a clients table and an email table. This setup allows each record to have multiple email addresses. Each email address has a status field which is set to either Yes or No. The purpose of this field is to determine whether or not to include this address in an export.

       

      There is a portal in the client table that lists all related email addresses.

       

      The purpose of the script is to export all email addresses marked "yes" where the client's swpd2 date is greater than today.

       

      I'm able to script the first find without issue, it's just getting the second part of the find where i'm having trouble.

       

       

      New Window [Name: "Client"; Left: 2000] //reason for new window is that script is being initiated from un-related table
       Go to Layout ["TechSupport" (TechSupport)]
       Enter Find Mode []
       Set Field [TechSupport::swpd2; ">" & Get ( CurrentDate )]
       Perform Find[]
       Enter Find Mode []
       Set Field [Email::Status; "Yes"]
       Contrain Found Set []
       Export Records [parameters]
      

       

      This script works in that it shows only clients who's paid 2 date is greater than today and shows emails with "yes" as status, but emails listed as "no" are also included. Not sure how to omit those during the script.

       

      Any help would be appreciated.

       

      Thanks

        • 1. Re: Two Level Scripted Find
          ian.moree

          YOu can do that via relationship or a script check

          If ( Paid2Date  > Today ) and emailshow = "yes" )

           

          Go to Layout, etc,etc

           

          Or Create a relationship TO with paid2date > today and Id = Id

          then use that table oCcurence to search for your data.

           

          I think that would be a good start at least.

          • 2. Re: Two Level Scripted Find
            MattLeach

            Could you elaborate on your suggestions? Not sure i follow. Thanks

            • 3. Re: Two Level Scripted Find
              ian.moree

              try this:

              Enter Find Mode [ ]
              Set Variable [ $searchString; Value:" > " & Get( CurrentDate) ]

              Set Field [ two_level::Paid2Date; $searchString ]

              Set Field [ two_level::EmailsStatus; "yes" ]

              Perform Find [ ]

               

              I have tried and works for me no changes to RG ( relational graph)

               

              try it of course using your tableNames

               


               

              Message was edited by: ian.moree

              1 of 1 people found this helpful
              • 4. Re: Two Level Scripted Find
                ian.moree

                You need to make code cleaner rather than having so much ">" ,etc in your scripts, try to

                put them in variables like

                $searchString.one

                $searchString.two or searchOne, searchTwo

                 

                helps with readability ..

                • 5. Re: Two Level Scripted Find
                  comment

                  MattLeach wrote:

                   

                  I have a clients table and an email table.

                  ...

                   

                  The purpose of the script is to export all email addresses marked "yes" where the client's swpd2 date is greater than today.

                   

                  Your script searches a table named TechSupport. If you want to find and export  email addresses, you should search in the email table:

                   

                  Go to Layout [ Emails ]

                  Enter Find Mode []

                  Set Field [ Email::Status ; "Yes" ]

                  Set Field [ Clients::swpd2; ">" & Get ( CurrentDate ) ]

                  Perform Find[]

                  • 6. Re: Two Level Scripted Find
                    MattLeach

                    The problem is that if i have a client record with 2 email addresses, one marked as yes, one marked as no, running the script will still allow an email address that is marked as no to be exported as long as there is another email address marked as yes linked to the same record.

                    • 7. Re: Two Level Scripted Find
                      MattLeach

                      That literally just clicked in my head as i was typing my last response and was in the middle of changing the script to the email layout when i read your post. That took care of it. Thanks to the both of you for your assistance.