Multicompany Smartlist has duplicate results in home company
Barb Mehling asked 3 years ago
Open the Smartlist under Financial > Account Transactions and click Modify to open Smartlist Builder.
Call it Account Transactions MultiCompany or something like that.
Add the Calculated Field for Company Name and include it in the defaults.
Go to the new report in Smartlist and filter it for a specific segment to return a smaller number of records for testing, but one that is used by multiple companies.
What happens to us:
The company we are logged into has multiple records for the same Journal number.
Log into a different company and now that company will have duplicates but the previous one will be fine.
Why does this happen?
I removed the Company Name calculated field and I get the same results.
Call it Account Transactions MultiCompany or something like that.
Add the Calculated Field for Company Name and include it in the defaults.
Go to the new report in Smartlist and filter it for a specific segment to return a smaller number of records for testing, but one that is used by multiple companies.
What happens to us:
The company we are logged into has multiple records for the same Journal number.
Log into a different company and now that company will have duplicates but the previous one will be fine.
Why does this happen?
I removed the Company Name calculated field and I get the same results.
Answers
Patrick Roth Staff answered 3 years ago
Think the issue is a missing GP company database.
During the part where you mark the report as multi-company, you then have to select the companies. I believe that list is pulled directly from the SY01500 table.
You mark the records/companies (which gets written into the SLB11800 table).
When SLB runs the multicompany report, it loops through that SLB11800 table.
It runs a:
USE XXXXX
select aaa,bbb,ccc from <slb query>
and does that for each company.
But what I’d suspect is happening is that one of the companies SQL db doesn’t exist and so if you ran a DexSQL.log we would see
USE ONECO
SQL Error: database ONECO does not exist
select aaaa, bbbb, cccc from <slb query>
Because we didn’t switch contexts, the report runs in the current company db again and so you get duplicate data.
I suppose the other possibility is that the current user doesn’t have login rights to one DB and the switch DB also fails. I’d have to think about that but makes sense.
I believe there is an outstanding bug on this in SLB.
The only workaround would be to make sure that the destination companies you mark do actually exist in SQL and that the current user has login permissions to that DB.
During the part where you mark the report as multi-company, you then have to select the companies. I believe that list is pulled directly from the SY01500 table.
You mark the records/companies (which gets written into the SLB11800 table).
When SLB runs the multicompany report, it loops through that SLB11800 table.
It runs a:
USE XXXXX
select aaa,bbb,ccc from <slb query>
and does that for each company.
But what I’d suspect is happening is that one of the companies SQL db doesn’t exist and so if you ran a DexSQL.log we would see
USE ONECO
SQL Error: database ONECO does not exist
select aaaa, bbbb, cccc from <slb query>
Because we didn’t switch contexts, the report runs in the current company db again and so you get duplicate data.
I suppose the other possibility is that the current user doesn’t have login rights to one DB and the switch DB also fails. I’d have to think about that but makes sense.
I believe there is an outstanding bug on this in SLB.
The only workaround would be to make sure that the destination companies you mark do actually exist in SQL and that the current user has login permissions to that DB.
Sandy answered 2 years ago
I had the same issue with some reports imported from another system. Thanks to Patrick’s comment above, I was able to resolve the issue. I unchecked the box for multicompany and saved the report, then went back in and checked the multicompany box again and selected the desired companies. The reports no longer duplicated for the login company.