Solved

FSM 5.7 - Change Sequence Starting point for records

  • 15 January 2021
  • 2 replies
  • 140 views

Userlevel 5
Badge +12

quick context: 

 

we have FSM & NAV integrated within each other. Somehow, our the sequence of contacts stopped matching

 

the last sequence in FSM is 55912 and NAV is 52614. So when NAV is making a contact that clashes with FSM, its overwriting tons of information, including place records.

 

Anyways, is there a way to change where the series continues from? can i have it jump up to 60,000 for example on the next entry? right now when i click new it goes to 52767 (which is 3000 less than the last record)

icon

Best answer by josdepijper 16 January 2021, 11:00

View original

This topic has been closed for comments

2 replies

Userlevel 5
Badge +12

Hi @jbernardo ,

This is possible. It is actually just a SQL sequence.

Normally I use beneath query to reset the sequence. But you can also set the sequence to a specific value. 

ALTER SEQUENCE [dbo].[SEQ_CONTSEQ] RESTART WITH 1 MINVALUE 1; -- SEQ_CONTSEQ

You can find all sequence counters by entering below query.

SELECT * FROM sys.sequences

You can use the first query to update the sequence. The first number in the query should be the value where you would like to start counting from. Try this in your TEST environment first.

 

The downside of FSM working with SQL sequences is that whenever you try to create a record, but not save the record, then the counter is still adding 1 to the current_value of the sequence. This might happen when your integration ran into errors in the past, or whenever just users tried to create contacts manually. If this happens, your FSM system is running out of sync with NAV. This means that the way you synced is pretty fragile. I would actually advice to use a user_def field for this sequence and then just let NAV being in the lead of whatever value is put in there, if that is possible anyway.

Userlevel 5
Badge +12

Hi @jbernardo ,

This is possible. It is actually just a SQL sequence.

Normally I use beneath query to reset the sequence. But you can also set the sequence to a specific value. 

ALTER SEQUENCE [dbo].[SEQ_CONTSEQ] RESTART WITH 1 MINVALUE 1; -- SEQ_CONTSEQ

You can find all sequence counters by entering below query.

SELECT * FROM sys.sequences

You can use the first query to update the sequence. The first number in the query should be the value where you would like to start counting from. Try this in your TEST environment first.

 

The downside of FSM working with SQL sequences is that whenever you try to create a record, but not save the record, then the counter is still adding 1 to the current_value of the sequence. This might happen when your integration ran into errors in the past, or whenever just users tried to create contacts manually. If this happens, your FSM system is running out of sync with NAV. This means that the way you synced is pretty fragile. I would actually advice to use a user_def field for this sequence and then just let NAV being in the lead of whatever value is put in there, if that is possible anyway.

apprecaite it