With two tables that are exactly the same, how can the found set of one table be moved over to the other table without exporting and importing?
I suspect that it can be done by building an array and then moving the contents of the array over to the other table. If someone can provide an example of how this can be done it would be greatly appreciated.
I have done this on several occasions. I created a field in the table I am pushing the records to to act a the FK. Then just use the relationship node to transfer the data
//Define Validation
//Add reviewed by info before transfer
//Add Review Date
date_reviewed = application.getTimeStamp();
accountstemp_to_accounts_by_temp_id.newRecord();
forms.AccountingRecAcctProfile.controller.saveData();
//Capture New Account Number
globals.Integer5 = accountstemp_to_accounts_by_temp_id.account_id;
//Transfer Data
accountstemp_to_accounts_by_temp_id.account_dea_number = account_dea_number
accountstemp_to_accounts_by_temp_id.account_hin_number = account_hin_number
accountstemp_to_accounts_by_temp_id.account_type = account_type
accountstemp_to_accounts_by_temp_id.billing_address = billing_address
accountstemp_to_accounts_by_temp_id.billing_city = billing_city
accountstemp_to_accounts_by_temp_id.billing_co = billing_co
accountstemp_to_accounts_by_temp_id.billing_country = billing_country
accountstemp_to_accounts_by_temp_id.billing_name = billing_name
accountstemp_to_accounts_by_temp_id.billing_payment_days_due = billing_payment_days_due
accountstemp_to_accounts_by_temp_id.billing_payment_terms = billing_payment_terms
accountstemp_to_accounts_by_temp_id.billing_state = billing_state
accountstemp_to_accounts_by_temp_id.billing_tax_county = billing_tax_county
accountstemp_to_accounts_by_temp_id.billing_tax_exempt = billing_tax_exempt
accountstemp_to_accounts_by_temp_id.billing_tax_exempt_number = billing_tax_exempt_number
accountstemp_to_accounts_by_temp_id.billing_tax_rate = billing_tax_rate
accountstemp_to_accounts_by_temp_id.billing_zip = billing_zip
accountstemp_to_accounts_by_temp_id.created_by_id = account_reviewed_by_id
accountstemp_to_accounts_by_temp_id.creation_datetime = creation_datetime
accountstemp_to_accounts_by_temp_id.gpo = gpo
accountstemp_to_accounts_by_temp_id.idn = idn
accountstemp_to_accounts_by_temp_id.lead_origination = lead_origination
accountstemp_to_accounts_by_temp_id.mang_org = mang_org
accountstemp_to_accounts_by_temp_id.name = shipping_name
accountstemp_to_accounts_by_temp_id.natl_mang_id = natl_mang_id
accountstemp_to_accounts_by_temp_id.reg_mang_id = reg_mang_id
accountstemp_to_accounts_by_temp_id.rep_id = rep_id
accountstemp_to_accounts_by_temp_id.sales_territory_id = sales_territory_id
accountstemp_to_accounts_by_temp_id.shipping_address = shipping_address
accountstemp_to_accounts_by_temp_id.shipping_city = shipping_city
accountstemp_to_accounts_by_temp_id.shipping_country = shipping_country
accountstemp_to_accounts_by_temp_id.shipping_email_notification_address = shipping_email_notification_address
accountstemp_to_accounts_by_temp_id.shipping_email_notification_status = shipping_email_notification_status
accountstemp_to_accounts_by_temp_id.shipping_name = shipping_name
accountstemp_to_accounts_by_temp_id.shipping_state = shipping_state
accountstemp_to_accounts_by_temp_id.shipping_zip = shipping_zip
//Capture Temp Acct Number
globals.Text16 = account_in_number
application.closeFormDialog();
//Create & Update New Contacts
forms.AccountTempContact.Createadaddnewcontacts();
//Add Shipping Accounts
for(var i=1; i <= accountstemp_to_shippingaccounts.getSize();i++)
{
elements.portal_accountstemp_to_shippingaccounts_70.setSelectedIndex(i)
accountstemp_to_shippingaccounts.account_id = globals.Integer5
}
application.closeFormDialog();
//Open New Account Form
forms.AccountingRecAcctProfile.controller.find()
forms.AccountingRecAcctProfile.account_id = globals.Integer5;
forms.AccountingRecAcctProfile.controller.search()
application.showFormInDialog(forms.AccountingRecAcctProfile,200,10,600,750,‘Account Profile’,false,false,true);
Hope this helps, I am sure that there might be a better way
Thank you for your reply. In my case I will only be moving about 5 columns, but will have as many as 500 rows to copy from table 1 to table 2.
For this reason I thought that assigning the data to an array would be faster. However, I am having trouble both with the syntax of building the array and with what to do with the array after it is built. In other words, how to move the data from the array back into the second table.
Yes, that is allot of rows to move. I only execute that code for one record. Not sure on the array snytax though.
If you simply loop over your 500 records and create 500 records in the other table, it will fire 500 inserts, but that shouldn’t really matter. I suppose that you don’t need to use this feature every two minutes…
This can be easier done!
Look under Database Manager: copyMatchingColumns!
Now thats what I am talking about,
I will have to use that in the future.
The example of copyMatchingColumns in the new 2.2 Help file is:
var srcRecord = foundset.getRecord(1);
var destRecord = foundset.getRecord(2);
databaseManager.copyMatchingColumns(srcRecord,destRecord,true);
How can I use this to copy 500 rows from Table 1 to Table 2 if both tables have three columns named: id, description and value? I suspect it involves use of arrays. If anyone can provide sample code it would be greatly appreciated.
No, you only have to loop trough the foundset.
it search automaticly mathing columnnames!