8 Replies Latest reply on Sep 5, 2011 2:51 AM by aikiko

    Constrained finds using different layouts

    aikiko

      Title

      Constrained finds using different layouts

      Post

      Dear All,

      I am a newbie and have a tricky 3 "stage" find question

      I have 2 layouts: layout A which is a census and layout B which is a detailed household survey. These 2 layouts represent different tables which are linked by ID numbers of individuals.

      I search for the number of "women"in layout A.

      Out of these "women" in layout A, I want to find how many children does this woman live with. This listing of children is in layout B. nb: the mother is linked to the child via a "household number".

      Then I want to find the list of all these "children".

      I can do a constrained find, but only in the same layout, but not when I change layouts.

      Any assistance will be of tremendous help for my health survey.

      Thank you very much in advance.

      Aikiko

      FMP 10Adv

      Mac OSX

        • 1. Re: Constrained finds using different layouts
          Kays

          heyy aikiko,

          in the table "children", it must be like a field that refers to table "mother", like a joint between ( children::id_mother ===== mother::id)

          K|Z

          • 2. Re: Constrained finds using different layouts
            aikiko

            Dear KIZ,

            Thank you, but the two tables are already joined by the same "ID number". Perhaps I should elaborate a bit further.

            I would like to do a Find in one layout/table, then use the ID numbers from this search to Find related records in another layout/table.

            I want to find all women in layout A that will give me a list of all records with women in it including their ID numbers. I then want to use these ID numbers to find matching children in layout B, however the children are only linked to these women through a household number, i.e. the children do not have an ID number. All women in layout A can be found in layout B.

            Layout A = list of selected men and women who each with a unique ID number

            Layout B = list of all people by household (men, women & children) - men and women have ID numbers, but not children. But each individual has a household number that groups households together. So there can be a household of 4 people: man, woman, 2 children, but only the man and woman would have a unique ID number, but all 4 people would have the same household number.

            I hope that makes sense.

            Thanks for any help once again.

            Aikiko

            • 3. Re: Constrained finds using different layouts
              Kays

              dear Aikiko,

              this is what i understood from ur last comment : find woman (men ) on layout A, and fin household group related to that woman in layout B?!! 

              ---------

              go to layout A

              set error capture (on)

              perform find ( blah blah blah)

              go to first record

              loop

              set variable ($var; A::id_number )

              go to layout B

              set error capture (on)

              perform find (A::id_number = $var) // cuz tables A,B related

              set variable ( $var2 = B::houshold_number ) // get the household_number of the woman ( man)

              perform find (B::household_number = $$var2 )

              // here u got the the related data to A::id_number, so you add and operations you want, and if this is not what u lookin for, plz some more explanation :)) 

              go to layout (A)

              go to nex record

              end loop

              -----------------

               

              K|Z

               

              • 4. Re: Constrained finds using different layouts
                philmodjunk

                If you find a set of records for Women on layout 1 and then want to see the list of children in a related table displayed on layout 2. You can use Go To Related Recors and use Show Only Related Records, match found set to get a corresponding list of women.

                An even simpler approach, is to perform the find on layout 2 in the first place. Since the tables are related, you can specify criteria in the related parent table as part of the find that brings up records in the child table. If this is a scripted find, you do not need to place fields from the parent record on the layout. If this is a Manual find, you will need to do so in order for the user to enter/select criteria in the fields from the related table.

                • 5. Re: Constrained finds using different layouts
                  aikiko

                  Dear Kays and Phil,

                  Thank you very much for your responses and apologies for not replying earlier (just back from a 3 month holiday!).

                  I've been trying both of your suggestions with little progress. To recap, this is what I am trying to do:

                  1) Layout A is a list of women who have had medical test

                  2) Layout B is a list of all people of all ages who live in X number of households

                  3) Find the number of women in layout A that meet certain criteria (I can do this) e.g. 40 out of 200 women

                  4) Find the number of children who live with these 40 women (from layout A) in layout B (I cannot do this via a script)

                  5) All women have an "ID number", but none of the children have an ID number.

                  6) The children are only linked to the women by a "household number" in Layout B.

                  7) "ID number" is the only criteria in the relationship linking layout A and B. 

                  8) So I think the overall goal appears to be a 2 step find command: Perform a find in Layout A, use these results to perform a find in Layout B.

                  Let me start with Phil's suggestions first:

                  Suggestion 1) Thanks - nice to learn about "Go to related records", and yes it gives me a corresponding list of women (and their ID numbers and their household numbers), but I would like to use this list of women's household numbers to find the children in layout B. When I do this manually, for 40 women I get about 130 children). 

                  Suggestion 2) I am not sure how to do a second Perform Find based on the first Perform Find by using a script

                  Kays' suggestion:

                  I think that this is getting closer to what is required. Thank you as well, but when I type in the script exactly as you have written it appears to be in an eternal loop that requires me to abort the script. This still happens when I type "perform find (B::household_number = $$var2 )" with one $ sign as well (Is this a typo?). I've also tried to break down the script to see what other results I could get, but to little effect most probably due to knowing what I am doing.

                  Once again, thank you very much for any assistance you can provide. I've spent 6 hours trying to play around with this today and have had to come back to those who know best.

                  Aikiko

                  • 6. Re: Constrained finds using different layouts
                    Kays

                    hi again aikiko,

                    Try the command "extend find" (or something like that) it may be helpful.

                    u can loop the table A, take the household id from the women's records, go to layout B and make an extend find. u will get the set of all the finds at the end of the script.

                    i think that should make the trick :)

                    K|Z 

                     

                    • 7. Re: Constrained finds using different layouts
                      aikiko

                      Dear Kays,

                      Thank you - I think I am nearly there......very close now, but still not close enough.

                      I can actually solve this challenge if I do it manually now, by right-clicking on the household number and pressing "extend found set" in layout B. However this defeats the purpose of using FMP scripts.

                      I think there are two ways to solve this using a script:

                      Number 1:

                      Enter Find mode

                      Perform Find

                      Go to Related Record

                      Extend found set [table:field:household_number=*]

                      The first three lines work perfectly, but my problem here is that I cannot get "extend found set" to create a list based upon the Household numbers I found in my first Perform Find i.e. I am not sure what to place where the * is.

                      Number 2 (your loop suggestion):

                      go to layout A

                      set error capture (on)

                      perform find 

                      go to first record

                      loop

                      set variable ($var; A::id_number )

                      go to layout B

                      set error capture (on)

                      perform find (A::id_number = $var) 

                      set variable ( $var2 = B::household_number )

                      perform find (B::household_number = $var2 )

                      go to layout (A)

                      go to next record

                      end loop

                      I do not know where to place the "extend found set" line, and what other lines to delete. Also when I use the script debugger, it keeps saying at this line: perform find (B::household_number = $var2), that there are no records to find.

                      Thanks for any assistance once again.

                      Kind regards

                      Aikiko

                      • 8. Re: Constrained finds using different layouts
                        aikiko

                        In case anyone else has a similar problem, I managed to find a solution on a different forum:

                        http://fmforums.com/forum/topic/79849-how-to-create-a-2-step-find-extend-find/

                        Perhaps I did not explain as well on this one. Alas thanks to Kays and PhilModJunk for their initial input.