AnsweredAssumed Answered

Script Help - More efficient way to update and retrieve data from individual related records

Question asked by jeremyb on Sep 13, 2017
Latest reply on Sep 13, 2017 by DanielShanahan

I am seeking a more efficient way to start in one table at the first record (of 500k records), capture a value of one specific field (using variable $AAA), use that value to update a related record to that first record in a different table occurrence (specifically in the Field "Paid Thusfar Temp"), capture the new value in field "Paid Thusfar Temp" (again using variable $AAA), then return to the original table, update a specific field ("Paid Thusfar Temp") based on the captured value (aka $AAA), then proceed to the second record and repeat, for all 500k records.


I wrote a script for this which "go to related record [in new window]" which seems to work but is horribly slow (see script in attached screenshot).



How can I do this to work way more efficiently?



Other info, possibly relevant:  I have a database with 300k invoice records and about 500k payment records (both record types are in the same table). One invoice record can have numerous payment records, while there is only one invoice record for any give payment record.  I use a Table Occurrence "TO_Invoice Transtype for Claimkey to relate any given record to it's "Invoice" record.


My reason for this is that for every payment record, I want a field "Paid Thusfar Temp", to accurately document the total payments on any given invoice up through and including that payment record, but not including any later payments.  I thought of an alternative way to do this: I can sort the records in the desired order, label them with ascending serial numbers, relate the table to itself based on a table occurrence defined with a specific relationship (Only greater-than or equal to serial number) and use that to "Replace Records" calculating what I'm seeking.  However, for other projects that solution won't work, and even for this one I'm seeking a better way.....


Thanks again!