Using the same No. Series line on different companies in the same database

Hello Colleagues,

Came across a peculiar challenge and decided to share it.

I would like to use the same no. series line across the different companies on the same database for the purpose of maintaining the same sequence across the companies.

For example;

In Company 1 - Cronus Shipping has CR/14/001 as an employee no. and in the same database Company 2 - Cronus Logistics has a newly recruited employee who is to be assigned the next available no. which is CR/14/002. And at the end of the year (31/12/14), the last person to be recruited in Cronus Shipping has employee no. CR/14/056, so for the new year the employee no. for the first employee recruited REGARDLESS of the company would be CR/15/001.

NOTE: This no. series line is only applicable on employee nos. not across all other application areas.

How would you go about achieving the above?

All contributions are welcome.

Daniel,

Standard NAVSION doesn’t work in this way because in single database navision mention the table id with company name and every transaction is looked into the particular master table on that company.

You need to customize the way employee no pick up across the companies using same no series. or you have to do it manual.

Hello, Daniel,

To generate an employee number we can follow below steps.

  1. We can keep same number series pattern in the all companies.

  2. We will use ‘CHANGECOMPANY’ function to find out last maximum used employee numbers.

  3. Update current company number with maximum number in number series and generate new number.

If its most frequent transaction, we can have table locking issues.

Hope , this helps you.

Feel free to contact me any time for any query.

Kind regards,

Ankit Parashar

Microsft Dynamics Consultant

MindQuad

Company B data will not be seen in Company A if you want to search or vise versa.

If you extract a report of employee details with No. then there will be a gap of 1 no. in each record for either of the company.

Hello Ankit,

Based on your suggestion would you need to run a code periodically or it would run automatically when creating a new employee record?

That would be the case since the sequence isn’t maintained by just one company.

I would create a scheduled job to export out that series code line field from No. Series Line from Company 1 and bring it in to the other company. I would run it every 15 minutes.

I would do the same on Company 2 to Company 1. Each on a 15 min schedule with Company 1 using odd minutes (every 15 min starting 12:15)- Company 2 starting at 12:16.

It would be nice to know if you are also replicating staff records- that would help keep things in sync.

JV

Hi Daniel,

While generating a new record new emp no. a logic will be added into code unit to get maximum emp no. accross the companies and update last no. used in current company(Code unit content mixed logic of SQL statements and Nav CAL code).

The remaining, code will be as Nav standard.

Hope it helps.

Regards,

Ankit Parashar

MindQuad

What about Reporting ???

I’ve seen a clever solution in a former job. They basically copied the No Series functionality and called it ‘Global No Series’. Exact copy of the tables and the codeunits, but the Global No Series tables were all with DataPerCompany=No. For this customer the global numbers were only implemented for something that was custom for the implementation that was not used in the std NAV logic. With a little tweaking you could implement global invoice numbers for instance, although you’d have to keep the regular per-company and the global numberings strictly separate. You would have both: the std numbering series will be per-company, and the custom global ones are only for specific cases. It gets tricky though so you have to be very careful.