Finance | Project Operations, Human Resources, ...

How to get primary active customer postal addresses?

(0) Share

Report

Posted on by junior AX

1,552

So if you go to customers in D365, addresses tab - Advanced

you'll be able to see active or history addresses

so right now i have active and history addresses that are all primary.

So if i do the following: i will get 4 primary addresses even though i only have one primary that is active

select  *from CUSTINVOICEJOUR
    inner join CUSTTABLE
        on CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT
    inner join DIRPARTYLOCATION
        on DIRPARTYLOCATION.PARTY = CUSTTABLE.PARTY
    inner join LOGISTICSPOSTALADDRESS
        on  LOGISTICSPOSTALADDRESS.LOCATION = DIRPARTYLOCATION.LOCATION
where CUSTTABLE.DATAAREAID = 'usmf'  and DIRPARTYLOCATION.ISPRIMARY=1  and CUSTINVOICEJOUR.DATAAREAID = 'usmf'
    and CUSTINVOICEJOUR.INVOICEID = 'CIV-000001' and CUSTINVOICEJOUR.ORDERACCOUNT = 'de-001'

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi JuniorAx,

There is a method postalAddress on CustTable record, which will return your primary postal address. Do you think you can use it?

Was this reply helpful? Yes No
MATTGUO 22,349 Moderator on at

Like (0)

Report

Add the validto field to filter the active record.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Sergie and Matgguo,

i'm doing this join using data entities, so i'm afraid i can't use the method. So i should put range in the property and say where validTo >= today() ? or what exactly?

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi JuniorAX

Please refer to the documentation on how to make queries for Valid time state table. Also, take into account that LogisticsPostalAddress has time UtcDateTime, so you need to use DateTimeUtil::utcNow() to get the current date and time.

docs.microsoft.com/.../effects-of-valid-time-state-tables-on-read-and-write-operations

Was this reply helpful? Yes No
Suggested answer

Rahul Mohta 21,032 on at

Like (0)

Report

could try data entity 'customer postal address'

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi sergie,

I think the link didn't help me in data entities. But i tried to put this range logistics postal address validTo field : >= DateTimeUtil::utcNow() but it didn't work

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Rahul,

this one has alot of joins is there any way to do it with custInvoiceJour,DirPartLocation and logisticsPostsalAddress like i showed above?

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Junior_AX,

There is property on entity itself "Valid time state enabled". You can try to set it to Yes.

Also you can fetch address via LogisticsPostalAddressBaseEntity, check how it's done for CustCustomerV3Entity (DirPartyBaseEntity->LogisticsPostalAddressBaseEntity)

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Sergie,

I edited the property to yes and i added validFrom field to the entity key because i got an error that i should add either valid from or valid to.

i also added this validFrom to the dataEntity Relation in staging table. but i still got all addresses. What am i missing?

i saw in the data entity u suggested they also used effective field which is validFrom in the entity key.

btw shouldn't it better to use the 4 tables i made in the data entity instead of the standard one because the standard ones has alot of datasources?

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (1)

Report

Hi Junior Ax,

How did you test the entity?

LogisticsPostalAddressBaseEntity itself is a quite light entity and for valid time state entity, you will need to separate address and invoice information, I'm not sure how the entity will behave if you don't have an active address and the whole entity is set as a valid time state is activated.

Was this reply helpful? Yes No