Copy records within Companies

kirkostas

I have a client who needs some tables to not be DataPerCompany.
I don't want to change the property because I will have to maintain several databases.
Is there any way to insert a record from one company to another when I enter a new record on a Table?
For example, I open customer table and I add a new record, and at the Insert trigger on the table I want to add the same record to another company of the same database. Is this possible?

kapamarou

Investigate the CHANGECOMPANY function of a record variable.

Example:

Cust2.CHANGECOMPANY(CompanyName);
Cust2.TRANSFERFIELDS(Cust1);
Cust2.INSERT;

Something like that... Try it and pay attention on the triggers behavior and table relations that might be missing from the other company (For example Customer Posting Group.

kine

You can found many posts regarding the DataPerComany. Using the way described by kapamarou is really, really, really dangerous solution and I will prefer some more complex solution like using NAS to do that (but you will need one NAS per company). This NAS can read the table from second company and transfer the changes, or all changes can be "stored" in some entry table, and based on this transfered into each company by the NAS. But this solution will be better because the Application logic will work for the target company.

It means reading from other company is no problem, storing into other company is not good. Make the target company active in the process and you have a solution...

kirkostas

Thank you very much for your quick reply. =D>

kirkostas

I am trying to do kapamarou example but I can't.
What I want is when I insert a new record on the Customer's table to insert the same record to another company. I can't find out in which trigger to put my code.
I tried to do that by creating a global variable "Inserted" boolean which activate in OnInsertRecord trigger on the form and at the OnAfterGetCurrRecord I use kapamarou's code. But an error occured "You cannot make any changes in the database until a transaction has been started".
Any idea ?

kapamarou

I'll give you an example so that you can follow from there:

Keep in mind though that kine's point is true: It is a Both Difficultand Dangerous task.



Select a Setup table. In that table add two fields. "From Copmany"and "To company".

Then got to table 18:

Find OnInsert Trigger:
Declare variable CustomerX of type Customer Record

MySetup.Get; // Read the setup table
IF COMPANYNAME = MySetup."From Company" //Check if you are inserting in the From company
THEN BEGIN
  CustomerX.TransferFields(Rec); //Copy values
  CustomerX.CHANGECOMPANY("To Company"); //Point To the 2nd Company
  IF NOT CustomerX.INSERT(FALSE) THEN //INSERT with no validation.
    ERROR('Customer Exists In Company %1',MySetup."To Company");
END;

Be careful. If you say INSERT(TRUE) then the validation called will be performed in the From Company leading to errors like existing contacts or you could get errors that the table relations are missing (like payment methods)... You would have to take care of the missing data.

What I would do would be to create an intermediate Customer-like table, insert my record in that table and then create a functionality that would create a customer from that data (You could do something similar with the rest of the "Required" data). Then I would have a user execute some Function like GETNEW customers and they would have to make sure that all the required data would be present.

Fully automating this process is possible but very time-consuming (in maintenance also) and dangerous...

kirkostas

Yes I agree that this is a very dangerous project. Imagine that my supervisor want to have a system with four, five companies opened all at the same time and when someone add or modify or delete a customer it will affect all the other companies as well. I don't think this is possible and we don't want to do it by changing DataPerCompany = False.
I wrote some code into OnInsert and OnModify trigger on Customer table and I realized that I have to manipulate all the functionality of the customer form which affect customer, salesperson/purchaser, post code, country , etc tables.....
I also noticed that when I modify something I have to Exit from Navision in order to see the changes to the other companies. Do you suggest that I have to enter a COMMIT always?

Do you have anything else in mind that I can do to achieve that?

DaveT

Hi,

Have you considered reversing the problem. i.e. if in company B and customer is in company A then show a consolidated list with the option to copy to the current company. It will be far easier to copy from a company into the company you're in. It also mean that the records are only created when needed and not time the no. of companies.

...just my $0.02

kirkostas

Do you suggest a button which synchronize all the needed data for the Customer table?
Lets assume that we have that button, and in company A we have 1000 customers and in company B we have 400, how am I suppose to sync data ?

kapamarou

You don't need to explicitly COMMIT.

You need to either open the 2nd company or read in a variable that's changed to the other company.

I can't think of another way right now. But whatever you do, you need to make sure that the related data are transferred to. Also you need to be sure that you won't have the case were in different companies the same series has been used and you have 2 customers that share the same No. but are actually different. You'll need to think of all the issues and decide. Maybe generate a new customer and add another field which will hold a "Common Customer No."...

Tell us exactly what the customer has asked be implemented and what your issues are in order to think of the best solution...

Personally I would use an additional table to store customers from companies and add them as different customers with same Common No. but it depends on what you need to achieve.

DaveT

EDIT in response to

Do you suggest a button which synchronize all the needed data for the Customer table?
Lets assume that we have that button, and in company A we have 1000 customers and in company B we have 400, how am I suppose to sync data ?

Hi,

I would approach it another way.

1. Show the 400 customers and a button to show the other company customers with a copy option.
2. Create a form with a temparory table of the merge 1000 customer and if a customer is select that is not on the current company the create.

To sync the data you can update the other companies in the onmodify trigger.

kine

kirkostas wrote:

Yes I agree that this is a very dangerous project. Imagine that my supervisor want to have a system with four, five companies opened all at the same time and when someone add or modify or delete a customer it will affect all the other companies as well. I don't think this is possible and we don't want to do it by changing DataPerCompany = False.
I wrote some code into OnInsert and OnModify trigger on Customer table and I realized that I have to manipulate all the functionality of the customer form which affect customer, salesperson/purchaser, post code, country , etc tables.....
I also noticed that when I modify something I have to Exit from Navision in order to see the changes to the other companies. Do you suggest that I have to enter a COMMIT always?

Do you have anything else in mind that I can do to achieve that?

1) Do not do that code on the form. this will turn all into a big mess...
2) Because you need to do the synchronization between tables, when something changed in the table, the table is good place for some code (but I prefer to place the code into codeunit and call it from the table triggers).
3) For me, you are not experienced enough to make this all correctly. Sorry, may be I am wrong and I it is nothing against you, just judging after reading all that...
4) Creating batch, which will read through all records from source company, and will transfer the differences into the current company is much safer for you. If you include some conflict handling and some timestemps on the record to be able to choose which one is newer, you have simple solution. You do not need to have the lists in sync on-line. Running this batch once per day looks good for me...

kirkostas

The customer has asked to have common data for Customer, Vendor and G/L Account tables for his 4 companies and we don't want to do that by disable DataPerCompany. Also he need some custom reports which will retrieve data for all companies and do some calculation. ](*,)

I believe that kine's latest solution is the best.

kapamarou

I don't think you will get away with this without requiring user intervention. Consider the following:

In Company A you have Customer 1 with Payment Method XXX.
In Company B you have Customer 2 with Payment Method YYY.

These two payment methods are the same but have different names. In such a case, a user should check that actually the two methods are the same and use YYY instead of creating XXX. Doing something like this in code requires a lot of work for testing differrencies in secondary tables in some of the important fields.

Show maybe the solution would be a form showing the new customers and requiring the user to complete some (most) of the work...

P.S. I just realized that XXX is not marked from the spell check but YYY is... :-k

DaveT

kapamarou wrote:

P.S. I just realized that XXX is not marked from the spell check but YYY is... :-k

I know is off-topic but... XXX or "Triple X" is used to describe extra strong e.g. beer. Friday afternoon - strong beer - hmmmm \:D/

kirkostas

XXX is good point for friday afternoon.

Savatage

Company Copy V1.5
Use this cute little tool to copy any field from any table between companies. This tool can by use in combination with Navision 3.70 (and higher).

http://www.mibuso.com/dlinfo.asp?FileID=275

souoik

hello am working for yse changcompany finction am workin on business central FR version whish use Payment Slip (Payment header ans payment line) so how to insert whit use the serie no in the first step by payment classe name .