Coming soon: Your brand new Help Center & Community! Get a sneak-peek here

Making joins that are not defined in database model with DotSyntax

Hello,

We use the DotSyntac & Dynamic archive provider extensively when building custom apps & integration, a situation we have a lot, especially with the use of extra tables, is that we want to join from an extra table to another table but that relation isn't defined in the database model. The DotSyntax documentation says the following about this:

"Additionally, all join field and table names must be valid, by reference to the runtime dictionary; but note that right-joins as well as left-joins using the square-bracket syntax to specify target table do not need to correspond to relations declared in the dictionary. "

I have tried to to make this work multiple times but have not come up succesfull.

An example:

Extra table name "y_sample', containing the field x_associate_id (not an relation that is possible to defined on an extra table).

We want to join immediately to the associate table to get the name, so we dont need to use a seperate query.

// https://community.superoffice.com/documentation/SDK/SO.NetServer.Data.Access/html/M_SuperOffice_CRM_ArchiveLists_DotSyntaxParser_ConstructExtenderTree.htm
SearchEngine seTest;
seTest.addField("y_sample.id");
seTest.addField("y_sample.x_associate_id");
seTest.addField("y_sample.x_associate_id.(associate->associate_id[associate]).name");

print(seTest.executeHTMLTable());


SearchEngine seTest2;
seTest2.addField("y_sample.id");
seTest2.addField("y_sample.x_associate_id");
seTest2.addField("y_sample.x_associate_id.(associate->associate_id).name");

print(seTest2.executeHTMLTable());

Could an example be provided how to join a table using an relation which isn't defined in the database model?

RE: Making joins that are not defined in database model with DotSyntax

Hi,

Have you had any success in trying to do this? I am looking to do something similar but have also not managed it.

By: Rhodri Blackwell 22 Mar 2021

RE: Making joins that are not defined in database model with DotSyntax

Hi,

No I haven't found the solution to this, I hoped someone from R&D could point me in the right direction :)

By: David Hollegien 22 Mar 2021