1 2 Previous Next 17 Replies Latest reply on Apr 7, 2011 11:03 AM by philmodjunk

    Scritping Help

    MatthewMackay

      Title

      Scritping Help

      Post

      I'm trying to write 2 scripts for my solution;

      1 that copies a child record to all parent records and 1 that duplicates a parent record with all it's child records.

      I don't really know where to begin, any help would be appreciated.

      cheers

        • 1. Re: Scritping Help
          LaRetta_1

          "1 that copies a child record to all parent records"

          We do not know enough about your solution to properly suggest but you shouldn't need to copy anything from a child to the parent.  Rather, you copy parent IDs to the child to relate them.  There is one exception, i.e. if you wish to store a 'preferenced address' (Addresses::AddressID) into a parent record's field called PreferredAddressID.  But I've the feeling that isn't what you want to do since you say copy to 'all parent records.'

          "1 that duplicates a parent record with all it's child records."

          Again, (lack of) redundancy is the reason databases rock.  I wonder if it is necessary to duplicate the information (it may be) and if instead it wouldn't work best to relate in a many-to-many fashion.

          It would work best to see your file and have you describe exactly what you want to have happen and where.  Only then can we properly advise on the best approach. :^)

          • 2. Re: Scritping Help
            MatthewMackay

            Sorry for the lack of information.

            I'm trying to build a property inventory solution.

            There are 3 main tables, "Property", "Rooms" and "Room Inventory"

            "Property" is the parent to "Rooms" and "Rooms" is the parent to "Room Inventroy".

            The reason for copying a child record from "Room Inventory" to all parent records in "Rooms" is for items like doors, normally in a property, most doors will be the same type/material, so if the user is able to create 1 record for a door and then apply that to all other rooms, it would make the invetory a lot faster.

            Similarly, duplicating a whole room with all it's inventory items just makes the inventory process a lot faster for similar rooms eg. bedrooms.

            Cheers

            • 3. Re: Scritping Help
              philmodjunk

              1 that duplicates a parent record with all it's child records.

              See this thread: 

              Duplicating Bill Of Materials (duplicating portal line items)

              In it, we duplicate a Bill of Materials, but the structure is the same as what you describe and should adapt to your needs with nothing more than some name changes to fit your tables and fields.

              • 4. Re: Scritping Help
                MatthewMackay

                Perfect, I'll have a play about with that script tonight, looks like it should work perfectly.

                Can it be easlily adapted for duplicating the child record to all parent records or is this more difficult?

                • 5. Re: Scritping Help
                  philmodjunk

                  A child record only has one parent record so your question makes no sense to me.

                  • 6. Re: Scritping Help
                    MatthewMackay

                    I want to copy that one child record and add a copy in to each parent record, the reason is as follows;

                    The reason for copying a child record from "Room Inventory" to all parent records in "Rooms" is for items like doors, normally in a property, most doors will be the same type/material, so if the user is able to create 1 record for a door and then apply that to all other rooms, it would make the invetory a lot faster.

                    • 7. Re: Scritping Help
                      philmodjunk

                      Ok, that's not a case of copying a record to "all of its parent records".

                      You can put the child data in variables, then loop through the parent records with a script like this:

                      #From Parent record's layout
                      Freeze window
                      Go To Record [first]
                      Loop
                         Set Variable [$ID ; Value: Parent::Primarykey]
                         Go To Layout [ChildLayout]
                         New Record/Request
                         Set Field [Child::Foreignkey ; $ID]
                         Set Field [CHild::OtherField ; $OtherVariable] // do this for every field you need to replicate
                         Go To Layout [Parent]
                         Go To Record/Request/Page [next ; exit after last]
                      End Loop

                       

                      • 8. Re: Scritping Help
                        MatthewMackay

                        This script is almost working, but it's duplicating the child record but just adding it to the same parent ID, then it's adding the duplicate to all other parent ID's but not all the fields?

                        • 9. Re: Scritping Help
                          philmodjunk

                          Please take another look at my last post and note the following:

                          You can put the child data in variables, then loop through the parent records with a script like this:

                          This is an outline of the actual script. You have to add lines like this:

                          Set Field [Child::OtherField ; $OtherVariable]

                          For each value loaded into variables before the start of this section of the script.

                          • 10. Re: Scritping Help
                            MatthewMackay

                            I've got;

                            Freeze Window

                            Go to Record/Request/Page[First]

                            Loop

                            Set Variable[$Room;Value:Parent Table::PK_Room_ID]

                            Set Variable[$Category;Value::Child table::Category]

                            then a heap of other child table variables

                            Go to Layout[Child Table layout]

                            New Records/Request

                            Set Field[Child Table::FK_Room_ID;$Room]

                            Set Field[Child Table::Category;$Category]

                            ....................

                            Go to Layout[Parent]

                            Go to Record/Request/Page[Next;Exit after last]

                            End Loop

                            • 11. Re: Scritping Help
                              philmodjunk

                              Not in the correct spot here. You only need to load the child record's variables once, not every time the script loops to another parent record.

                              #starting on child record's layout....
                              Freeze Window
                              Set Variable[$Category;Value::Child table::Category]
                              //then a heap of other child table variables

                              Go To Layout [ParentTable Layout]
                              Go to Record/Request/Page [first]
                              Loop
                                Set variable [$Room

                              and so forth.

                              • 12. Re: Scritping Help
                                MatthewMackay

                                It's still adding the duplicate to the original parent record I was already in? The rest is working though :).

                                • 13. Re: Scritping Help
                                  philmodjunk

                                  Omit that record from the found set before you start looping through the other records.

                                  • 14. Re: Scritping Help
                                    MatthewMackay

                                    Sorry to keep adding to the post but there's one more thing I'd like the script to do but not sure how it would work.

                                    At the moment, when a user adds a new room record, several common room inventory records are automatically created, flooring, walls, windows etc.

                                    It's these records I'd be using the discussed script for to duplicate for all parent records (Rooms).

                                    I've just realised though that if the user duplicates say "flooring", with all the flooring details for that room, then it obviously adds this as a new record to all rooms.

                                    Because of the previous mentioned script creating standard items, the user ends up with the blank "flooring" record and the duplicate "flooring" with all the populated fields.

                                    Is there any way the script could search for duplicates and either remove or replace certain fields?

                                    1 2 Previous Next