Trigger Help: Setting a Calculated Value from another table and updating #inserted data

[BruceA]

After some "trigger" trial and error, I am finding I cannot update an #inserted record field with a calculated value. I can manage to get a value calculated but only in an "insert" event.

I have a table of text records called suffix "001" "002" "003" that are related to an account ID "12345"

On submission of new data, I want the default suffix for a new record to be calculated as "004"

Issue 1:

Using a trigger, I can select the parent table and calculate the MAX suffix which returns "003". When I add =1, I get 4, not "004"

is there a way to format the data to maintain the leading zero's?

Issue 2:

The only trigger I can get to make the calculation is an insert action. How can I structure the trigger to update the inserted data on submission of the new data?

Any advice would be greatly appreciated.

 

BA

 

 

[Hastur]

@BruceA
1. You can use the Formula field on the table level to add needed number of zeros.

2. You can use calculations in Update blocks as well if you add select into the Join directly and enable Group By.

This is just the visual example without any logic behind.