How to save data of different companies in one table?

Aravindh_Navision

Hi Guys,

I have designed one dataport (DATAPORT) to extract data from a G/L Entry table (GLE Table) and simultaneously I am saving those data in another new table say NEWTABLE, with few fields. From this new table, I am doing few grouping calculations and exporting the grouped data again via a processing only report. This entire flow is working fine. [I need both text files - i.e., before and after grouping]

What should I need to do to save data of all companies (in same database) in the NEWTABLE when the DATAPORT is triggered? This helps me in grouping based of "GL Account No." for all the companies and export it as a single text file as output via processing only report.

Can anyone guide me to do the above?

Thanks and Regards,
Aravindh R.

MBerger

There are 2 ways you could do this :
1) Set the property 'DataPerCompany" off your NEWTABLE to "No.", that way it's contents is available from every company.
2) Use NEWTABLE.ChangeCompany('<name of company>') to select the company you want your data to be saved in and then insert it.

Aravindh_Navision

Thank you MBerger for your reply.

I did as you have mentioned in you reply.

1. G/L Entry is the dataitem. (For an information, I have placed 5 G/L Entry dataitems for exporting in different filtering conditions.)
2. recCompany - Record variable - Company
3. NEWTABLE - Record Table - <Table newly created to store records> (Set the "DataPerCompany" property to NO)
4. varDate and varCode are the Date and Code datatype fields respectively. (I have declared many variables for exporting it through dataport fields.)

5. And done code in below trigger.

G/L Entry - OnBeforeExportRecord()

recCompany.RESET;
IF recCompany.FIND('-') THEN BEGIN
  REPEAT
    "G/L Entry".FIND('-');
    IF "G/L Entry".CHANGECOMPANY THEN BEGIN
      REPEAT
        <Field calculations and exporting field variables.>
        
        varDate := <Assigned date field from another table.>
        varCode := <Assigned code field from another table.>
        .
        .
        .
        .
         
        CLEAR(NEWTABLE);
        NEWTABLE.INIT;
        NEWTABLE."Date" := varDate;
        NEWTABLE."Code" := varCode;
        .
        .
        .
        .
        NEWTABLE.INSERT;

      UNTIL "G/L Entry".NEXT = 0;
    END;
  UNTIL recCompany.NEXT = 0;
END;

Seems it is looping 'n' no. of times and finally showed the message to Expand the Database. Can anyone tell me whether the above code is correct? If not correct me where I am going wrong.

vijay_g

This function work as..

RecVar.CHANGECOMPNAY("Company Name");
Now this record var will be referencing to the company has used in function.

ufuk

Also, you have to move FIND('-') command after ChangeCompany command.

G/L Entry".CHANGECOMPANY(recCompany.Name);
IF "G/L Entry".FIND('-') THEN BEGIN

Aravindh_Navision

Thank you Vijay and ufuk for your reply. My final code looks like below. When I tried to give the record count message in G/L Entry - OnPreDataitem() trigger, I am getting the correct counts for all the companies. I am writing the below code in G/L Entry - OnBeforeExportRecord() trigger, but the records are not getting inserted in the NEWTABLE.

IF recCompany.FIND('-') THEN BEGIN
  REPEAT
    "G/L Entry".CHANGECOMPANY(recCompany.Name);
    IF "G/L Entry".find('-') THEN BEGIN
      REPEAT
        <Field calculations and exporting field variables.>
        
        varDate := <Assigned date field from another table.>
        varCode := <Assigned code field from another table.>
        .
        .
        .
        .
         
        CLEAR(NEWTABLE);
        NEWTABLE.INIT;
        NEWTABLE."Date" := varDate;
        NEWTABLE."Code" := varCode;
        .
        .
        .
        .
        NEWTABLE.INSERT;

      UNTIL "G/L Entry".NEXT = 0;
    END;
  UNTIL recCompany.NEXT = 0;
END;

ufuk

It is difficult to understand the problem from the code you provided. First of all, debug your code and see what's happening.

When I look at your code it seems you are looping in a dataitem (GL Entry) and I think you should use a record variable instead. If you want to use this G/L Entry as dataitem, you have to loop in Company as first dataitem and then the G/L Entry dataitem which is indented under the Company.

Aravindh_Navision

Ufuk, In dataport, we cannot indent and also there is no common field between G/L Entry and Company.

vijay_g

If you can debug the code you will find the exact problem where and why this is not inserting the record in the table.

Note- For indent within two dataitem it's not mendatory that both dataitem must have common field.

Aravindh_Navision

Hi Guys,

I am extremely sorry for confusing you all #-o . The previous code itself is working . I am using a variable inside loop to define the company name which reads the current company name (COMPANYNAME). Instead I need to give it as recCompany.Name for that variable.

Thanks all.

Aravindh.