Announcements
Hello Team,
I have a custom table "TransformationAssociate" where I want two fields as "Timesheet Approver" and "Expense Approver" as lookup columns. I want to create a relation with HCMEmployment Table which has a field "worker" which contains all the Employee name. I tried to add my custom fields same way but it is not giving me a lookup column. Why is this happening? Can you pls help me resolve this issue.
HcmEmployment Table:
My custom table:
Hi skd,
in your screenshot I don't see any lookup. A lookup is a drop down list that can be shown when user edits a field. What we are seeing is the table browser, and in the upper screenshot it automatically replaces the RefRecId and shows the replacement value from the related table.
Looking at your screenshot, it's not possible to know if something is wrong or not. If your TimeSheetApprover field doesn't have any value (=value is 0) then the table browser would look like your screenshot.
You can use SQL Server Management Studio to see what values you actually have in that field.
Could you also let me know if I didn't understand your question correctly. Thanks!
Hi Nikolaos Mäenpää ,
Actually, you are somewhat right. My issue is that I want to get all the names in the dropdown list of my custom field which are in the HCmEmployement Table. It should be a pure lookup field. I tried creating the relation same way as that in the HCmEmployement Table. So, can you please tell me how can I convert my field into a pure lookup column with the Names of the workers from HCmEmployement Table.
This is done on the form level, by using a Replacement group. Please check some form that shows lookup for HcmEmployment.Worker and you will learn how to implement it.
Or please check Martin's blog: community.dynamics.com/.../replacement-groups-in-ax-2012
Why are you creating a relation with HcmEmployment and not HcmWorker? I think yhou should create a FK relation with HcmWorker.
Hi Nikolaos Mäenpää,
Thank you I will look into this blog. Right now, I added this field into my form and changed the lookup property it is working fine , just wanted to ask you can't we create same relation for ExpenseApprover using RECID.
EG: Tranformation.ExpenseApprover==HCmWorker.RecId.
Because when I'm trying I don't see RecID option in my dropdown to add a relation
Then perhaps the data type of your ExpenseApprover is not correct. It should be RefRecId or HcmWorkerRecId.
What is it?
Yes, you are right now it's visible thank you so much.
Hey Nikolaos Mäenpää,
I want to ask you something, I'm creating this lookup to get all the active workers in the lookup i.e. those workers who have a "ValidTo" value as "Never". So how can I get this kind of filtered data in my lookup field from the HcmEmployement table where "ValidTo" field for the Employees have a value as "Never". Please help me with this issue.
I believe that should work automatically. By default, when you query ValidTimeState tables, only valid records are returned. But in this case your table relation must of course point to HcmEmployment.Worker, not HcmWorker.RecId.
You can use the HcmWorkerLookup form for this. Please check the method lookupReference under Forms\SalesTable\DataSources\Fields\WorkerSalesResponsible.
public Common lookupReference(FormReferenceControl _formReferenceControl) { return HcmWorkerLookup::newOnlyActiveWorkers().lookupWorker(_formReferenceControl); }
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our community stars!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
André Arnaud de Cal... 396 Super User 2026 Season 1
Subra 323
Martin Dráb 243 Most Valuable Professional
