How do I create a query which uses the logical 'or' of two relations?

(0) Share

Report

Posted on by DD-19021315-0

230

I need to create a query for use by XDS on the VendPackingSlipJour table. The query should filter the records based in whether either the OrderAccount or the InvoiceAccount values are in a given list (stored in a MyParties tempDB table).

Hence the composite relation I'd like is:

VendPackingSlipJour.InvoiceAccount == MyParties.Party OR VendPackingSlipJour.OrderAccount == MyParties.Party

How can I achieve this since there doesn't seem a way to combine relations?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

André Arnaud de Cal... 301,035 Super User 2025 Season 2 on at

Like (0)

Report

Hi Derek,

You need to use the VendTable in between. Then link VendPackingSlipJour with VendTable and MyParties with the VendTable.Party field. For performance considerations, don't include too many fields from the VendTable datasource.

Was this reply helpful? Yes No
Suggested answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

Hi Derek,

Answer to the subject of the question, you can use query expressions to add logical operators in AOT queries which I think you will use in XDS. Example are here msdn.microsoft.com/.../aa893981.aspx

Regards to the query you shared you would need to link vendPackingSlipJour table with vendTable first then use your table MyParties as Andre suggested. I believe you are aware of parties concept in AX since it came in AX 2012. For example, customers, vendors, employees these are all parties in AX and has relation with DirPartyTable in PartyId.

Was this reply helpful? Yes No
DD-19021315-0 230 on at

Like (0)

Report

I think there's a confusion of terminology. The Party field in the tempDB table MyParties is actually populated with an account number (AccountNum) from VendTable, so I don't think I need DirPartyTable. It's called Party to confirm with corporate terminology used elsewhere.

The problem I have is in specify a composite relation where I want to match on either InvoiceAccountNum or OrderAccountNum.

Was this reply helpful? Yes No
DD-19021315-0 230 on at

Like (0)

Report

See my other reply. MyParties.Party is populated from VendTable.AccountNum and isn't a PartyId so I don't think I need to join through VendTable.

Ranges require constant values according to the page referenced so it's not clear to me how to define the join specification as a range since I've trying to compare 2 fields.

Also, the example given is creating the range in X++. I'm trying to restrict the "Product receipt journal" page (Form: VendPackingSlipJour) so hadn't yet looked at adding extensions to customise the page itself. I was trying to do it using simple (or not so simple) queries.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

This is how you do it, you may use Query Expressions in the range for a logical OR operator, between any tables and fields participating within your query. Faisal has linked the MSDN documentation, here is one with a bit more detail. It is NOT X++, you may use such syntax within the range criteria itself:

http://www.axaptapedia.com/Expressions_in_query_ranges

Concentrate on the parts within the braces ((table.fieldA == "value") || (table2.FieldB > dateValue)) and such.

Was this reply helpful? Yes No
DD-19021315-0 230 on at

Like (0)

Report

It seems ranges don't require constants. Simply putting:

((Party == VendPackingSlipJour.InvoiceAccountNum) || (Party == VendPackingSlipJour.OrderAccountNum))

as the 'value' on a range using field Party on my MyParties datasource appears to have the desired effect.

Thanks.

Was this reply helpful? Yes No