Attempting to create a SQL query between Scheduled and Worker

(0) Share

Report

Posted on by Community Member

Within [Entity] > Enterprise asset management > Common > Work Order > All Work Orders

We have a field in the list box called Schedule. This information is pulled from:

MROWorkOrderTable_WorkerScheduled_HcmWorker_DirPerson_FK_Name
DirPartyTable

I am creating a query from the Work Order table (MROWORKORDERTABLE) and I am trying to retrieve this value. I have attempted to join MROWORKORDERTABLE.WORKERSCHEDULED to fields within DIRPARTYTABLE, DIRPERSONUSER and DIRPERSONNAME. I am unable to find the relevant join.

Please can somebody help me.

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

For tables that are extending each other, their RecId is a match. Have a look at the Right click popup on the table in the AX AOT > Add-ins > Table type hierarchy browser to validate which one is extending what record.

Also on the tables you may check the Relations node to find the connections between them.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!

Hi,

As I dont have EAM(Enterprise asset management) ISV solution, couldn't provide the exact relation between MROWORKORDERTABLE to DirPartyTable. But following job will provide you the relation between tables HcmWorker, DirPartyTable, DirPerson, DirPersonName, DirPersonUser and UserInfo(in Ax2012).

static void EAM(Args _args)

{

DirPartyTable dirPartyTable;

DirPersonUser dirPersonUser;

DirPersonName dirPersonName;

DirPerson dirPerson;

UserInfo userInfo;

HcmWorker hcmWorker;

select dirPartyTable

join dirPerson

where dirPerson.RecId == dirPartyTable.RecId

join dirPersonName

where dirPersonName.Person == dirPerson.RecId

join dirPersonUser

where dirPersonUser.PersonParty == dirPerson.RecId

join userInfo

where dirPersonUser.User == UserInfo.id

join hcmWorker // This is the relation referred by MROWorkOrderTable_WorkerScheduled_HcmWorker_DirPerson_FK_Name

where hcmWorker.Person == dirPerson.RecId;

info(strFmt("Name: %1, userId: %2", dirPartyTable.Name, userInfo.id));

}

Hope this information helps you.

Thanks,

Chaitanya Golla

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks for this information.

I am still struggling to join MROWORKORDERTABLE.WORKERSCHEDULED to any of the tables in the function. I have attempted to join MROWORKORDERTABLE.WORKERSCHEDULED to HCMWORKER.PERSON but this is returning no results.

Please can somebody help.

Was this reply helpful? Yes No