LOGISTICPOSTALADDRESS and SALESTABLE relation

(0) Share

Report

Posted on by Community Member

Hello all,

I have a little problem. I need to make a query in SQL to show all the invoices that have been delivered to a country not UK.

So, I need the field LogisticPostalAddress.CountryRegionId.

The problem is that I can't find the relation between this table and salestable or CustinvoiceJour. At first, I tought that one relation could be LOGISTICSPOSTALADDRESS.LOCATION = CUSTINVOICEJOUR.DELIVERYPOSTALADDRESS but the data that shows is not the correct, so the link is not the correct one.

Did you used this relation already? Can you tell me the correct relation?

Thank you very much!

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

CustInvoiceJour.DeliveryPostalAddress contains RecIds (not Locations) of LogisticsPostalAddress. Look at the Relations node in AOT next time to find this information.

Also note that accessing AX database directly is highly discouraged - that you can't simply set Relations = Yes as with AX queries is just one of many reasons.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, of course, that contains RecIds, I use this in the inner join of my query to relate the LogisticsPostalAddress to CustinvoiceJour.

Then, I list the LogisticPostalAddress.CountryRegionId that is the field that I need.

But this is not the correct relation because the result that I get is not the correct one.

I'm going to see the relations in the AOT node to see if I can found the correct relation between this two tables.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Well, I found the relation not to custinvoicejour but with salestable, but the link between those two is easy. In case anyone wants to know, the correct relation is:

LOGISTICSPOSTALADDRESS.RECID = SALESTABLE.DELIVERYPOSTALADDRESS

Thanks for the help!

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

It *is* the correct as defined by Microsoft. You either have corrupt data in your database or you're doing something wrong. In either case, stop looking for a *more correct* relation and start dealing with the actual problem.

Was this reply helpful? Yes No
Suggested answer

Kaustav Chakrabarty 60 on at

Like (1)

Report

Hi Gonzalo,

If you have not the privilege to check the existing relation from AX application end, you can check the below link, where Microsoft already prepared all the relations. But the list is based on Microsoft Dynamics AX 2012 R2 (not for the latest R3).

www.microsoft.com/.../ax2012r2

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

Note that using SalesTable doesn't meet your requirement ("to show all the invoices"). There may not be any order anymore for an invoice, therefore such an invoice won't be covered by your query.

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (1)

Report

Hi Gonzalo, Hi All,

irrespective of all other answers already done here you should consider and keep in mind the following:

LogisticsPostAddress is a "ValidTimeState" table. If you select (without modifications) on this table you won't get a result, even if the record is available, when the currentDateTime (standard) is ouside the range within that record.

If you use "old" references (refRecIds to currently not valid records) to such PostalAdresses you should extend the selects to this table by a validTimeState(datetmin, datemax) clause or use the validTimeStateDateRange method on the query.

regards

Douglas

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the remark Martin,

There is two solutions for that, the field salestable.status or doing an inner join to the custinvoicejour table where all the sales orders invoiced are.

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

How would you check a status on sales order that's been already deleted? And how would you use in a join?

Orders can be deleted after invoicing, because they're not needed anymore.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Agree, but is not my case :)

That's why I didn't care about using custinvoicejour or salestable. I can use both for this case

Was this reply helpful? Yes No