Finance | Project Operations, Human Resources, ...

Lookup method (search last name but return position)

(3) Share

Report

Posted on by CU29041349-0

444

Hello:

I have a control on a custom form which I have a lookup method attached to return a select position Id. It works fine as-is. Clicking on the dropdown, I see the position Id, the worker first name, the worker last name, and I can filter on the individual fields. If I tab into the field, and start typing, I have to search based on the position Id. For efficiency sake, what I would like to do is as my user tabs through the fields on this form, when they get to the position they can type in the last name to filter the list, then select the appropriate record which should return the position id to my code rather than the last name.

What I've tried is changing the order of the fields so last name is first on the field selection. Typing in the last name now would filter the list as I want, but upon selecting the record, I get an error indicating no such position exists.

Hoping for some direction.

Edit:

The current code I'm using...

public void lookup()
{
Query query = new Query();
QueryBuildDataSource qbdsPSHcmPosWrkrView;
utcdatetime currDateTime = HcmDateTimeUtil::startOfCurrentDay();

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(psHcmPositionWorkerView), this);
sysTableLookup.addLookupField(fieldNum(psHcmPositionWorkerView, PositionId));
sysTableLookup.addLookupField(fieldNum(psHcmPositionWorkerView, Description));
sysTableLookup.addLookupField(fieldNum(psHcmPositionWorkerView, PersonnelNumber));
sysTableLookup.addLookupField(fieldNum(psHcmPositionWorkerView, FirstName));
sysTableLookup.addLookupField(fieldNum(psHcmPositionWorkerView, LastName));

qbdsPSHcmPosWrkrView = query.addDataSource(tableNum(psHcmPositionWorkerView));
qbdsPSHcmPosWrkrView.addOrderByField(fieldNum(psHcmPositionWorkerView, PositionId));

qbdsPSHcmPosWrkrView.addRange(fieldNum(psHcmPositionWorkerView, DirPersonName_ValidFrom)).value(queryRange(null, currDateTime));
qbdsPSHcmPosWrkrView.addRange(fieldNum(psHcmPositionWorkerView, DirPersonName_ValidTo)).value(queryRange(currDateTime, null));

qbdsPSHcmPosWrkrView.addRange(fieldNum(psHcmPositionWorkerView, HcmPositionWorkerAssignment_ValidFrom)).value(queryRange(null, currDateTime));
qbdsPSHcmPosWrkrView.addRange(fieldNum(psHcmPositionWorkerView, HcmPositionWorkerAssignment_ValidTo)).value(queryRange(currDateTime, null));

qbdsPSHcmPosWrkrView.addRange(fieldNum(psHcmPositionWorkerView, HcmPositionDetail_ValidFrom)).value(queryRange(null, currDateTime));
qbdsPSHcmPosWrkrView.addRange(fieldNum(psHcmPositionWorkerView, HcmPositionDetail_ValidTo)).value(queryRange(currDateTime, null));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

//super();
}

Categories:

Dynamics 365 Finance

All responses (8)

Answers (0)

CU29041349-0 444 on at

Like (0)

Report

Lookup method (search last name but return position)

Thank you, Martin, for the lead. My priorities shifted a little so I'll have to come back to this a bit later, but I will follow up. I much appreciate your help.
Martin Dráb 233,013 Most Valuable Professional on at

Like (1)

Report

Lookup method (search last name but return position)

Saif, please stop spamming this forum with completely useless answers copied from a chat bot. Your last one is again completely meanigless - not just that it ignores the actual question, but it propose something that make no sense at all. It's just AI hallucinating and you blindly coping its response without thinking.

The value of such replies isn't just none - it's negative. People might thing you're trying to really help and will spend time before they find that the answer is rubbish. And real replies become buried among walls us AI-generated texts. This makes the forum less and less useful.
Suggested answer

Martin Dráb 233,013 Most Valuable Professional on at

Like (0)

Report

Lookup method (search last name but return position)

I don't think that any of the replies are anywhere close to the actual solution. What you actually need to look at is Contextual data entry for lookups.

Waed's suggestion would return an invalid value.

Saif's original reply is just AI hallucination. The attempt to fix it by using parmUseLookupValue(true) is wrong too. The value already is true, therefore setting it to true once more has no effect at all. You'd get the behaviour you already have.
Suggested answer

Saif Ali Sabri 1,932 Super User 2025 Season 1 on at

Like (0)

Report
Lookup method (search last name but return position)

Since you're using a view and encountering issues with parmReturnField(), you're likely working within the constraints of FormStringControl.lookup() instead of using SysTableLookup, which doesn't support parmReturnField() in that context.
Here’s how to handle this correctly when using a view and needing to search by last name but return position ID:

✅ Full Working Solution Using FormControl.lookup()
Assuming your view (e.g. MyPositionWorkerView) includes PositionId, FirstName, and LastName, and you want to:

Show all three in the dropdown

Allow filtering by LastName

Return PositionId when a record is selected

Step-by-step Implementation:
1. Override lookup() on the control:
x++
CopyEdit
public void lookup()
{
    Query query = new Query();
    QueryBuildDataSource qbds;

    qbds = query.addDataSource(tableNum(MyPositionWorkerView));
    qbds.addSortField(fieldNum(MyPositionWorkerView, LastName), SortOrder::Ascending);

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(MyPositionWorkerView), this);

    // Add fields to display in the lookup
    sysTableLookup.addLookupField(fieldNum(MyPositionWorkerView, PositionId));
    sysTableLookup.addLookupField(fieldNum(MyPositionWorkerView, FirstName));
    sysTableLookup.addLookupField(fieldNum(MyPositionWorkerView, LastName));

    // Set PositionId to be the value returned
    sysTableLookup.parmUseLookupValue(true); // Needed to return selected field value

    // Set the query
    sysTableLookup.parmQuery(query);

    // Display the lookup
    sysTableLookup.performFormLookup();
}

2. Ensure Control is Bound Correctly

The control must be bound to a PositionId field on your form’s data source.

If you're using an unbound control, you’ll need to manually set the selected PositionId in modified() or lookupReference().

3. Optional: Override modified() to set selected PositionId if needed
If not auto-bound:
x++
CopyEdit
public boolean modified()
{
    boolean ret;
    MyPositionWorkerView viewRec;

    ret = super();

    viewRec = MyPositionWorkerView::find(this.text()); // Or use appropriate find logic

    if (viewRec)
    {
        // Manually set PositionId to your data source
        YourDataSource.PositionId = viewRec.PositionId;
    }

    return ret;
}

⚠️ Key Notes:

parmReturnField() is only available in FormReferenceGroupControl, not in SysTableLookup. For SysTableLookup, the field you bind the control to is the return field.

If the control is unbound, you must handle the return manually via modified() or lookupReference().
CU29041349-0 444 on at

Like (0)

Report

Lookup method (search last name but return position)

Hi Saif,

Sorry for the late response.

I included my code in the post. I'm using a view as the basis for the lookup, but otherwise, it's inline with your response. Also, parmReturnField() doesn't appear to be a valid method. I've check the control properties as well. Any ideas?
CU29041349-0 444 on at

Like (0)

Report

Lookup method (search last name but return position)

Hi Waed, I've updated the post with the code snippet.
Suggested answer

Waed Ayyad 8,287 Super User 2025 Season 1 on at

Like (0)

Report
Lookup method (search last name but return position)

Hi @CU29041349-0,

Can you show us your code?

In general, you can change the first field on the lookup as below

sysTableLookup.addLookupfield(fieldNum(YourTable, lastName), true);

Thanks,

Waed Ayyad

If this helped, please mark it as "Verified" for others facing the same issue
Suggested answer

Saif Ali Sabri 1,932 Super User 2025 Season 1 on at

Like (0)

Report

Lookup method (search last name but return position)

To achieve filtering by last name while still returning the Position ID, you need to override the lookup method and set a custom SysTableLookup. You must also set the returnField to Position ID, even though the filtering is based on last name.
Here's a concise solution:
1. Override the lookup method on the control
x++ CopyEdit
public void lookup()
{
    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(HcmPositionWorker), this);
    Query                   query = new Query();
    QueryBuildDataSource   qbds;

    qbds = query.addDataSource(tableNum(HcmPositionWorker));
    qbds.addSortField(fieldNum(HcmPositionWorker, Position), SortOrder::Ascending);

    // Add fields to show in the lookup
    sysTableLookup.addLookupField(fieldNum(HcmPositionWorker, Position));
    sysTableLookup.addLookupField(fieldNum(HcmPositionWorker, FirstName));
    sysTableLookup.addLookupField(fieldNum(HcmPositionWorker, LastName));

    // Set Position as return field
    sysTableLookup.parmReturnField(fieldNum(HcmPositionWorker, Position));

    // Apply the query
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}
2. Set AutoDeclaration to Yes for the control and make sure it's bound to the Position ID field.
3. Ensure that the control is of a Reference Group or similar input type that allows lookups.
With this, typing a last name will filter the lookup results, but selecting a record will still return the Position ID.
Let me know if you're using a different table setup or if you need this in a form extension or a form control event.