Creating ID values from MAX of existing dataset

Why don't you just make the id an identity column or use a sequence in Oracle.

If you use a sequence, you can ustilise some advanced table handling in the featuretypes parameters.

If not, and you want to do it all in FME. You need to get the max of the initial table (you are using a statisticscalculator but I would use SQLCreator and write a query to get the max using a SQL statement as that would use the databases power and not read every feature into FME).

You then need to do an unconditional merge to your new features. So that may be a featuremerger with "1" as the value of the requestor and "1" in the value of the supplier

Next comes the counter. In "Count_Start" create an arithmetic function of _max attribute + 1

Then that should give you the next value for the ids, to be written out into the table

Hi,

I understand that "set "ID" to @Value(_max)+1 --> my result is "+1" means if the max value is 99 then 1st record in excel will be 100 and 2nd record is also 100 instead of 101.

If this is the situation then I propose the following method...

1. Add counter transformer with starting value 1 with local (so each record will have attribute 1st records will have 1 and 2nd record will have 2)

2. Add the _max value to the attribute created in step 1 with attributecreator

Result will be as shown below...

1st record: _max=99 and _counter=1 results 100

2nd record:_max=99 and _counter=2 results 101

You can either use a SQL creator (or executor) to read the table and select a count( ID) as max. Or max(rownum) as max.

Then set a counter and have it start on max+1 where fme_feature_type = "your excel"

Or set a statisticscalculator and get the "Total Count Attribute" for "ID" on your SQL Reader and use the counter and have it start on _count+1 (_count is defaultname in statistics caclulator) where fme_feature_type = "your excel"

All of the other 3 answers here are good solutions. Basically you want a SQLExecutor to find the current max value and a Counter to start counting from MaxValue+1

But timing is everything here.

For example, if you put the SQLExecutor in the main data stream, before the Counter, then every feature will trigger it (ie if you have 1000 features you will make 1000 queries to the database). This is not efficient, and there are risks. What happens if the first feature is written to the database before the second feature runs the SQLExecutor? What happens if the Writer locks the database so the SQLExecutor can't read it (will they sit waiting for each other)?

So, to be safe, I would do as @todd_davis suggests. I would place a SQLCreator that queries the database to find the current maximum. Then I would merge that information onto the main features using a FeatureMerger with a fixed (unconditional) merge. Then add the Counter to start counting from MaxValue+1 (use the arithmetic editor in the Count Start parameter).

Hope that helps. Great answers everyone.