Options

Sharing the Employee table between all the companies

poppinspoppins Member Posts: 647
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???

Answers

  • Options
    ppavukppavuk Member Posts: 334
    I am not sure this is great idea. I would create code to synchronize employee table across companies instead.
  • Options
    Borrachon2013Borrachon2013 Member Posts: 5
    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
    Simple is simply the best
  • Options
    poppinspoppins Member Posts: 647
    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???
  • Options
    poppinspoppins Member Posts: 647
    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???
  • Options
    ppavukppavuk Member Posts: 334
    Investigate oninsert and onmodify trigger
  • Options
    KYDutchieKYDutchie Member Posts: 345
    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
    Fostering a homeless, abused child is the hardest yet most rewarding thing I have ever done.
  • Options
    poppinspoppins Member Posts: 647
    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???
  • Options
    ppavukppavuk Member Posts: 334
    do the filed numbers match in both tables? transferfields works only for matching filed numbers
  • Options
    ppavukppavuk Member Posts: 334
    another one, when you inserting employee nothing else is populated apart of no. you need to deal with this onmodify as well.
  • Options
    poppinspoppins Member Posts: 647
    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()???
  • Options
    ppavukppavuk Member Posts: 334
    there is unnecessary AllAmp.RESET; in your code. Yes, you could insert new rec on employee insert, and then update it when do modification.
  • Options
    poppinspoppins Member Posts: 647
    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...
  • Options
    ppavukppavuk Member Posts: 334
    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 :)
  • Options
    poppinspoppins Member Posts: 647
    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 :mrgreen:
    Thanks alot for your patience :)
Sign In or Register to comment.