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.
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
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.