Cannot retrieve customer primary address values in form

(4) Share

Report

Posted on by fspafj

247

I am new to X++ and trying to build a new form which shows related data of customer, including the primary address info of the customer.

In the new form, I added 3 data sources and defined their properties as follows:

1. CustTable; Join Source: blank; Link Type: blank

2. DirPartyTable; Join Source: CustTable; Link Type: Inner Join

3. LogisticsPostalAddress; Join Source: DirPartyTable; Link Type: Outer Join

I override these data sources's init() to define the relationship between them

[DataSource]
class DirPartyTable
{
public void init()
{
super();

QueryBuildDataSource qbdsCustTable,qbdsDirPartyTable;
qbdsCustTable = CustTable_ds.query().dataSourceTable(tableNum(CustTable));
qbdsDirPartyTable = qbdsCustTable.addDataSource(tableNum(DirPartyTable));
qbdsDirPartyTable.addLink(fieldNum(CustTable, Party), fieldNum(DirPartyTable, RecId));
qbdsDirPartyTable.joinMode(JoinMode::InnerJoin);

}

[DataSource]
class LogisticsPostalAddress
{
public void init()
{

super();

QueryBuildDataSource qbdsDirPartyTable,qbdsLogisticsPostalAddress;
qbdsDirPartyTable = DirPartyTable_ds.query().dataSourceTable(tableNum(DirPartyTable));
qbdsLogisticsPostalAddress = qbdsDirPartyTable.addDataSource(tableNum(LogisticsPostalAddress));
qbdsLogisticsPostalAddress.addLink(fieldNum(DirPartyTable, PrimaryAddressLocation),fieldNum(LogisticsPostalAddress, Location));
qbdsLogisticsPostalAddress.joinMode(JoinMode::OuterJoin);

}

Then I put the fields of DirPartyTable and LogisticsPostalAddress to the form. When I opened the form, I can see the value of DirPartyTable fields but the address fields are blank. Could someone tell me where was wrong?

Categories:

Dynamics 365 Supply Chain Management

All responses (8)

Answers (0)

fspafj 247 on at

Like (0)

Report
Cannot retrieve customer primary address values in form

I was able to get the fields of child data source into the form by defining parent data source in Join Source property then overriding the init() method of the child data source.

QueryBuildDataSource qbds = this.QueryBuildDataSource(); qbds.clearLinks(); qbds.addLink(fieldNum(ParentTable, ParentField), fieldNum(ChildTable, ChildField), ParentTable_ds.name());
Martin Dráb 230,934 Most Valuable Professional on at

Like (0)

Report

Cannot retrieve customer primary address values in form

It looks very suspicious indeed. I think it's the cause.
fspafj 247 on at

Like (0)

Report

Cannot retrieve customer primary address values in form

Hi Martin,

Thanks for your suggestion.

I checked the SQL clause again and the DataAreaId and Partition of the record should be the same with the record in GUI

My next step would be checking the actual query string used by the form. It sometimes reveal unexpected ranges, dynamic links or wrong conditions.

-> I checked the actual query statement of the controls on the form and it looks something like this:

SELECT FIRSTFAST FORUPDATE * FROM SalesTable(SalesTable)
USING INDEX SalesIdx WHERE ((SalesType = 0))
OUTER JOIN FORUPDATE * FROM CustTable(CustTable_CustAccount)
ON SalesTable.CustAccount = CustTable.AccountNum
OUTER JOIN FORUPDATE * FROM DirPartyTable(DirPartyTable_CustAccount)
ON CustTable.Party = DirPartyTable.RecId
OUTER JOIN FORUPDATE FROM LogisticsPostalAddress(LogisticsPostalAddress_CustAccount)
ON DirPartyTable.PrimaryAddressLocation = LogisticsPostalAddress.Location
OUTER JOIN FORUPDATE Description FROM LogisticsLocation(Ref_LogisticsLocation_LogisticsLocation_PrimaryAddress)
ON DirPartyTable.PrimaryAddressLocation = LogisticsLocation.RecId

I tried comparing with other standard form control's queries and saw that there is no field on the join clause of LogisticsPostalAddress. Could it be the reason why I can't see the values on form?

OUTER JOIN FORUPDATE FROM LogisticsPostalAddress(LogisticsPostalAddress_CustAccount)
Martin Dráb 230,934 Most Valuable Professional on at

Like (0)

Report

Cannot retrieve customer primary address values in form

Your SQL code ignores DataAreaId (and Partition), which may influence your results. You may be getting data from other companies than in GUI (if there us a customer with the same ID in another company). Please either follow my advice or add a filter of a.DataAreaId. And if you want to be completely correct, add Partition to all join conditions and filter by it (there may be records in other partitions created by automated tests). Writing SQL code simulating what F&O does isn't as simple as it might look.

In general, there are two possible explanations: the data is filtered out by the form query or the query fetches the data but it's not displayed correcly on the form. Both can be tested, but the problem is more likely in the query.

My next step would be checking the actual query string used by the form. It sometimes reveal unexpected ranges, dynamic links or wrong conditions.
fspafj 247 on at

Like (0)

Report

Cannot retrieve customer primary address values in form

Hi Martin,

I checked with a customer primary address and they should be valid. But like other addresses, it was not displayed in the form
Martin Dráb 230,934 Most Valuable Professional on at

Like (0)

Report

Cannot retrieve customer primary address values in form

You can't have the same query with a different result. Most likely, your SQL query isn't really the same. For example, maybe you forgot to apply ranges for ValidFrom and ValidTo fields, as F&O does.

Let's focus on a single customer. Find the DirPartyTable record and its PrimaryAddressLocation field value. Now find record(s) in LogisticsPostalAddress, look at their values and try to determine why they're filtered out by your form query. First of all, check ValidFrom and ValidTo fields.
fspafj 247 on at

Like (0)

Report

Cannot retrieve customer primary address values in form

Hi Martin,

Thanks for your response.

So I removed all the code in the init() method of the data sources and keep data sources properties. However the LogisticsPostalAddress fields are still blank for all customers while they have active primary postal addresses. I did the same query in SQL and it worked fine.

Here is my data source properties:

Are you using fields that are populated in the corresponding records in LogisticsPostalAddress?
-> Im not sure if I understand you correctly but I dragged the fields from LogisticsPostalAddress data source directly into my form.
Martin Dráb 230,934 Most Valuable Professional on at

Like (0)

Report

Cannot retrieve customer primary address values in form

Throw away all your code, because it's wrong and it's not needed at all. You already created the data sources in form's metadata and there is a QueryBuildDataSource object for each of them. You're adding additional data sources by calling addDataSource(), and you add links to those query data sources created at runtime, not those used by your form data sources. If you needed to make some changes to those data sources in code, you should get references to existing ones by dataSourceTable(), not to create new ones.

So, you don't need code to add data sources. You don't need to set JoinMode either, because it's already done in data sources' properties. You don't need to add a links between tables, because they're created automatically based on existing table relations. Which makes every line of your code obsolete.

Regarding your problem with empty address fields, doesn't it happen to all customers? Do they have postal address valid today? Are you using fields that are populated in the corresponding records in LogisticsPostalAddress? Note that we don't have access to your data and we don't know details of your implementation, therefore our options are limited, but you can do more to analyze the problem.