0 votes

Hi,

is it possible to have in Efficy show information from the relation one level up? I'm struggling with this and everytime the custom queryview have an error and this is been disabled.

I shall try to explain my situation. For a customer we use Main publications with linked Variants and these Variants are linked to Segments. A Segment will be linked to a Contact. Now we want to show in the Publications tab of a Contact custom information from the link between a Variant and a Segment. So I need the information from one level up in the relation instead of one level down which is default in Efficy.

Update: Below how I've made the join.
enter image description here

enter image description here

Best regards,
Jeroen

asked in Efficy/ Client side by (314 points)
edited by

1 Answer

0 votes

Hello Jeroen.

There is no such thing als "level up" or "level down" in queries, you just make joins.

If you can design the query in the Queryview builder, it should work. Please check the details of the exception in the Debug.log file generated by Efficy.dll. If the file is not there, enable the debug mode.

We can only trace the source of the issue if we have this information.

answered by (6.8k points)
Hi Kristof,

below is partly the debug information:
== DEBUG REPORT ================================================================
Error Context     : >S1: ExecuteQuery(11, 21)
date/time         : 2018-09-05, 08:57:07, 794ms
computer name     : WIN-JFL
user name         : LOCAL SERVICE
registered owner  : Windows-gebruiker
operating system  : Windows NT New x64 build 9600
system language   : Dutch
system up time    : 22 days 22 hours
program up time   : 2 minutes 2 seconds
processor         : Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz
physical memory   : 84/1280 MB (free/total)
free disk space   : (C:) 3,89 GB
display mode      : 1024x768, 32 bit
process id        : $f8c
allocated memory  : 24,82 MB
command line      : C:\Windows\SysWOW64\dllhost.exe /Processid:{B411F94C-67A0-4A26-BB23-34E0BBFA19F9}
executable        : dllhost.exe
current module    : DSERVE~1.DLL
module date/time  : 2017-06-16 00:00
version           : 10.2.10150.0
compiled with     : Delphi compiler version 30.0
madExcept version : 3.0n
callstack crc     : $6ae88914, $05012970, $05012970
exception number  : 1
exception class   : ESlspException
exception message : QueryBuilder Inconsistent Data Model: cannot find a join between tables "PUBL_PUBL" and "PUBL_PUBL" ErrorContext ============================= EEfficyQueryException 2006 (ENTT-1533) QueryBuilder Inconsistent Data Model: cannot find a join between tables "PUBL_PUBL" and "PUBL_PUBL" Error ============================= ESlspException 0 Application Server Error.

thread $1194:
009b324f +10b DSERVE~1.DLL FrameworkServer TFrameworkManager.HandleSlspException
009c0ce0 +1ac DSERVE~1.DLL FrameworkServer TFrameworkManager.AS_GetRecords
772607fa +00a ntdll.dll                    KiUserExceptionDispatcher
004b7686 +0aa DSERVE~1.DLL System.Classes  TStrings.Assign
00767d38 +0e4 DSERVE~1.DLL NttQueryStore   TQueryStore.BuildAndOpenQuery
009c1bf7 +383 DSERVE~1.DLL FrameworkServer TFrameworkManager.AcquireDataSet
009c139c +020 DSERVE~1.DLL FrameworkServer TFrameworkManager.FrameworkProviderBeforeOperation
009c143a +002 DSERVE~1.DLL FrameworkServer TFrameworkManager.FrameworkProviderBeforeGetRecords
00695ec7 +01b DSERVE~1.DLL RProvider       TCustomProvider.DoBeforeGetRecords
006979fe +042 DSERVE~1.DLL RProvider       TDataSetProvider.DoBeforeGetRecords
0075e855 +055 DSERVE~1.DLL NttSupplier     TTableViewProvider.DoBeforeGetRecords
0069600c +04c DSERVE~1.DLL RProvider       TCustomProvider.DoGetRecords
00695f96 +04a DSERVE~1.DLL RProvider       TCustomProvider.GetRecords
009c0c24 +0f0 DSERVE~1.DLL FrameworkServer TFrameworkManager.AS_GetRecords
74b571f7 +327 RPCRT4.dll                   NdrStubCall2
0040983a +002 DSERVE~1.DLL System          @ClassDestroy
009b2ec0 +01c DSERVE~1.DLL FrameworkServer TFrameworkManager.Destroy
7521a6db +00b user32.dll                   DispatchMessageW
76f27c02 +022 KERNEL32.DLL                 BaseThreadInitThunk

main thread ($158c):
75372cc1 +0b1 KERNELBASE.dll  WaitForSingleObjectEx
75372bfd +00d KERNELBASE.dll  WaitForSingleObject
74c1c3fb +15b combase.dll     CoRegisterSurrogateEx
76f27c02 +022 KERNEL32.DLL    BaseThreadInitThunk

...
So the issue is: QueryBuilder Inconsistent Data Model: cannot find a join between tables "PUBL_PUBL" and "PUBL_PUBL"

In your screenshot, we can see that PUBL_PUBL is not linked to any table. This is likely the source of the issue. This is not supported, each table that is selected should be joined somehow.

Can you correct this and try again?

At least, now you can see the error
I tried to start with the PUBL_PUBL, but then I can't select any fields and the name of the tables are empty.
Re-reading your post, I don't see the links between your segment and variants entities? Or are these just types of publications?

Because, you can't have the same Efficy table multiple times in one system query. This is indeed a limitation.
I don't need the same Efficy table multiple times I only need the relation table PUBL_PUBL but then linked via K_PUBLICATION2 to the PUBLICATIONS table as the Segment-publication is a sub of a Variant-publication. And between this relation is the information that I need to show.
Ok, now I understand the "level up", sorry it took so long...

The link between PUBLICATIONS and PUBL_PUBL is always made with K_PUBLICATION and never with K_PUBLICATION2...

This really looks like a limitation in SYS_RELATIONS, you can only have one way of joining entities. Not sure there is a workaround besides switching from native Efficy query to SQL queries.

So that would be my advise, add a new detail on the consult of a contact "PublJoins" and do a RunQuery of an SQL statement. This will bring some limitation, you can no longer use queryviews and you have to handle the header translations.

Regards,
Kristof
1,167 questions
1,425 answers
1,717 comments
325 users