Multi Company Data to be shown on a form

onkarmm

Hi all...
I've got an unique situation. The client operates 7 different companies for the same business. The cust, Contact, Vendor data is supposed to be shared across the group. MBS advises not to share the ledger tables (Thru DataPerCompany = No on the table).

So, am forced to use "changecompany" and extract data like Outstanding, Inventory for a particular Item etc in reports and forms. in the forms, am not able to apply flowfilters on data calculated from other companies. Please advise... :-k

Christian_Feitl

We had a similar demand and after some research we decided not to use DataPerCompany=No because of the problems you mentioned.
I have to say that we only had to do that for contacts, but it was tricky enough.

We synchronize the data between the companies. So we added some C/AL code in the OnInsert, OnModify, OnDelete and OnRename triggers. If a user changes data in one company it is written to all other existing companies that have a flag "Synchronize Companies" set in CRM Setup.

I do not have a idea for what size of installations this is applicable because of performance, locking and so on. For us it worked fine and still works. You should check that before you decide to do it the same way.

Chris

Marije_Brummel

This is indeed a complex senario. You can try to make a codeunit which do the calculation for what normaly the flowfields calculate.

You can use this codeunit in all reports so you only have to program this once.

There is also a trick to show the data of all companies in one form without sharing the table.

DenSter

You really need to be careful with the datapercompany setting. You can REALLY screw up your database if you make assumptions as to how it works.

ara3n

If you are using SQL. You could create Views and Base your reports and form s off the views.

onkarmm

HI all

Christian Feitl : plz advise me abt u'r excercises on Contacts bcz we are testing with the complete RM module(marketing in nav 4) sharing across all companies. we are using DataPerCompany = No for this and sharing all RM tables (contacts related, Opportunities, to-dos etc etc). this is required bcz the client is using different companies only for admin-fin purpose and all the activities done by the sales team are transparent to all the companies.

Mark Brummel : I am searching for the tool u r talking abt in which i can show all company data on a single form without changing DataPerCompany since we CANT share Ledger tables. And thanx for ur advise abt the codeunit since it'll simplify making reports.

DenSter : I share ur views abt that property, bcz its behaviour is not known. will it be advisable to share All the marketing(RM) related tables except offcourse the setups where we define the no serieses?

Can we use the forum expertise and find a suitable solution? I have got 3 developers to work on this only. I feel that this is going to be suitable to many clients bcz even though there might be multi comp in a conglomerate, the recievebles and payables need to come under one umbrella somehow so that we can talk abt really integrated solution. What's ur view? :?:

Marije_Brummel

Example

Form 31, Item List

OnOpenForm
IF RunOnTempRec THEN
  CreateMultiCompanyTable();

OnFindRecord

IF RunOnTempRec THEN BEGIN
  TempItem.COPY(Rec);
  Found := TempItem.FIND(Which);
  IF Found THEN
    Rec := TempItem;
  EXIT(Found);
END ELSE
  EXIT(FIND(Which));

OnNextRecord


IF RunOnTempRec THEN BEGIN
  TempItem.COPY(Rec);
  ResultSteps := TempItem.NEXT(Steps);
  IF ResultSteps <> 0 THEN
    Rec := TempItem;
  EXIT(ResultSteps);
END ELSE
  EXIT(NEXT(Steps));

Function CreateMultiCompanyTable()


TempItem.DELETEALL;
IF Company.FIND('-') THEN 
  REPEAT
    Item.CHANGECOMPANY(Company.Name);
    IF Item.FIND('-') THEN 
      REPEAT
        TempItem := Item;
        TempItem.Company := Company.Name;
        IF TempItem.INSERT THEN;
      UNTIL Item.NEXT = 0;
  UNTIL Company.NEXT = 0;

It is possible that an Item exists in more companies. You can add a field company and a field to the Item table. If an Item exists in more than one company you fill this field with 'company1, company2'

DenSter

I would, under no circumstances, never ever, share ANY of the standard Navision master tables, ever. It is doable, but it is more trouble than it is worth.

If you have to create 'global' functionality, I would create a new table (let's use the Item table as an example), based on the standard table. So I would open the Item table, and save it as a new table called 'Global Item', and set its datapercompany property to No. This is the global table that all companies share their records from. Each company still has their own 'local' Item table that they have total control over. You need to make sure that you can only edit the global data in one designated company, so that not all companies can start changing all the data in there, so add a boolean field 'Main Office' or something to the Company Info table and only allow insert/modify for the main company.

Then you create copy/update functionality in the global table to populate records into the local tables, and functionality to do mass updates in all companies whenever a global record is modified. So if the Item person in company A wants to add a new Item that comes from the global catalog, they would go into the global item list, and copy it down to the local item table.

Don't forget that in addition to the Item table, you would also need to make 'global' conpies of all its subsidiary tables, and include those in the create/update functionality.

As far as looking into other companies' inventory, I would probably go with the view solution, but that would only work if you are on SQL, and I dont think it would be very fast, since I don't know if flowfields would work that way.

By the way, you need to understand that in Mark's example, he is using a temporary record variable. If you use a regular record variable, you will end up with all companies' items in your item table.

Marije_Brummel

DenSter wrote:

By the way, you need to understand that in Mark's example, he is using a temporary record variable. If you use a regular record variable, you will end up with all companies' items in your item table.

That can also be a solution

But you are forgetting the DELETEALL, if you use a regular variable you will first be deleting all items in the current company.:shock:

But hey, this is why I alwas tell consultants to test my software first before sending it to the customer :P

DenSter

Oh I'm not forgetting anything, I am deliberately leaving things out I'd be writing a 40 page design document if I put everything in here that you'd need to make this watertight.

onkarmm

TY very much both of u. offcourse it'll b tooo much to ask if we ask for watertight codes. no issues..

have u experimented with flowfilters on data from other companies. suppose cust net change u want to view from other companies, and u are just going in the other company (thru changecompany) and calsumming the detailed cust ledger entries. it'll happen at OnAfterGetRecord (or possible at other places?) after viewing the data, the user will place some value in the Date Filter on Cust list. how to pass this date filter to the previous calsum?

well... this is an interesting but pain in the a** problem when u want to solve in toto. Help!

DenSter

Flowfields will not work into other companies. The Date Filter is a flowfilter field, and will therefore also not work into other companies. Flowfields are not calculated in the OnAfterGetRecord, so that is not the place to write that code. Calcsumming is part of SIFT functionality, like flowfields, and will therefore also not work into other companies.

You could do some coding in the OnAfterGetRecord trigger, but I always try to avoid programming anything data related on the form. I think you need to get a Navision solution center involved in this issue.

davmac1

I have to do the same thing - one common set of contacts for multiple companies.
The idea that looks the best so far is a custom common table with all the contacts and then copy the contacts to each company.
We will also have a web app adding contacts.

The Navision code gets longer and more complicated with each release, so minimizing major changes to standard Navision code is the safest way to go.
Plus we plan to keep the project updated with new releases.

davmac1

The way I have decided to implement this is to only allow inserts of new contacts through our web app to the primary company contacts.
Use SQL Server trigger to insert contacts into the other companies (same database).
Use SQL Server trigger to update between companies.
Deletes - if needed - either do at company level - possibly block for all users other than SUPER.
Use the same numbers for creating customers and vendors (in this business, a lot of the customers are vendors too).
Block insert of customers and vendors from their associated forms.

Does anyone know why the link between customers and vendors is in the business relation rather than in the contact itself?

This implementation will also solve the other problem of having common customer and vendor numbers between companies.

Marije_Brummel

davmac1 wrote:

Does anyone know why the link between customers and vendors is in the business relation rather than in the contact itself?
[/qoute]

One of the benefits is that the contact table does not get locked if a customer/vendor is created

DenSter

Writing your own SQL Server trigger is a mistake. I just wanted to let you know . You should really implement stuff like this in Navision objects only.

davmac1

I ended up taking them out - caused problems with dataports.
Looks like version 5 will open up Navision to .net