Finance | Project Operations, Human Resources, ...

How to dynamically add relations on form datasource in X++?

(0) Share

Report

Posted on by Shetu Raj Microsoft Employee

I want to dynamically add a foreign key relation to the datasource in my form so that I get the values from that foreign key as a dropdown in one of my form fields.

So there is field A, which is of type enum and has 2 options:- Text(default) and List. There is another field B whose value depends on value selected in field A. Whenever user selects List in field A, a dropdown should occur in field B whereas when Text is selected in field A, then field B should become a simple text field.

I implemented this manually by adding a relation explicitly on the form datasource and it works fine when I select List on field A ( a dropdown appears which contains list of values from the foreign key) but when I select Text again on field A the dropdown still appears on field B which means the control(relation) did not get cleared which is not what I want. So to do it dynamically, I am using modify() method for that form field (field A).

Here is a snippet of my code:-

public boolean modified()
{
    boolean ret;

    ret = super();
    
    if(Field_A::List)
    {
        QueryBuildDataSource qbds;
        RelatedTable relatedtableobject;
        FormDataSource formDataSource = FormDataSourceTable.dataSource();
        qbds = formDataSource.query().dataSourceTable(tablenum(FormDataSourceTable));
        qbds.addSelectionField(fieldnum(FormDataSourceTable, Field_A));
        qbds = qbds.addDataSource(tablenum(RelatedTable));
        qbds.relations(true);
        qbds.addForeignkeyRelation('FormDataSourceTable','RelatedTable');
        qbds.addDynalink(fieldnum(FormDataSourceTable,ForeignKeyField), relatedtableobject, fieldnum(RelatedTable, Field_B));
    }
    return ret;
}

I am using querybuilddatasource and dynalink to create relations. FormDataSourceTable is the data source table in my form and RelatedTable is the table which I need to add relation to with my form datasource for referencing foreign key.

But I am getting below error:-

The specified data source cannot be found.

on this line:-

qbds.addForeignkeyRelation('FormDataSourceTable','RelatedTable');

Basically I want the relation to be applied dynamically only when List is selected and for Text the relation should be cleared. Can anyone suggest a better way to do this since I am new to x++?

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Komi Siabi 13,218 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi Shetu,

From what you describe, you want to pick related record as a dropdown on Field B when you populate Field A.

If this is right, you can achieve this by using the lookup reference functionality.

Here is a sample code I used just few days ago.

InventJournalTable inventJournalTable;
LedgerJournaltable LedgerJournalTable;

FormControlCancelableSuperEventArgs ce = e as FormControlCancelableSuperEventArgs;

ledgerJournalTrans ledgerJournalTrans = sender.dataSourceObject().cursor() as ledgerJournalTrans; //Datasource of the field

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(inventJournalTable), sender); //Table that contain the values you want to lookup
Query query = new Query();

query.addDataSource(tableNum(inventJournalTable)).addRange(fieldNum(inventJournalTable,Customer )).value(ledgerJournalTrans.accountDisplay());

sysTableLookup.addLookupfield(fieldNum(inventJournalTable, JournalId));
sysTableLookup.parmQuery(query);
if(ledgerJournalTrans.accountDisplay() != "") //Ensure lookup can only be done when the field required for the lookup is not blank
{
sysTableLookup.performFormLookup();
ce.CancelSuperCall();
}

Was this reply helpful? Yes No
Suggested answer

BrandonSA 1,673 on at

Like (0)

Report
Copy link

Link copied!

Hi Shetu

Usually you'd say: qbds.addlink(fieldnum(table1, field1), fieldnum(table2, field2)

However, your solution looks a little strange. Could you clarify a little better what you're trying to accomplish here?

Was this reply helpful? Yes No