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)

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

Like (0)

Report
Copy link

Link copied!

Good to hear that you've got it working.

Changing Auto Query from Yes (the default value) to No is indeed a bad idea. No query is created in such a case; you would have to do it completely in code. Reviewing properties of the data source would be the next step after confirming that queryBuildDataSource() is the method returning null.

It's not true that ExistsJoin don't work in forms. You can find hundred examples in then standard application.

addLink() is needed only if there isn't a relatation between those tables, or if you're joining a view.

linkActicve() is executed for dynamic links.

Was this reply helpful? Yes No
Suggested answer

Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!
So I got it working but I don't understand why.

Here are the things you NEED to do for it to work right

First when you setup the new datasource, make note of a few things. It has to have Auto Query on and it HAS to be an inner join. I was trying almost everything and finally switched to inner join, and it worked

Next, in the init of that datasource, set the link if needed, and set your range. criteriaBOMVersionActive is declared at the top of the class as a QueryBuildRange

[DataSource] class BOMVersionActive { ///
/// ///
public void init() { QueryBuildDataSource qbds; super(); qbds = this.query().dataSourceName(BOMVersionActive_DS.name()); qbds.addLink(fieldNum(BOMVersion, BOMId),fieldNum(BOM, BOMId)); criteriaBOMVersionActive = qbds.addRange(fieldNum(BOMVersion, Active)); } }

Last I found the line that was setting the item and put my line to set the active in the same method, which was in linkActive() of the main datasource (BOM). This is done before the super()

criteriaBOMVersionActive.value(queryValue(NoYes::Yes));

After all this, it's working!

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

Like (0)

Report
Copy link

Link copied!
Martin,

I have gotten a bit closer now. The first problem was I had the Auto Query set to No

Once I set that to yes, the form was showing thousands of rows which told me the join wasn't working right. So I put this in the code of the datasource I added

[DataSource] class BOMVersionActive { ///
/// ///
public void init() { QueryBuildDataSource qbds; super(); qbds = this.query().dataSourceName(BOMVersionActive_DS.name()); qbds.addLink(fieldNum(BOM, BOMId),fieldNum(BOMVersion, BOMId)); } }

This got the form working correctly but when I put in your line of code, the form doesn't actually filter. Here is your line that I have put in the init method of the form

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

So now the form opens and if I add the active field to see what it's doing, it shows as if it's supposed to be filtering the form, but it's not.

This is what it shows but as you can see, it's not filtering that column

The rows are 157

If I clear the active filter, then type in Yes again and hit apply, the row count goes down to 50 (which is correct)

So it's like the filter is being set but somehow not applied. Even if I open the form, add that column, and just click Apply, that doesn't work either. I HAVE to clear it, then type in Yes again and apply it in order for it to work.

What am I missing?

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

Like (0)

Report
Copy link

Link copied!

The problematic line is the one you told me to put in, so how would I know how to troubleshoot that? I didn't even know that line should be put in the code....

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

Like (0)

Report
Copy link

Link copied!

Sorry, but I'm not going to explore all the code if you already know the problematic line. If I was you, I would verify the assumption about which method returns null and then think about why.

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

Like (0)

Report
Copy link

Link copied!

Martin, are you able to see anything in this code that I need to do to make this work?

Was this reply helpful? Yes No

Andrew Huisman

313 on at

Like (0)

Report

Copy link

Link copied!

Here is all the code from that form,

Perhaps I'm missing something, that the query for the top grid with BOM datasouce is being manipulated somewhere else? I'm not sure. Someone please help

[Form]
public final class TCI_BOMPartOfActive extends FormRun
{
    ConfigGroupId               configGroupId;
    ItemId                      itemId;
    EcoResConfigurationName     configId;

    QueryBuildRange             criteriaBOMItemId;
    QueryBuildRange             criteriaBOMFromDate;
    QueryBuildRange             criteriaBOMToDate1;
    QueryBuildRange             criteriaBOMToDate2;

    QueryBuildRange             criteriaInventConfigId;

    QueryBuildRange             criteriaVersionFromDate;
    QueryBuildRange             criteriaVersionToDate1;
    QueryBuildRange             criteriaVersionToDate2;
    QueryBuildRange             criteriaVersionActive;

    TransDate                   fromDate;
    TransDate                   toDate;

    InventDimCtrl_Frm           inventDimFormSetup;
    InventDimCtrl_Frm           inventDimFormSetupBOMVersion;
    PmfFormCtrl_BOMPartOf       pmfFormCtrl_BOMPartOf;
    BOMId bomId()
    {
        return bom.bomId;
    }

    /// 
    /// Enable the buttons in the engineering change group, if an engineering change case category exists.
    /// 
    private void enableEngineeringChangeButtons()
    {
        boolean caseCategoryFound = CaseCategoryHierarchyDetail::existsByCategoryType(CaseCategoryType::EngineeringChange);

        engineeringChangeCreateCase.enabled(caseCategoryFound);
        engineeringChangeAssociateCase.enabled(caseCategoryFound);
        engineeringChangeItemAssociateCase.enabled(caseCategoryFound);
        engineeringChangeAddToLog.enabled(caseCategoryFound);
        engineeringChangeItemAddToLog.enabled(caseCategoryFound);
    }

    TransDate fromDate()
    {
        return BOMHierarchy::setValidFromDate(fromDate,bom.FromDate);
    }

    /// 
    /// Get the list of BOMTable records from the selected BOM records.
    /// 
    /// 
    /// A list of BOMTable records
    /// 
    List getBOMTableRecords()
    {
        List            selectedRecords = new List(Types::Record);
        BOM             bomSelected;
        BOMTable        bomTable;

        // Get the BOMTable records for each selected BOM
        bomSelected = BOM_ds.getFirst(true);
        if (!bomSelected)
        {
            bomSelected = BOM_ds.cursor();
        }

        while (bomSelected)
        {
            bomTable = bomSelected.bomTable();
            selectedRecords.addEnd(bomTable);
            bomSelected = BOM_ds.getNext();
        }

        return selectedRecords;
    }

    void init()
    {
        pmfFormCtrl_BOMPartOf = PmfFormCtrl_BOMPartOf::newFromForm(this);
        pmfFormCtrl_BOMPartOf.initPre();

        super();

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

        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();
    }

    boolean isBOMCriteriaItemId()
    {
        return true;
    }

    public boolean pmfIsEnabled()
    {
        return pmfFormCtrl_BOMPartOf.isFormulaEnabled();
    }

    TransDate toDate()
    {
        return BOMHierarchy::setValidToDate(toDate,bom.ToDate);
    }

    void updateDesign(InventDimFormDesignUpdate _mode)
    {
        Set             validDataSources;
        InventDimParm   inventDimParmShow;
        InventDimParm   inventDimParmEnabled;

        element.design().caption(strfmt("@SYS191501", "@SYS11737", itemId));

        switch (_mode)
        {
            case InventDimFormDesignUpdate::Init        :
                if (! inventDimFormSetup)
                {
                    validDataSources = new Set(Types::String);
                    validDataSources.add(inventDim_ds.name());
                    inventDimFormSetup = InventDimCtrl::newFromFormValidDataSources(element,validDataSources);
                }
                inventDimParmShow.initDimBOMAll();
                inventDimFormSetup.parmDimParmVisibleGrid(inventDimParmShow);

            case InventDimFormDesignUpdate::Active      :
                inventDimParmEnabled.initDimBOMEnable(InventDimGroupSetup::newItemId(bom.ItemId), bom);
                inventDimFormSetup.parmDimParmEnabled(inventDimParmEnabled);
                inventDimFormSetup.formSetControls(true);
                break;

            default :
                throw error(strfmt("@SYS54195",funcname()));
        }
    }

    void updateDesignBOMVersion(InventDimFormDesignUpdate _mode)
    {
        Set             validDataSources;
        InventDimParm   inventDimParmShow;

        switch (_mode)
        {
            case InventDimFormDesignUpdate::Init        :
                if (! inventDimFormSetupBOMVersion)
                {
                    validDataSources = new Set(Types::String);
                    validDataSources.add(inventDimBOMVersion_ds.name());
                    inventDimFormSetupBOMVersion = InventDimCtrl::newFromFormValidDataSources(element,validDataSources);
                }

                inventDimParmShow.initDimBOMRouteVersion();
                inventDimFormSetupBOMVersion.parmDimParmVisibleGrid(inventDimParmShow);
                inventDimFormSetupBOMVersion.formSetControls(true);
                break;

            default : throw error(strfmt("@SYS54195",funcname()));
        }
    }

    [DataSource]
    class BOM
    {
        public int active()
        {
            int ret;

            ret = super();

            element.updateDesign(InventDimFormDesignUpdate::Active);

            return ret;
        }

        void linkActive()
        {
            InventTable         bufferInventTable;
            BOM                 bufferBOM;
            ProdBOM             bufferProdBOM;
            BOMVersion          bufferVersion;

            pmfFormCtrl_BOMPartOf.dataSourceLinkActivePre();

            switch (element.args().dataset())
            {
                case tablenum(InventTable):
                    bufferInventTable   = element.args().record();
                    itemId              = bufferInventTable.ItemId;
                    configId            = '';
                    break;
                case tablenum(BOM):
                    bufferBOM           = element.args().record();
                    itemId              = bufferBOM.ItemId;
                    configId            = bufferBOM.inventDim().ConfigId;
                    fromDate            = element.args().caller().fromDate();
                    toDate              = element.args().caller().toDate();
                    break;
                case tablenum(ProdBOM):
                    bufferProdBOM       = element.args().record();
                    itemId              = bufferProdBOM.ItemId;
                    configId            = bufferProdBOM.inventDim().ConfigId;
                    break;
                case tablenum(BOMVersion):
                    bufferVersion       = element.args().record();
                    itemId              = bufferVersion.ItemId;
                    configId            = '';
                    fromDate            = element.args().caller().fromDate();
                    toDate              = element.args().caller().toDate();
                    break;

                case tablenum(ReqPO):
                    itemId      = pmfFormCtrl_BOMPartOf.parmItemId();
                    configId    = pmfFormCtrl_BOMPartOf.parmConfigId();
                    break;
                default:
                    itemId      = naStr();
                    configId    = naStr();
                    break;
            }

            criteriaBOMItemId.value(queryValue(itemId));
            if (configId)
            {
                criteriaInventConfigId.value(queryValue(configId));
            }
            else
            {
                criteriaInventConfigId.value(SysQuery::valueUnlimited());
            }

            super();
        }

        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);
        }

        void executeQuery()
        {
            TransDate searchToDate = toDate  ? toDate : dateMax();

            if (bomShowInterval.value())
            {
                criteriaBOMFromDate.value(queryRange(dateNull(),searchToDate));
                criteriaBOMToDate1.value(queryRange(fromDate,dateMax()));
                criteriaBOMToDate2.value(queryValue(dateNull()));
            }
            else
            {
                criteriaBOMFromDate.value('');
                criteriaBOMToDate1.value('');
                criteriaBOMToDate2.value('');
            }

            super();
        }

        [DataField]
        class BOMId 
        {
            public void jumpRef()
            {
                BOMTable::jumpRefBomTable(BOMTable::find(bom.bomId),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);
        }

        void executeQuery()
        {
            TransDate searchToDate = bom.ToDate ? bom.ToDate : dateMax();

            if (versionShowInterval.value())
            {
                criteriaVersionFromDate.value(queryRange(dateNull(),searchToDate));
                criteriaVersionToDate1.value(queryRange(bom.FromDate,dateMax()));
                criteriaVersionToDate2.value(queryValue(dateNull()));
            }
            else
            {
                criteriaVersionFromDate.value('');
                criteriaVersionToDate1.value('');
                criteriaVersionToDate2.value('');
            }

            super();
        }

    }

    [DataSource]
    class InventDim
    {
        public void init()
        {
            QueryBuildDataSource    qbds;

            super();

            // Only range on configId, should be all item dimensions later on
            qbds = this.query().dataSourceName(InventDim_DS.name());
            criteriaInventConfigId = qbds.addRange(fieldnum(InventDim,ConfigId));
        }

    }


    [Control("MenuFunctionButton")]
    class EngineeringChangeCreateCase
    {
        void clicked()
        {
            List    bomTableRecords = element.getBOMTableRecords();

            CaseDetailFormCreate::createFromRecordList(bomTableRecords, bom, CaseCategoryType::EngineeringChange);
        }

    }

    [Control("MenuFunctionButton")]
    class EngineeringChangeAllCases
    {
        void clicked()
        {
            CaseDetailForm::runMenuItemForm(bom.bomTable(), menuitemdisplaystr(EngChgCaseDetailGridBom));
        }

    }

    [Control("MenuFunctionButton")]
    class EngineeringChangeAssociateCase
    {
        void clicked()
        {
            List    bomTableRecords = element.getBOMTableRecords();

            CaseDetailForm::runEngChgCaseAssociateForm(bomTableRecords, bom);
        }

    }

    [Control("MenuFunctionButton")]
    class EngineeringChangeAddToLog
    {
        void clicked()
        {
            CaseDetailForm::runEngChgCaseAddToLogForm(bom.bomTable());
        }

    }

    [Control("CheckBox")]
    class BOMShowInterval
    {
        void clicked()
        {
            super();

            bom_ds.executeQuery();
        }

    }

    [Control("MenuFunctionButton")]
    class EngineeringChangeItemAssociateCase
    {
        void clicked()
        {
            List            selectedRecords = new List(Types::Record);
            BOMVersion      bomVersionSelected;
            InventTable     inventTable;

            // Get the BOMTable records for each selected BOMVersion
            bomVersionSelected = BOMVersion_ds.getFirst(true);
            if (!bomVersionSelected)
            {
                bomVersionSelected = BOMVersion_ds.cursor();
            }

            while (bomVersionSelected)
            {
                inventTable = bomVersionSelected.inventTable();
                selectedRecords.addEnd(inventTable);
                bomVersionSelected = BOMVersion_ds.getNext();
            }

            CaseDetailForm::runEngChgCaseAssociateForm(selectedRecords, inventTable);
        }

    }

    [Control("MenuFunctionButton")]
    class EngineeringChangeItemAddToLog
    {
        void clicked()
        {
            CaseDetailForm::runEngChgCaseAddToLogForm(bomVersion.inventTable());
        }

    }

    [Control("CheckBox")]
    class VersionShowInterval
    {
        void clicked()
        {
            super();

            bomVersion_ds.executeQuery();
        }

    }

}

Was this reply helpful? Yes No

Andrew Huisman 313 on at

Like (0)

Report
Copy link

Link copied!

I'm assuming it is too because when I went to debug the code and hit F11 on that line, it gave me the error immediately.

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

Like (0)

Report
Copy link

Link copied!

Please debug the code to see which method returns null. My guess is that it's queryBuildDataSource() - please go and test it.

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

Like (0)

Report
Copy link

Link copied!

Sorry if I'm bugging you but it seems like it is probably something simple and I would really like to get this project done for the user.

Was this reply helpful? Yes No