How to make a trigger that copies multiple records to the same table get sequential number values for each new record

[DesiLogi]

Hi, I have a trigger in a table that runs On Update to copy and insert new records into the same table.  This happens when a user selects multiple records in a tabular datapage and uses Bulk Edits to check a field 'CopyIt'. If a record's 'CopyIt' value = true then it goes through the trigger. 

This works fine for copying multiple records at the same time into the same table. The issue is I have a field (ItemNum_Integer) in that table that needs to be sequentially ordered for each new record (sort of like an autonumber record but not set to that because sometimes the values start over for subrecordsets and have to be changed).

So the first new, copied record's ItemNum_Integer value would be the Max of the existing ItemNum_Integer  in that table (joined by a common ID number so it uses a sub-recordset) + 1.  This works but ONLY for the first new, copied record. Every record thereafter uses the same value. So if I copy 5 records the first record will have the correct Max ItemNum_Integer + 1 value but then each record after it will use the same number.  For example, if the first new/copied record has a Max ItemNum_Integer + 1 value of 106 then each of the five new records repeats 106 for their ItemNum_Integer value when they need to be 106, 107, 108, 109, 110 respectively. 

It seems like somehow the Max ItemNum_Integer + 1 isn't firing sequentially. Does anyone know how to do this? 

 

[DesiLogi]

Hi Tubby,

Thanks for the clarification--I think you're right about the 'Top' part--I hadn't thought of that. At least we know it's possible to use Bulk Edit with multiple records going through a trigger. I wasn't sure it would work at all. 

[Wikiwi]

@DesiLogi You can encapsulate your whole insert action block inside a for loop, that way it will still run as expected using bulk edit. You can check this other article where they edited an existing trigger to work on bulk edit (highlighted by the red box). 

 

[DesiLogi]

Ah that's great Wikiwi--thanks very much for posting that and I'll take a look at it soon. I'd love to solve this issue. 

[DesiLogi]

I just tried the For Loop and am still getting the same thing--106 repeated for each record in the bulk edit where it should be 106,107,108, etc. Here's the trigger--there must be some reason it's not sequentially adding 1 to each ItemNum_Integer value as new records are created. 

 

[Wikiwi]

So what happening is, before the bulk edit, the last record is in 105 and once the bulk edit was executed all records have 106 instead of a sequential value? If that's the case, I think instead of adding one, you can create a variable outside your loop and set that value to 1 then use that value to add to your ItemNum_Integer and at the end of your loop add 1 to that variable so on the 2nd run of the loop it will use the current value of the variable which is 2. Hope this helps.

[DesiLogi]

Got a solution to this issue--the problem was that the 1st 'Where' clause was holding up the Loop from sequentially running. If the 1st 'Where' clause is put in the 'For' section itself, the Insert runs the Loop sequentially. I ended up using a lookup table (joined) to get the next value for ItemNum_Integer. Once that value is pulled for the new record the trigger will update that lookup table with +1 so the next record in the Loop gets a sequential number. Here's some images of the trigger--works like a charm (I had some help, can't take all the credit ;). 

 

 

 

The Forum isn't letting me upload the last image of the trigger but it's an Update clause for updating the lookup table for the ItemNum_Integer value. 

[Tubby]

1 hour ago, DesiLogi said:

Got a solution to this issue--the problem was that the 1st 'Where' clause was holding up the Loop from sequentially running. If the 1st 'Where' clause is put in the 'For' section itself, the Insert runs the Loop sequentially. I ended up using a lookup table (joined) to get the next value for ItemNum_Integer. Once that value is pulled for the new record the trigger will update that lookup table with +1 so the next record in the Loop gets a sequential number. Here's some images of the trigger--works like a charm (I had some help, can't take all the credit ;). 

 

 

 

The Forum isn't letting me upload the last image of the trigger but it's an Update clause for updating the lookup table for the ItemNum_Integer value. 

I believe its not just the WHERE statement that is preventing the loop to happen.


If you'll read this SELECT, it selects the TOP 1 record in Items_2 that matches the ProjectID of #inserted. #inserted only stores 1 value at a given time, that is the record that you have edited on the table. So that is the reason why it uses the same value as the first record, is because it gets the top most record repeatedly.

You may noticed that on the FOR loop that worked, it uses #record instead for this same part. Which makes the value change based on what record is the triggered action looking at the moment. If that makes sense.

Anyways, nice workflow. Gonna  bookmark this as this might get useful for me as well.