6 Replies Latest reply on Jan 2, 2014 8:13 PM by vlight

    Find 2 criteria, where the 2nd is dependent on the 1st



      Find 2 criteria, where the 2nd is dependent on the 1st


           I'm building a scripted Report to be exported to Word.  The script includes the find (that i'm having difficulty with).  

           I have a Person table related to a Notes table.  Notes can be of 3 types: C, F, NS.  (Conversations, FileNote, NextSteps)

           My Report is basically to show who i've had conversations with during a given time frame (e.g. the current month) …  Which people have i talked to (recorded as a C note), And, if any of thosepeople (and ONLY those people) also have any open Next Steps (NS notes), i want to include those in the Report.  

           Find#1 — I'm able to easily find the people with C-notes during the time period.   Find#2 — However, i don't know how to THEN limit the next find (for open NS notes) to only the people who were found in Find#1.  Suggestions??  

           (working in FM 11 on Mac OS 10.8)

        • 1. Re: Find 2 criteria, where the 2nd is dependent on the 1st

               After performing find #1, return to find mode, specify the cirteria for Open NS notes and constrain the found set--an option you can select from the Requests menu.

               This can also be scripted.

          • 2. Re: Find 2 criteria, where the 2nd is dependent on the 1st

                 I'm still missing the mark.  In my example, i know ultimately that i am looking to end up with 8 records — 4 people — for each person, i've had 1 conversation during the time period (gives me the 4 C notes) (this is the most important find), and then for each of these same people, i happen to have created 1 NextStep each (gives me the 4 NS notes) — so 4 people, each with 2 notes (1 C note and 1 NS note)

                 When i do the first Find, i correctly receive 4 records — 4 people with whom i've had a conversation during the designated time period.  So i want to expand that found set to include not only those 4 C-note records but also include the 4 NS-notes for those same 4 people.  The only constraining that i would want to have is to constrain it to the 4 people found in the first Find. From that point going forward, i want to extend it to include any open NS-notes that those particular people might also have.

                 I'm doing the Find on a layout based on Notes.

            • 3. Re: Find 2 criteria, where the 2nd is dependent on the 1st

                   So you want to find Notes records, not People records--that's a key difference here. wink

                   Perform the first find on your People Layout to find all people with at least one C Note record. Then use Go to Related Records to pull up all Notes records for those People, be sure to use the "match found set" option. Then enter find mode and use two find requests in the same find--specify the C Note criteria in one and the Open note criteria in the other, then Constrain the found set to omit all other note records except these two types.

                   You may find this thread helpful in understanding some of the issues involved when you perform a find that specifies criteria in fields of a related table: searches on related table

              • 4. Re: Find 2 criteria, where the 2nd is dependent on the 1st

                     I appreciate your help so much — and i've been working at it all day but still can't make it work.  I am uploading a jpg of the main script used in generating the Report.  As you can see, it starts out by presenting the user with a custom dialog where i am having them type in the dates they want to use for the Report. Their input here populates a global field called gFindDate in the People table. A typical entry would be 11/*/13, to use later in finding all conversations "during November 2013,"  and then including any Next Steps that exist for those people only.

                     So it starts out in a layout in the People table, searching for all people with whom i've had Conversations (via a portal to the Notes table).  Then i do the GTRR per your notes, which then takes me to one of the layouts in the Notes table, and puts me in Find mode.   There i do a set field, applying the gFindDate field to a calculation field "c_ConversDate."  And then i do the "2 Request Find" that you mentioned, and then Perform Find. And my Constrain found set step doesn't alter it at all.  In the end, i get about 64 records rather than the 8 that i know i should get based on an 11/*/13 time frame.   

                     Is there anything else i can give you to help you determine where i might be going wrong?  Let me know if you don't receive the .jpg i'm uploading. Thanks for your help!!

                • 5. Re: Find 2 criteria, where the 2nd is dependent on the 1st

                       The perform find step after the script has taken you to the notes layout should not be there. You shouldn't need that find and it might be affecting the results that you are getting here.

                       Other things that you can check:

                       Temporarily put in a pause script step after the GTRR step and check to see if the correct set of records is being found. You should have all the Notes records linked to one of the people who have a conversation note in the specified month. The Constrain found set part is then intended to omit the extra "notes" records that aren't the ones you want to see here.

                  • 6. Re: Find 2 criteria, where the 2nd is dependent on the 1st

                         I'm very grateful for your help.  I struggled with your responses and suggestions, struggling mainly, i'm sure, because i must not have clearly communicated what i had and where i was trying to go.  In the end, though, i've been successful in getting the Report to give me what i want to see and nothing else. I'm attaching a marked up copy of the final script, in case you are in interested.  

                         Thanks so much for working with me!  The experience has offered me some valuable insights.  Happy New Year!