Skip to content
+1-888-319-3663

COMMUNITY FORUM

Multicompany Smartlist has duplicate results in home company

Barb Mehling asked 10 months 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. 
Answers
Patrick Roth Staff answered 10 months 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.

If you would like to submit an answer or comment, please sign in to the eOne portal.