10 Replies Latest reply on Apr 3, 2015 9:35 PM by philmodjunk

    Finding Child records based on Parent records found set

    ChanhThach_1

      Title

      Finding Child records based on Parent records found set

      Post

      I have 2 tables in a one to many relationship and are linked together with an ID number. The Parent table has a date field on when the record was made. I want to be able to find all the records in the Parent table that were made in the last 30 days, then use the ID number to find all the related child records.

      So far I am able to find all the records made in the last 30 days in the Parent table, but I am not able to figure out how to find all the Child records that belong to all the records in the Parent table (found set).

       

        • 1. Re: Finding Child records based on Parent records found set
          GuyStevens

          The simplest way is to make a layout based on the child table and to simply add the Date field from the Parent table.

          If they are related you can just look for the date of the parent table from within the Child table.

          I hope that helps.

          • 2. Re: Finding Child records based on Parent records found set
            ChanhThach_1

            Guy

            Are you talking about using the modify button in table view on the Child table and adding in a field from the Parent. Then running the date search on both tables?

            I'll have to give that a shot.

            Ultimately, I am just trying export tables and keeping the file size small.

             

            • 3. Re: Finding Child records based on Parent records found set
              philmodjunk

              What you describe is one option for a manual find. With a scripted find, you might not have to add the field to the view or layout at all.

              This could also be done on a form or list view by using the field tool to add the field in order to make a manual find possible.

              • 4. Re: Finding Child records based on Parent records found set
                ChanhThach_1

                Phil,

                How would you script the find to get all records that are related?

                After I get the found set for the Parent table, I tried:

                Go to Layout[Child]

                Enter Find[]

                Set Field[Child::ID;Parent::ID]

                Perform Find[]

                But I keep getting a perform find error about the data type criteria. I checked to make sure that all data types are the same in both tables. I am going down the wrong path or is there something that I am doing incorrectly this script?

                • 5. Re: Finding Child records based on Parent records found set
                  philmodjunk

                  First, you never need to get the found set of parent records. Take another look Guy Stevens' suggested method.

                  You can perform a find for the child records on a layout based on the child table while also using criteria in fields from the related parent record.

                  Example:

                  Customers-----<Invoices

                  Customers is the parent of Invoices. Say I want to see all Invoices  with a total of $200 or more that are also for customers with a Shipping Address in Modesto, CA.

                  I can go to an invoices layout, enter find mode and enter this criteria (either in a manual or a scripted find):

                  Customers::ShippingCity: "Modesto"
                  Customers::ShippingState: "CA"
                  Invoices::InvoiceTotal: >200

                  For examples of how I would script a find, see: Scripted Find Examples

                  • 6. Re: Finding Child records based on Parent records found set
                    ChanhThach_1

                    Wouldn't I need to get the found set of both the Parent records and Child records if I wanted export both the parent and child tables as 2 separate tables.

                    Using Guy's method, I can do the searches manually but I cannot script the find because the parent field I added onto the child table layout does not show up when I use set field[] or perform find[].

                    • 7. Re: Finding Child records based on Parent records found set
                      GuyStevens

                      You never added the parent field onto the child table. You are simply displaying a field from a related table.

                      In the script you can do exactly the same.

                      Just do a set field in the field from the parent table and it will wirk the same as the manual find.

                      As long as tables are related you can do a find in all of the fields from both related tables at the same time.

                       

                      • 8. Re: Finding Child records based on Parent records found set
                        ChanhThach_1

                        Maybe I'm missing something.

                        When I do a Set Field[]. Am I doing a

                        Set Field[Child::ID;Parent::ID]

                        or

                        Set Field[;Parent::Date] <----- I do not know which target field to specify. Since the Child table does not have a date field.

                        When I do it manually, the Child table shows the Parent date field when I place the Parent date field in the layout.

                        Thank you guys for responding.

                         

                        • 9. Re: Finding Child records based on Parent records found set
                          ChanhThach_1

                          Okay,

                          I think got it now. I used Guy's method.

                          Go to layout[Child Layout] <----layout of Child Table with the parent date displayed

                          Enter Find[]

                          Set Field[Parent::Date;  " > "  & Get ( CurrentDate ) - 30] <-----search parent date created in the last 30 days

                          Perform Find[]

                          I did find all the Child records with the Parent date created in the last 30 days, but it did not search the Parent records for records made in the last 30 days (which is not a problem since it has unique keys.)

                          Hopefully this is what you meant for me to do Guy.

                          Thanks again.

                           

                          • 10. Re: Finding Child records based on Parent records found set
                            philmodjunk

                            I note that a detail crept in that I don't see in your original post:

                            Do you want two exports or one? If all you want is an export that combines data from parent and child records. We have exactly spelled out what you need to do as an export from the context of the child table can include fields from the parent table.

                            But if you want a separate export of records from that parent table, then you'll still need that found set of records on a layout based on your parent table in addition to what we have specified here.