Create GL Account in all companies

alvi99
alvi99 Member Posts: 71
edited 2012-11-12 in NAV Three Tier
Hi all,

hope to get some help with this issue.

I need this functionality: When a new G/L account is created in a company this G/L account is also created in all other companies. (only the primary key = "No.")

I have tried:

Table 15 G/L Account
OnInsert() 
GlNoVar := Rec."No.";
CompanyRec.SETFILTER(Name,'<>%1',COMPANYNAME);
CompanyRec.FINDFIRST;
REPEAT
  GLAccountRec.CHANGECOMPANY(CompanyRec.Name);
  GLAccountRec."No." := GlNoVar;
  GLAccountRec.INSERT(TRUE);
UNTIL CompanyRec.NEXT = 0;

but this (ofcourse) causes a recursive error. Any ideas on how this could be done?

Thanks

Comments

  • jglathe
    jglathe Member Posts: 639
    Hi,

    the insert(true) will be the culprit. The Changecompany() function only affects TableData, not the underlying C/AL context of the record. So, your insert trigger will always be executed in the company you've opened.

    with best regards

    Jens
  • jglathe
    jglathe Member Posts: 639
    Hi,

    Aside from the recursive problem you're creating, that is... The source of the error is the same.

    with best regards

    Jens
  • alvi99
    alvi99 Member Posts: 71
    I see. Do you have any solution on how this could be done if i want ondelete and onmodify code executed in the other companies. Maybe a batch job...

    What about fields onvalidate code - it that the same case?
  • jglathe
    jglathe Member Posts: 639
    Hi,

    it's the same for any code in tables. The context of these variables doesn't change when a changecompany() was issued on the record. I would try to avoid structures which try to synchronize data over companies in table triggers. There is the "except sometimes" rule which always applies (except sometimes;). This would make the whole structure complex and complicated to manage.
    Synchronizing accounts is a master data management problem. The best practice I use is having a master company defining the allowed accounts and synchronizing from there (for the records/fields that make sense to be synchronized). To make it more usable, there is a "get new account" function showing the allowed accounts for the company I'm working in. It's the only way to create new accounts, so there are no deviations/conflicts in the local companies.

    with best regards

    Jens
  • alvi99
    alvi99 Member Posts: 71
    Ok thanks for info. I have tried to build a simple solution - can I get comments on it?
    Tabel 15
(only shows my code)

OnInsert()
GlNoVar := Rec."No.";
CompanyRec.SETFILTER(Name,'<>%1',COMPANYNAME);
CompanyRec.FINDFIRST;
REPEAT
  GLAccountRec.CHANGECOMPANY(CompanyRec.Name);
  GLAccountRec."No." := GlNoVar;
  GLAccountRec.INSERT;
UNTIL CompanyRec.NEXT = 0;

Added code on selected fields on OnValidate() trigger:

UpdateFieldInOtherCompanies(FIELDNO(Name));
UpdateFieldInOtherCompanies(FIELDNO("Search Name"));
UpdateFieldInOtherCompanies(FIELDNO("Account Type"));
UpdateFieldInOtherCompanies(FIELDNO("Search Name"));
UpdateFieldInOtherCompanies(FIELDNO("Debit/Credit"));
UpdateFieldInOtherCompanies(FIELDNO("Debit/Credit"));
UpdateFieldInOtherCompanies(FIELDNO("Gen. Posting Type"));
UpdateFieldInOtherCompanies(FIELDNO("Gen. Bus. Posting Group"));
UpdateFieldInOtherCompanies(FIELDNO("Gen. Prod. Posting Group"));
UpdateFieldInOtherCompanies(FIELDNO("VAT Bus. Posting Group"));
UpdateFieldInOtherCompanies(FIELDNO("VAT Prod. Posting Group"));

Added function:

UpdateFieldInOtherCompanies(FieldRefPar : Integer)
GLAccountRecordRef.GETTABLE(Rec);
FieldRefVar := GLAccountRecordRef.FIELD(FieldRefPar);
FieldRefVar.VALUE(GLAccountRecordRef.FIELD(FieldRefPar));

CompanyRec.SETFILTER(Name,'<>%1',COMPANYNAME);
CompanyRec.FINDFIRST;
REPEAT
  GLAccountRec.CHANGECOMPANY(CompanyRec.Name);
  GLAccountRec.GET("No.");
  GLAccountRecordRef2.GETTABLE(GLAccountRec);
  FieldRefVar2 := GLAccountRecordRef2.FIELD(FieldRefPar);
  FieldRefVar2.VALUE(FieldRefVar);
  GLAccountRecordRef2.MODIFY;
  GLAccountRecordRef2.CLOSE;
UNTIL CompanyRec.NEXT = 0;

    Br,
    Alvi
  • MarkHamblin
    MarkHamblin Member Posts: 118
    If the accounts need to be the same in each company, and all accounts will exist in all companies, you may want to consider making the General Ledger table common to all companies. This is done by setting the "DataPerCompany" property on the table to "No".

    This will essentially mean you have one table for all companies to share. Flow fields (e.g., balances, etc.) will work fine - it's only the master configuration that is shared. This saves you from having to write code to keep things in sync when accounts are added or edited.

    One side effect of this approach is that if you're accessing the SQL table directly for reporting, etc., it will no longer include the company name in it.

    - Mark
  • David_Singleton
    David_Singleton Member Posts: 5,479
    MarkHamblin wrote:
    If the accounts need to be the same in each company, and all accounts will exist in all companies, you may want to consider making the General Ledger table common to all companies. This is done by setting the "DataPerCompany" property on the table to "No".

    Nooo. Please don't do this. :thumbsdown:
    David Singleton
  • David_Singleton
    David_Singleton Member Posts: 5,479
    jglathe wrote:
    ...The best practice I use is having a master company defining the allowed accounts and synchronizing from there (for the records/fields that make sense to be synchronized). To make it more usable, there is a "get new account" function showing the allowed accounts for the company I'm working in. It's the only way to create new accounts, so there are no deviations/conflicts in the local companies. ...

    :thumbsup:

    This is almost exactly the way I do it.

    I add a boolean in the COMPANY data field "Use As Master" and a field "Master Company" linked back on it self to the company table filtered by Use As Master = FALSE and code to lock out a loop.

    Then a new boolean per each table to sync
    "Sync Master G/L"::Boolean
    "Sync Master Customer"::Boolean
    "Sync Master Item"::Boolean

    etc.


    Now in each master table add code that prevents modification if "Master Company" is selected. So you can only work on the master tables from the Master company. And you can still have normal companies AND you can even have multiple master companies if you need. There will be locking issues, but they are simple to resolve.

    By the way I normally also create a new ledger table that is common to all companies, and adds company name to the primary key (Company Name, Entry No.) and a batch to keep those synced at night, which makes it easy to view items by company or consolidated AR etc.
    David Singleton
  • MarkHamblin
    MarkHamblin Member Posts: 118
    David Singleton wrote:
    MarkHamblin wrote:
    If the accounts need to be the same in each company, and all accounts will exist in all companies, you may want to consider making the General Ledger table common to all companies. This is done by setting the "DataPerCompany" property on the table to "No".

    Nooo. Please don't do this. :thumbsdown:

    The DataPerCompany property is actually a very good option in a lot of cases, and shouldn't be dismissed out-of-hand without a discussion of it merits and limitations.

    We've used this numerous times with simple tables like units of measure (though never with slightly more complex tables like the G/L), and it is a much better approach with basic tables than writing code to synchronize a master company (which we've also done for more complex tables like Item where we also want to copy BOMs, extended text, etc. based on user selections).

    If, as I stated, you want the chart of accounts to match exactly between companies (i.e., have essentially a common chart of accounts), then the DataPerCompany property may be worth evaluating.

    Now, the downsides to using this approach:
    • Any validation that occurs on this table that affects (or reads) other company-specific tables only occurs within the company where the changes to the G/L were made. In the case of the G/L table, this primarily concerns the default dimensions (and deleting accounts). E.g., editing a default dimension in Company A will not update the default dimension table in Company B. Of course, you have a similar issue when doing a sync - you need to make sure you are syncing any related tables as well.
      • This could be, depending on the table, a major issue with tables where NAV updates, modifies, or evaluates company-specific data from the table or forms where the table is used.
    • If you already have transactions in any of the companies, making this change becomes very scary, as you need to delete all the G/L accounts before changing the property. If the companies are already live, making the change to use common data is likely a very bad idea.

    The upside, of course, is that no modifications, special master company, or processes are required to keep the master data (but not related data) synchronized between companies. If the functionality matches your requirements, then it is an approach worth at least evaluating.

    - Mark
  • David_Singleton
    David_Singleton Member Posts: 5,479
    MarkHamblin wrote:

    The DataPerCompany property is actually a very good option in a lot of cases, and shouldn't be dismissed out-of-hand without a discussion of it merits and limitations.

    How many more times do you think it needs to be discussed? :wink:
    David Singleton

Categories