Max Sequence number based on value in table

I apologize if this is a duplicate post. I tried posting this earlier but it did not look like it posted properly. Maybe it did but I cannot tell.

What I need to do is look at a table that has a record number, find out what it is if it exists, then use that number to start a new CSV file counting off from what the current number is.

For example:

if the table has 300 records, I want to start my CSV at 301.

if the table does not have any records, I want to start my CSV at 1.

I have been playing around with the multi-row formula but don't seem to have it right yet.

Any help would be greatly appreciated!

Thanks,

Randy

Workflow

Accepted answers

All comments

Jean-Balteryx

Hi @Trip ,

If I understood correctly, you'd need to find the max value using a Summarize tool then generate new rows using Generate Rows tool starting from max value.

Jonathan-Sherman

Hi @Trip,

You could take the approach of using a Count Records tool to count the number of record in your first CSV file. If there are three records this will return 3, if no records it would return 0. Appending this to your main CSV table you can add the number of records to the record id of your normal table.

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

20210715 - UpdateRecordID.yxzp

Trip

Yes

Jonathan-Sherman

@Jean-Balteryx I would avoid using a summarise. The summarise tool needs at least one record to produce an output (i.e you can't get a count rows of 0 whereas you can in the count records tool.

Summarise tool:

Count records tool:

Kind regards,

Jonathan

AngelosPachis

Hi @Trip ,

If I understand your question correctly, you can use a summarize tool after your first file and then append that to your new table, where you will have created a separate record ID spanning from 1 till N. Then you can add the two to get a new record ID column, starting from your previous table max ID +1.

It get's tricky if there are no rows in your first table, because your summarize tool will then return no rows. This is why you should add a count records tool.

Hope that helps,

Angelos

example.yxmd

Trip

It is working well except when there is no data returned from my SQL query that feeds the first node. If a select statement that feeds it does not return anything, is there a way to substitute data?