Sharing the Employee table between all the companies

poppins

Hi everyone,

A customer of mine has a NAV database with 8 companies (NAV 2009 R2).
He wants to share the list of employees between all the companies.
I tried to change the 'DataPerCompany' property to No but I got the following error message:

It is not possible to change the property 'DataPerCompany' when there are records in the Employee table. Follow these guidelines:

* Export all records in the table to a file.
* Delete all records from the table.
* Modify the table definition.
* Import all records from the file.

What shall I do???

ppavuk

I am not sure this is great idea. I would create code to synchronize employee table across companies instead.

Borrachon2013

First of all, you have to check whether you have to share more tables then just this one.... check all tables that are related to 'Employee'.
Second, a lot of experts say about sharing tables JUST DO NOT DO IT - which means it is something you should leave to experts.
However, I do it often but you have to be carefull what to share ande what not. The best example here is the Item table. if you share this one between companis then they all have an incorrect cost price in the future... so think before you act!

But if you want to do it, what has been suggested is correct.

Look at what tables in which companies contain records.
Build an XML port to export all contents of tables into different files (use the same XML port foor all exports and imports)
Empty all Emplyee tables in all companies (NOTE: this might not be possible because of relations, postings in other tabels....)
Change the property 'Datapercompany' to NO
Import all the files with the same XML Port.

Last advice: if you're not sure of what you are doing, listen to the experts: JUST DO NOT SHARE TABLES

Greetings from Germany

poppins

ppavuk wrote:

I am not sure this is great idea. I would create code to synchronize employee table across companies instead.

I am not sure I understand what you mean..How to do that???

poppins

I created a new table with a similar structure to the Employee table...and I set the DataPerCompany property to yes...and I populated it with records from Employee tables(from the different companies)...
Now I have to make sure that everytime a record is added to an Employee table, it is automatically added to my new table...
How to do that???

ppavuk

Investigate oninsert and onmodify trigger

KYDutchie

Hi,

What I would do in your situation is, create a new table similar to the employee table but on this new table clear the flag "Data Per Company".
You have to add a field to this new table that can act as a "Company Identifier". I would also set this field as the first key element.
So your Primary key would look like this: "Company Identifier","No.". I would add this additional key field to the table to prevent errors on duplicate employee numbers across companies.

I would suggest to setup this "Company Identifier" field in the "Company Information" table.

Then I would change the "OnInsert","OnModify" and "OnDelete" triggers to update this new table with any modification to the Employee table.

In each trigger you would first read the "Company Information" table and get the "Company Identifier". Then depending on the action (Insert,Modify or Delete) update the record in the new non-company specific table using the "Company Identifier" and the "No.".

I hope this helps,

Regards,

Willy

poppins

KYDutchie wrote:

Hi,

What I would do in your situation is, create a new table similar to the employee table but on this new table clear the flag "Data Per Company".
You have to add a field to this new table that can act as a "Company Identifier". I would also set this field as the first key element.
So your Primary key would look like this: "Company Identifier","No.". I would add this additional key field to the table to prevent errors on duplicate employee numbers across companies.

I would suggest to setup this "Company Identifier" field in the "Company Information" table.

Then I would change the "OnInsert","OnModify" and "OnDelete" triggers to update this new table with any modification to the Employee table.

In each trigger you would first read the "Company Information" table and get the "Company Identifier". Then depending on the action (Insert,Modify or Delete) update the record in the new non-company specific table using the "Company Identifier" and the "No.".

I hope this helps,

Regards,

Willy

I tried to proceed as you suggested...
I created the new table AllEmployees with the appropriate key.In the OnInsert trigger of the Employee table, I wrote the following code:

AllEmp.TRANSFERFIELDS(Rec);
AllEmp.INSERT;

but when I am inserting an new record in the Employee table, I only get the No. inserted in the AllEmployee table(my new table)...
What shall I do to get all the fields inserted???

ppavuk

do the filed numbers match in both tables? transferfields works only for matching filed numbers

ppavuk

another one, when you inserting employee nothing else is populated apart of no. you need to deal with this onmodify as well.

poppins

ppavuk wrote:

another one, when you inserting employee nothing else is populated apart of no. you need to deal with this onmodify as well.

I inserted the following code in the OnModify() trigger of the Employee table:

AllAmp.INIT;
IF AllAmp.GET("No.")THEN
BEGIN
AllAmp.RESET;
AllAmp."No.":="No.";
AllAmp."First Name":="First Name";
AllAmp."Last Name":="Last Name";
AllAmp."Job Title":= "Job Title";
........
AllAmp.MODIFY;
END
ELSE
BEGIN
AllAmp."No.":="No.";
AllAmp."First Name":="First Name";
AllAmp."Last Name":="Last Name";
AllAmp."Job Title":= "Job Title";
.......
AllAmp.INSERT;
END;

and the following code in the OnDelete() trigger:

AllAmp.GET("No.");
AllAmp.DELETE;

The results seem to be correct so far...
Do I need to put any verification in the OnInsert trigger()???

ppavuk

there is unnecessary AllAmp.RESET; in your code. Yes, you could insert new rec on employee insert, and then update it when do modification.

poppins

ppavuk wrote:

there is unnecessary AllAmp.RESET; in your code. Yes, you could insert new rec on employee insert, and then update it when do modification.

I tried to insert the employee in the OnInsert() trigger, but i get only the No. inserted...
Besides, I wanted to make difference between when I insert a new record and when I update an existant record...

ppavuk

create function updatenewtable(Employee) record Employee as parameter.

Inside this function write:

newtable.init;
newtable.transferfields(employee);
if not newtable.insert then
  newtable.modify; 

call this function on insert and onmodify in empl. table.

You can put whatewer you want before if statement.

Less code - less mistakes

poppins

ppavuk wrote:

create function updatenewtable(Employee) record Employee as parameter.

Inside this function write:

newtable.init;
newtable.transferfields(employee);
if not newtable.insert then
  newtable.modify; 

call this function on insert and onmodify in empl. table.

You can put whatewer you want before if statement.

Less code - less mistakes

It works just fine
Thanks alot for your patience