Filter form on load

(0) Share

Report

Posted on by Andrew Huisman

313

Hello everyone,

We have a form called BOMPartOf that shows all the BOM's an item is a part and then on the bottom part of the form, it shows the BOM Version

I have a request from a user to duplicate this form but only show BOM's that item is a part of that have active BOM Versions

So to start, I duplicated the form

The form has BOM and BOMVersion as datasources so the first thing I tried was to add a range to BOMVersion in code like this

[DataSource]
    class BOMVersion
    {
        void init()
        {
            Query query;
            QueryBuildDataSource qbds;

            super();

            query = new Query();
            qbds = query.addDataSource(tablenum(BOMVersion));
            qbds.addDynalink(fieldnum(BOMVersion,bomId),bom,fieldnum(BOM,bomId));
            criteriaVersionFromDate     = qbds.addRange(fieldnum(BOMVersion,FromDate));
            criteriaVersionToDate1      = qbds.addRange(fieldnum(BOMVersion,ToDate));
            criteriaVersionToDate2      = qbds.addRange(fieldnum(BOMVersion,ToDate));
            criteriaVersionActive       = qbds.addRange(fieldNum(BOMVersion, Active));
            criteriaVersionActive.value(queryValue(NoYes::Yes));
            this.query(query);
        }

The problem is, this didn't filter the top part of the form with the BOM's, only the bottom part of the form showing BOM versions. It would now only show the version, if it was active.

Then I tried to add a datasource and range to the BOM table in the init like this

void init()
        {
            Query                   query;
            QueryBuildDataSource    qbds;

            super();

            query                   = new Query();
            qbds                  = query.addDataSource(tablenum(BOM));
            criteriaBOMItemId   = qbds.addRange(fieldnum(BOM,ItemId));
            criteriaBOMFromDate = qbds.addRange(fieldnum(BOM,FromDate));
            criteriaBOMToDate1  = qbds.addRange(fieldnum(BOM,ToDate));
            criteriaBOMToDate2  = qbds.addRange(fieldnum(BOM,ToDate));
            bom_ds.query(query);
            pmfFormCtrl_BOMPartOf.dataSourceInitPost(bom);
        }

This didn't do anything, so lastly I added another BOMVersion to the datasources and renamed it BOMVersionActive

I joined it to BOM and then I tried to filter it in the code like this

[DataSource]
    class BomVersionActive
    {
        /// 
        ///
        /// 
        public void init()
        {
            Query query;
            QueryBuildDataSource qbds;

            super();

            query = new Query();
            qbds = query.addDataSource(tablenum(BOMVersion));
            qbds.addlink(fieldnum(BOMVersion,bomId),fieldnum(BOM,bomId));
            criteriaVersionActive       = qbds.addRange(fieldNum(BOMVersion, Active));
            criteriaVersionActive.value(queryValue(NoYes::Yes));
            this.query(query);
        }

    }

But this gives me an error that says The data sources is not embedded within a parent data source.

Basically I'm just trying to duplicate this.....

SELECT *
FROM BOM
JOIN BOMVERSION ON BOMVERSION.BOMID = BOM.BOMID
WHERE BOMVERSION.ACTIVE = 1

Anyone have an idea what I could be doing wrong?

I have the same question (0)

All responses (21)

Answers (2)

Verified answer

Martin Dráb 238,874 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You need to pay attention to link/join types.

BOM and BOMVersion data sources aren't joined, therefore filtering BOMVersion indeed doesn't filter BOM.

Adding an extra data source is a good idea. You said that you added it (and called it BOMVersionActive), but you're also trying yet another one in code. I suggest you stop adding the one in code and have code just for the filter. But make sure you BOMVersionActive data source has Join Source = BOM and LinkType = ExistJoin.

Just by the way, the error in your code is caused by the fact that you added a root data source and you're setting a join condition, which can't work if the data source isn't actually joined to any parent data source.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!
Thanks Martin,

I already added BomVersionActive so I've changed the join from OuterJoin to ExistsJoin

I've changed the code to look like this

[DataSource] class BomVersionActive { ///
/// ///
public void init() { Query query; QueryBuildDataSource qbds; super(); query = new Query(); criteriaVersionActive = qbds.addRange(fieldNum(BOMVersion, Active)); criteriaVersionActive.value(queryValue(NoYes::Yes)); this.query(query); } }

But now I'm getting an error saying "Object reference not set to an instance of an object."

What did I do incorrectly?

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!
I did some more research and have read that I should be filtering the datasource in the executequery method.

So I've done this:

[DataSource] class BomVersionActive { ///
/// ///
public void executeQuery() { QueryBuildRange qr; qr = this.query().dataSourceTable(tableNum(BOMVersion)).addRange(fieldNum(BOMVersion,Active)); qr.value(queryValue(NoYes::Yes)); super(); } }

But I'm getting the error "query missing querybuilddatasource for formdatasource" when I hit the super() in the BOM datasource executequery method......

I tried researching that error but didn't find anything as of yet.

Was this reply helpful? Yes No
Martin Dráb 238,874 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

What you read isn't correct for this scenario and if your code didn't blow up, it would add a new range every time when the query executes (e.g. when a user filters a grid). Add the range just once, when initializing the form.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!
I have changed my code to add the range when initializing the datasource of the form (BomVersionActive). Is this the right spot?

[DataSource] class BomVersionActive { ///
/// ///
public void init() { Query query; QueryBuildDataSource qbds; super(); query = new Query(); qbds = query.addDataSource(tablenum(BOMVersion)); criteriaVersionActive = qbds.addRange(fieldNum(BOMVersion, Active)); criteriaVersionActive.value(queryValue(NoYes::Yes)); this.query(query); } }

When I go to open the form, I get the following error:

"Cannot set a Query on joined FormDataSource 'BomVersionActive'. A Query can only be set on a Master FormDataSource"

Or should I be putting this range somewhere else in the code?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 238,874 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
You should merely add a range. Your code completely throws away the query created from form data sources and creates a new query with BOMVersion only. That's clearly wrong and it can't filter the joined BOM data source if there is none in your query.

This is how you can add a range to the current data source:

this.queryBuildDataSource() .addRange(fieldNum(BOMVersion, Active)) .value(queryValue(NoYes::Yes));

And this how you can do it from outside of the data source (e.g. in form's init():

BomVersionActive_ds.queryBuildDataSource() .addRange(fieldNum(BOMVersion, Active)) .value(queryValue(NoYes::Yes));

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!
I tried both ways you suggested and I get the following error

"Object reference not set to an instance of an object"

Here is my code with it in the forms init

void init() { pmfFormCtrl_BOMPartOf = PmfFormCtrl_BOMPartOf::newFromForm(this); pmfFormCtrl_BOMPartOf.initPre(); super(); switch (element.args().dataset()) { case tablenum(ReqPO): case tablenum(InventTable),tablenum(ProdBOM): bomShowInterval.visible(false); break; case tablenum(PmfFormulaCoBy): case tablenum(BOM),tablenum(BOMVersion): break; default: throw error(strfmt("@SYS25688", element.name(), tablepname(InventTable), tablepname(BOM), tablepname(BOMVersion), tablepname(ProdBOM))); } toDate = dateMax(); groupVersion.visible(new DictTable(tablenum(BOMVersion)).rights() >= AccessType::View && isConfigurationkeyEnabled(configurationkeynum(BOMVersion))); element.updateDesign(InventDimFormDesignUpdate::Init); element.updateDesignBOMVersion(InventDimFormDesignUpdate::Init); pmfFormCtrl_BOMPartOf.setDatasources(bom); pmfFormCtrl_BOMPartOf.initPost(); this.enableEngineeringChangeButtons(); BomVersionActive_ds.queryBuildDataSource().addRange(fieldNum(BOMVersion, Active)).value(queryValue(NoYes::Yes)); }

What did I do wrong?

Was this reply helpful? Yes No
Martin Dráb 238,874 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

The error means that you're trying to call a method on a variable (or a return value) that contains null instead of an object reference. Your first step is finding where the error gets thrown from. When you know which value is null, you'll need to fix the code that it's supposed to set the value.

Was this reply helpful? Yes No
Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!

My guess is that this is the problem. Says no data selected for BOMVersion.....

So how do I fix this?

Was this reply helpful? Yes No
Martin Dráb 238,874 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Of course that no data was selected; your code is in init(), therefore which happens before queries are executed and data loaded. Remember, what you're now doing is defining the query to be used later to fetch data from database.

Therefore there is nothing to fix; you just got distracted by something irrelevant.

Was this reply helpful? Yes No