Microsoft Dynamics AX (Archived)

X++ Count Distinct CustAccount

(0) Share

Report

Posted on by HAIRUL HAZRI

1,214

These are the tables I'm working on:

DriverShipTable

-SeqNo int

DriverShipTableDet

-SeqNo int

-InvoiceNo

CustInvoiceJour

-InvoiceId str

-CustAccount str

Table relationships:

DriverShipTable.SeqNo = DriverShipTableDet.SeqNo

DriverShipTableDet.InvoiceNo = CustInvoiceJour.InvoiceId

My goal is to create a view with a column that counts the total of CustInvoiceJour.CustAccount Group By DriverShipTable.SeqNo.

So, I'm writing an X++ method inside the CustInvoiceJour Table as following:

public display int customerCount()
{

DriverShipTableDet driverShipTableDet;
DriverShipTable driverShipTable;

select *
from driverShipTableDet
where driverShipTableDet.InvoiceNo == this.InvoiceId
join driverShipTable
where driverShipTable.SeqNo == driverShipTableDet.SeqNo;

custCount =
(select count (this.InvoiceAccount) //........SYNTAX ERROR HERE........
group by driverShipTable.SeqNo;)

return custCount;

}

Will you please help me correct my syntax? I'm totally new with X++ so I'm very confused with how to properly using values or calling existing table methods. Any kind of guidance will be appreciated.

Thank you very much.

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (2)

Suggested answer

Rohin 4,624 on at

Like (1)

Report
Please try below code:

Public display int customerCount() { DriverShipTableDet driverShipTableDet; DriverShipTable driverShipTable; ; while select count(custAccount) from driverShipTableDet where driverShipTableDet.InvoiceNo == this.InvoiceId join driverShipTable group by driverShipTable.SeqNo where driverShipTable.SeqNo == driverShipTableDet.SeqNo { return driverShipTableDet.custAccount; } }

For more information about select statement , please refer Select Statement Syntax [AX 2012]

Was this reply helpful? Yes No
HAIRUL HAZRI 1,214 on at

Like (0)

Report

Hi Visvash, thanks for replying.

I wrote this method in CustInvoiceJour table. CustAccount column is in CustInvoiceJour table. So this statement if throwing error "The table DriverShipTableDet does not contain the field CustAccount."

I'm trying to grab the CustAccount column by using this.CustAccount like below:

while select count(this.CustAccount) from driverShipTableDet //syntax error here

where driverShipTableDet.InvoiceNo == this.InvoiceId

join driverShipTable group by driverShipTable.SeqNo

where driverShipTable.SeqNo == driverShipTableDet.SeqNo

{

return driverShipTableDet.custAccount;

}

And it throws syntax error. Why can't I use "this" on select but it is allowed on

"where driverShipTableDet.InvoiceNo == this.InvoiceId"?

How to fix this? Thank You.

Was this reply helpful? Yes No
Suggested answer

Rohin 4,624 on at

Like (1)

Report

Well, The error "The table DriverShipTableDet does not contain the field CustAccount" is pretty clear which means custAccount field is not exist in DriverShipTableDet. So please go to AOT > Tables > DriverShipTableDet> field Node and look for custAccount if it exist there or not or might be exist with different name. Then use that field in your code with right name.

Secondly , you can't use "this" for getting fields or methods of DriverShipTableDet table on custInvoiceJour Table because "this" reference to current object (i.e. custInvoiceJour in your case). Please refer to documentation X++ Keywords[AX2012] for more details

Was this reply helpful? Yes No
HAIRUL HAZRI 1,214 on at

Like (0)

Report

Yes I'm fully aware that CustAccount is not in DriverShipTableDet table because the field is in CustInvoiceJour table. So I tried something like this instead:

DriverShipTableDet driverShipTableDet;

DriverShipTable driverShipTable;

CustInvoiceJour custInvoiceJour;

int counter;

;

counter =

(SELECT count

(CustAccount)

from custInvoiceJour

where custInvoiceJour.InvoiceId == this.InvoiceId

join driverShipTableDet //*****The join keyword is illegal in this context.*****//

where driverShipTableDet.InvoiceNo == custInvoiceJour.InvoiceId

join driverShipTable group by driverShipTable.SeqNo

where driverShipTable.SeqNo == driverShipTableDet.SeqNo);

return counter;

And it's throwing join illegal error as on the line stated. What did I do wrong? And the method is supposed to return the counter which is an integer value.

Thank You.

Was this reply helpful? Yes No
Suggested answer

Rohin 4,624 on at

Like (1)

Report
Try this one:

while Select count(custAccount) from CustInvoiceJour join DriverShipTableDet where driverShipTableDet.InvoiceNo == this.InvoiceId join driverShipTable group by driverShipTable.SeqNo where drivershipTable.SeqNo == driverShipTableDet.SeqNo { return this.custAccount; }

Was this reply helpful? Yes No
HAIRUL HAZRI 1,214 on at

Like (0)

Report

Hi Visvash,

while Select count(custAccount) from CustInvoiceJour

join DriverShipTableDet where driverShipTableDet.InvoiceNo == this.InvoiceId

join driverShipTable group by driverShipTable.SeqNo

where drivershipTable.SeqNo == driverShipTableDet.SeqNo

{

return this.custAccount; //The operand is not compatible with the type of the method.

}

It's throwing "The operand is not compatible with the type of the method". custAccount is string data type. Like I mentioned above, the method is supposed to return the count, which is integer.

Thank You.

Was this reply helpful? Yes No
Suggested answer

Rohin 4,624 on at

Like (1)

Report

sorry I haven't see return type. Then use this.

Was this reply helpful? Yes No
HAIRUL HAZRI 1,214 on at

Like (0)

Report

I don't understand. Do you mean to use it like this?

while Select count(InvoiceAccount) from CustInvoiceJour

join DriverShipTableDet where driverShipTableDet.InvoiceNo == this.InvoiceId

join driverShipTable group by driverShipTable.SeqNo

where drivershipTable.SeqNo == driverShipTableDet.SeqNo

{

return str2Int(this.InvoiceAccount);

}

This throws "Method never returns a value."

Thank You

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (2)

Report

Doing a while select and then interrupting execution with a return keyword straight away when the first record is fetched does not make sense. Also returning the current object's CustAccount field value within does not make sense again.

I would recommend to familiarize yourself with the select statements by reading all the sub-pages here:

msdn.microsoft.com/.../aa845764.aspx

Reading books on AX development would also be helpful to understand generic programming concepts which you are obviously missing, before trying to implement something which you do not fully understand.

Also your last piece of code is missing a global return at the end of the method, like the error message clearly states that you do not have a return value set.

Was this reply helpful? Yes No
Verified answer

Rohin 4,624 on at

Like (1)

Report
declare a variable and assign it the value in your while loop and return it outside the while loop.

int count; while Select count(InvoiceAccount) from CustInvoiceJour join DriverShipTableDet where driverShipTableDet.InvoiceNo == this.InvoiceId join driverShipTable group by driverShipTable.SeqNo where drivershipTable.SeqNo == driverShipTableDet.SeqNo { count = str2Int(this.InvoiceAccount); } return count;

I would recommend you to follow Vilmos comments.

Was this reply helpful? Yes No

Community site session details

X++ Count Distinct CustAccount

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

X++ Count Distinct CustAccount

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates