AFAS startup SQL selection of administrations

I would like to filter out some companies from my queries using a startup SQL. For my Exact Online-based clients, I’ve already set this up, but for AFAS I’m encountering some generic error messages. This is the code I’ve tried:

use
select administratie
from   AfasOnline.GetConnector.administratieparameters_algemeen_rapport\_
where  administratie not in (4, 8, 2, 3);

I suspect that AfasOnline.GetConnector.administratieparameters_algemeen_rapport_ is the wrong table, but I don’t see an alternative myself. Can someone help me with this?

The SystemPartitions@DataDictionary table provides an overview of the available administrations across all connectors. This makes it very useful for filtering.

However, AFAS does not have an administration concept like Exact Online, Twinfield, or Yuki. It is more like Odoo and Oracle Applications. It is recommended to define SQL views from the start-up that already filter the data from tables containing administration-specific data.

Thank you for the response. SystemPartitions@DataDictionary is indeed empty, is that what your colleague meant?

I am looking for a simple solution to avoid filtering all administrations.

The advice is to set up SQL custom views in the startup that exclusively allow the desired administrations. This can be achieved through a hard list or by joining with an InMemoryStorage table or subquery.

Example:

create or replace view administrations
as
select 1 code
union all
select 2 code
;
create or replace view Rules_Selection
as
select rgl.*
from   Rules rgl
join   administrations@DataDictionary ade
on     ade.code = rgl.administration

Do you perhaps have a slightly less technical answer? I use the connection to create my Power BI reports.

Certainly. There is no straightforward way to pass through only a part of the AFAS administrations. The described solution requires knowledge of SQL.

This question has been automatically closed after 1 week of inactivity. The last given answer has been marked as a solution.

Please create a new question in a separate topic if the issue recurs. In the new question, please include a link to this topic by pasting its URL into the text.