Apex Code Development You need to sign in to do that
Don't have an account?
alex_from_75015 Handling comparison request in mass trigger
Hello
I need some help to develop a trigger (for mass insert)
Here is my need :
I have a reference table : lets call it TABLE1 with one field "start_row" (double) and one field "end_row" (double) and other information fields (TABLE1 has around 120000 records)
I am performing a mass insert on TABLE2 with one field called "position" (double)
For each insert (trigger before insert), I have to search in TABLE1 if there is a record where "start_row"<="position"<="end_row"
If one record is found, I need to transfer other information fields from TABLE1 to TABLE2 object inserted
If it was a one by one insert request will look like be
try {
myrecord=[select info1, info2, info3 from TABLE1 where start_row<=evt.position and end_row>=evt.position]; // limit 1 is not necessary since I am sure there is only one or none matching record in TABLE1
evt.info1=myrecord.info1;
evt.info2=myrecord.info2;
}
catch...
How can I perform this mass select ?
Thanks for your help
Regards
Works perfectly
Just have to shortener your variable so as not to hit 10000 characters limit for request
Thanks a lot
Regards
NB : you should apply to SFDC premium support since they say it was not possible to do my need in a trigger !!!!!
All Answers
My first thought of things to try would be to use dynamic SOQL to build up a query that includes all items.
(pseudo code)
For inter x = 0 to size of trigger.new
If it's not the first entry, add an 'OR' term
Add a "start_rwo"<= :trigger.new[x].position and "end_row" >= :trigger.new[x].position
So you should get something like:
myrecords=[select info1, info2, info3 from TABLE1 where start_row<=evt[0].position and end_row>=evt[0].position or start_row<=evt[1].position and end_row>=evt[1].position]
and so on.
Based on your description of the logic, you should only get up to 200 records in the result. This should be a small enough number for you to manually search through the records to find the right one. To avoid script limits you'll probably want to sort the results by start_row and implement a binary search on the resulting list.
The only concernt that remains is SOQL limit length. If you see an error occurring there, try splitting the list into two separate lists and do two queries instead.
I'd say this approach has a very high probability of working for you.
Good luck
Dan
Hi
Nice idea
I will try it
Regards
Alex
Works perfectly
Just have to shortener your variable so as not to hit 10000 characters limit for request
Thanks a lot
Regards
NB : you should apply to SFDC premium support since they say it was not possible to do my need in a trigger !!!!!