5 Replies Latest reply on Apr 10, 2012 10:23 AM by philmodjunk

    How to write a script to search another database,then add results of search

      Title

      How to write a script to search another database,then add results of search

      Post

      Hi! This has to do with a relational database many(2) to 1.  In one of the "many" databases I want to search for a unique ID and based

      on that search gather other fields then do a lookup to transfer information to the "1" database.  As there can be multiple records using

      the same unique ID.  I want to make  a script step that will create on demand new records in the "1" database as needed

      to house the information gathered. I am able to do a lookup, however, it only transfers the first record it comes across and I need

      that step to repeat itself until all info(based on the unique ID) has been  transferred.  The reason for using the lookup is that 

      even after the original data is transferred there can be changes to those records and I need to be able to keep the "1" database

      updated constantly. I would appreciate any input.  Aloha!

        • 1. Re: How to write a script to search another database,then add results of search
          philmodjunk

          Can you describe how, "on paper", you would gather data from multiple records and copy it to a single related record?

          Would these be aggregate values such as totals, averages, etc computed across the set of records found by your search?

          • 2. Re: How to write a script to search another database,then add results of search

            Purchaser order Database and Travel Itinerary Database (Many)

             Both send to MASTER (1)

             

            Purchase order database -

            Contains many records

            Unique field ID is the Purchase Order Number

            Contains Match Field with Master - Job Number

            Need to be able to send multiple records to Master.

            Once purchase orders records are sent, need to be able to update

            automatically in the Master.

            Purchase order database fields to send to Master would be

            Purchase order number(s)-(Unique field)

            Vendor Name(s)

            Total(s) for each purchase order.

            I was able to do a lookup however it only found the first purchase order record

            with the matching field(job number) and I need to keep repeating this until

            all purchase order records with the matching field(job number) are found 

            at the same time create a new record in the masterdatabase to house 

            this data.

             

             

            • 3. Re: How to write a script to search another database,then add results of search
              philmodjunk

              Is this creating a single record in "master" or multiple records? If a single record, how do yo propose to do that? Multiple Fields? Repeating Fields?

              If it's a group of records in Master that you are trying to create, You can perform a find on the source table to find the records. Then, you can either loop though the found records in the source table and create one record in Master for each found record or you can use Import Records to copy over the entire found set in one batch.

              And the big question: Why?

              Why do you need to copy any data at all like this? There can be very good reasons for doing so but often a person requests this here in the forum  for reasons that don't really merit the effort when relationships in a relational database may provide alternative approaches that don't require copying the data.

              • 4. Re: How to write a script to search another database,then add results of search

                This would be multiple records in the Master(on its own table). I abandoned the idea of using a repeating field, as you pointed out on an earlier post

                it only copied the first line in the repetition.  So, I would like to ask if you have any ideas on how to write a script to "loop" through the records

                in the source table until all records with the matching field(job number) are found and be able to copy that info (only 3 fields, purchase order number,

                vendor name and total of that PO) onto the Master, creating "new" records in the Master for each record found in the source table.

                 

                As for the why, I am attempting to track the various costs associated with the job in real time.  Aloha!

                • 5. Re: How to write a script to search another database,then add results of search
                  philmodjunk

                  As for the why, I am attempting to track the various costs associated with the job in real time. 

                  I see no reason why that requires copying data from one table to another. you can do searches on the current table to pull up a summary report of your data and no copying of records is required.

                  A looping script would have this basic structure:

                  #Perform find or use Go to Related records to pull up found set of records here first
                  go to Record/Request/page [first]
                  Loop
                     Set Variable [$ID ; value: Table1::IDField]
                     Set Variable [$Value ; value: table1::valuefield]
                     #use a variable for each field that has data you want to transfer to the other table
                     Go to Layout [table 2]
                     New Record/Request
                     Set Field [table 2::IDField ; $ID ]
                     Set Field [Tbale 2::ValueField ; $value]
                     Continue for each variable...
                     Go to Layout [Table 1]
                     Go to record/Request/page [next ; exit after last]
                  End Loop

                  PS. Import records can be much simpler, but keep in mind that not copying the data at all is simpler yet.