CrossJoin

[WitmondPaul]

Hi Bruno,

I've tried to get the CrossJoin function to work but it gives "Bad post content".

In Postman we do this request :

https://api.hanaserver.nl:50000/b1s/v2/QueryService_PostQuery

Body : { "QueryPath":"$crossjoin(Invoices,Invoices/DocumentLines)", "QueryOption":"$expand=Invoices($select=DocEntry, DocNum),Invoices/DocumentLines($select=ItemCode,LineNum)&$filter=Invoices/DocEntry eq Invoices/DocumentLines/DocEntry and (Invoices/DocumentLines/ItemCode eq '10617' and Invoices/DocumentLines/U_StartDate eq '01022023' and Invoices/DocumentLines/U_EndDate eq '28022023' and Invoices/CardCode eq 'zp3')" }

The result is like this :

{ "@odata.context" : "$metadata#Collection(Edm.ComplexType)", "value" : [ { "Invoices" : { "DocEntry" : 19, "DocNum" : 5 }, "Invoices/DocumentLines" : { "ItemCode" : "10617", "LineNum" : 0 } } ], "@odata.nextLink" : "/b1s/v2/QueryService_PostQuery?$expand=Invoices($select=DocEntry, DocNum),Invoices/DocumentLines($select=ItemCode,LineNum)&$filter=Invoices/DocEntry eq Invoices/DocumentLines/DocEntry and (Invoices/DocumentLines/ItemCode eq '10617' and Invoices/DocumentLines/U_StartDate eq '01022023' and Invoices/DocumentLines/U_EndDate eq '28022023' and Invoices/CardCode eq 'zp3')&$skip=1" }

Is this possible to do with B1Sslayer ?

Regards,

Paul

[bgmulinari]

Hi, @WitmondPaul. Yes it's possible and you can do the request in two ways:

Using the QueryService_PostQuery resource like you're doing:

var body = new
{
    QueryPath = "$crossjoin(Invoices,Invoices/DocumentLines)",
    QueryOption = "$expand=Invoices($select=DocEntry, DocNum),Invoices/DocumentLines($select=ItemCode,LineNum)&$filter=Invoices/DocEntry eq Invoices/DocumentLines/DocEntry and (Invoices/DocumentLines/ItemCode eq '10617' and Invoices/DocumentLines/U_StartDate eq '01022023' and Invoices/DocumentLines/U_EndDate eq '28022023' and Invoices/CardCode eq 'zp3')"
};

var result = await serviceLayer.Request("QueryService_PostQuery").PostAsync<List<YourModelClass>>(body);

Or, like this:

var result = await serviceLayer
    .Request("$crossjoin(Invoices,Invoices/DocumentLines)")
    .Expand("Invoices($select=DocEntry, DocNum),Invoices/DocumentLines($select=ItemCode,LineNum)")
    .Filter("Invoices/DocEntry eq Invoices/DocumentLines/DocEntry and (Invoices/DocumentLines/ItemCode eq '10617' and Invoices/DocumentLines/U_StartDate eq '01022023' and Invoices/DocumentLines/U_EndDate eq '28022023' and Invoices/CardCode eq 'zp3')")
    .GetAsync<List<YourModelClass>>();

[WitmondPaul]

Hi Bruno,

Thanks, this is great !

Best regards / Met vriendelijke groet,

Paul Witmond

Planlogic B.V. | Bolderweg 2 | 1332 AT Almere | T. +31 (0)36 3030003| M. +31 (0)627 096 016| KvK 11033102 @.*** Volg ons online: www.planlogic.nlhttp://www.planlogic.nl/ | LinkedInhttps://www.linkedin.com/company/planlogic-b-v- | Facebookhttps://www.facebook.com/PlanlogicNederland/

The contents of this message, as well as any enclosures, are addressed personally to, and thus solely intended for the addressee. They may contain information regarding a third party. Any recipient who is neither the addressee, nor empowered to receive this message on behalf of the addressee, is kindly requested to immediately inform the sender of receipt. Any use of the contents of this message and/or of the enclosures by any other person than the addressee is illegal towards the sender and the aforementioned third party. Always scan attachments before opening them.

Van: Bruno Mulinari @.> Verzonden: vrijdag 14 april 2023 15:07 Aan: bgmulinari/B1SLayer @.> CC: Paul Witmond | Planlogic @.>; Mention @.> Onderwerp: Re: [bgmulinari/B1SLayer] CrossJoin (Issue #36)

Hi, @WitmondPaulhttps://github.com/WitmondPaul. Yes it's possible and you can do the request in two ways:

Using the QueryService_PostQuery resource like you're doing:

var body = new

{

QueryPath = "$crossjoin(Invoices,Invoices/DocumentLines)",

QueryOption = "$expand=Invoices($select=DocEntry, DocNum),Invoices/DocumentLines($select=ItemCode,LineNum)&$filter=Invoices/DocEntry eq Invoices/DocumentLines/DocEntry and (Invoices/DocumentLines/ItemCode eq '10617' and Invoices/DocumentLines/U_StartDate eq '01022023' and Invoices/DocumentLines/U_EndDate eq '28022023' and Invoices/CardCode eq 'zp3')"

};

var result = await serviceLayer.Request("QueryService_PostQuery").PostAsync<List>(body);

Or, like this:

var result = await serviceLayer

.Request("$crossjoin(Invoices,Invoices/DocumentLines)")

.Expand("Invoices($select=DocEntry, DocNum),Invoices/DocumentLines($select=ItemCode,LineNum)")

.Filter("Invoices/DocEntry eq Invoices/DocumentLines/DocEntry and (Invoices/DocumentLines/ItemCode eq '10617' and Invoices/DocumentLines/U_StartDate eq '01022023' and Invoices/DocumentLines/U_EndDate eq '28022023' and Invoices/CardCode eq 'zp3')")

.GetAsync<List<YourModelClass>>()>>

— Reply to this email directly, view it on GitHubhttps://github.com/bgmulinari/B1SLayer/issues/36#issuecomment-1508479105, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A4LY3PDKX2ZQOJJ7EHDI76LXBFDVTANCNFSM6AAAAAAW6HGNKY. You are receiving this because you were mentioned.Message ID: @.**@.>>