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?
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)
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
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.
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!
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]
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]
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]
PS. Import records can be much simpler, but keep in mind that not copying the data at all is simpler yet.