SysTableLookup and union queries

(0) Share

Report

Posted on by JoeBrewer

692

Hi All

Has anyone used a union query with SysTable lookup? I’ve been given a spec to override an item id field so that it brings back a list of items from InventTable where PdsVendorCheckItem == PdsVendorCheckItem::NoCheck unioned with a list of items from InventTable that are linked to table PdsApprovedVendorList by the ItemId where PdsApprovedVendorList. PdsApprovedVendor == vendor account number.

Here is a job that contains a working query:

static void test_job(Args _args)
{
    QueryBuildDataSource qbds;
    QueryBuildDataSource qbdsApprovedVendorList;
    QueryBuildRange qbrVendor;
    QueryRun queryRun;
    VendAccount vendAccount = '12345';
    Query query = new Query();
 
    query.queryType(QueryType::Union);
 
    qbds = query.addDataSource(tablenum(InventTable), identifierstr(InventTable_1));
    qbds.addRange(fieldNum(InventTable, PdsVendorCheckItem)).value(queryValue(PdsVendorCheckItem::NoCheck));
 
    qbds = query.addDataSource(tablenum(InventTable), identifierstr(InventTable_2), UnionType::Union);

    qbdsApprovedVendorList = qbds.addDataSource(tableNum(PdsApprovedVendorList));
 
    qbdsApprovedVendorList.relations(false);
 
    // change the relation to vendor account number only
    qbdsApprovedVendorList.addLink(fieldNum(InventTable, ItemId), fieldNum(PdsApprovedVendorList, ItemId));
 
    // add a range for the item id
    qbrVendor = qbdsApprovedVendorList.addRange(fieldNum(PdsApprovedVendorList, PdsApprovedVendor));
    qbrVendor.value(queryValue(vendAccount));
 
    // info(query.toString());
 
    queryRun = new QueryRun(query);
 
    while (queryRun.next())
    {
        info(int642str(queryRun.get(tablenum(InventTable)).RecId));
    }
}

When this query is used with SysTableLookup line 73 of SysTableLookup.formRun adds an outer join to the query and breaks it as only exists and not exists joins are allowed.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

I don't know whats causing the error/additional join with the SysTableLookup class, but you could try to create a view with this query as datasource and use the view in the lookup. Or create your own lookup form based on the query and use this instead of SysTableLookup.

Was this reply helpful? Yes No
Malcolm Burtt 1,475 on at

Like (0)

Report
Copy link

Link copied!

We ran into problems using the unions with multi-select lookups and managed to solve that (see www.k3technical.com/using-union-queries). That was a different kind of problem to what you're seeing but I wonder if you could look at the multi-select lookup and compare with the standard lookup class and see why one would work (with modification) and the other would not.

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report
Copy link

Link copied!
Hi Joe,

don't know what kernel version you are speaking of, but have checked this on a non data bound stringfield type ItemID in R3 client 6.3.2000.2992 with standard systablelookup (non multiselect)

lookup method on stringcontrol

public void lookup() { QueryBuildDataSource qbds; QueryBuildDataSource qbdsApprovedVendorList; QueryBuildRange qbrVendor; VendAccount vendAccount = '12345'; Query query = new Query(); Systablelookup SysTableLookup; ; query.queryType(QueryType::Union); qbds = query.addDataSource(tablenum(InventTable), identifierstr(InventTable_1)); qbds.addRange(fieldNum(InventTable, PdsVendorCheckItem)).value(queryValue(PdsVendorCheckItem::NoCheck)); qbds = query.addDataSource(tablenum(InventTable), identifierstr(InventTable_2), UnionType::Union); qbdsApprovedVendorList = qbds.addDataSource(tableNum(PdsApprovedVendorList)); qbdsApprovedVendorList.relations(false); // change the relation to vendor account number only qbdsApprovedVendorList.addLink(fieldNum(InventTable, ItemId), fieldNum(PdsApprovedVendorList, ItemId)); // add a range for the item id qbrVendor = qbdsApprovedVendorList.addRange(fieldNum(PdsApprovedVendorList, PdsApprovedVendor)); qbrVendor.value(queryValue(vendAccount)); sysTableLookup = sysTableLookup::newParameters(tableNum(InventTable), this); sysTableLookup.parmQuery(query); sysTableLookup.addLookupfield(fieldNum(InventTable, itemid)); sysTableLookup.performFormLookup(); }

and the lookup itself

This seems to be run (at least with that binaries, the important classes resides in the kernel)

Is this your constellation ?

regards

Douglas

Was this reply helpful? Yes No
JoeBrewer 692 on at

Like (0)

Report
Copy link

Link copied!

Hi Douglas

Thanks for looking into this. I am using R3 CU9 for this, and the itemId field I am using is on the table PurchReqLine - I guess it is one of the relations that is causing the issue

Here is the query that is produced after SysTableLookup changes it:

SELECT {field list removed}

FROM InventTable(InventTable_1)

WHERE ((PdsVendorCheckItem = 0))

OUTER JOIN * FROM EcoResProduct(Ref_EcoResProduct_EcoResProduct) ON InventTable.Product = EcoResProduct.RecId

UNION

SELECT {field list removed}

FROM InventTable(InventTable_2)

EXISTS JOIN * FROM PdsApprovedVendorList(PdsApprovedVendorList_1)

WHERE InventTable.ItemId = PdsApprovedVendorList.ItemId AND ((PdsApprovedVendor = N'S100002'))

Joe

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!
Looks like AX adds a dynaLink because somewhere there is a EcoResProduct data source. Try deleting the dynaLinks of the query before executing the lookup

qbds.clearDynaLinks();

Maybe you can share your lookup code with us.

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report
Copy link

Link copied!

Hi Joe,

I'm really not able to reproduce this with he given lookup code in my last answer.

I'm, using "some kind "of R3 CU9

exactly: A standard application (without any modification, beside the testform)

- Application/ModelStore

6.3.200.323 = R3CU9 Standard (as upgared from CU8) = non direct slipstream install

- Binaries (both AOS and Client)

6.3.2000.2992 = some POST CU9 Binary Package applied to ALL components

I've checked this with databound 'ItemID ' field in PurchReqLine (uups sorry - of course a modification was required here - setting this to AllowEdit=true, for checking the lookup)

After that I modifed my form using more DataSources, but even with EcoResProduct/InventTable in different order and join modes I wasn't able to reproduce your problem on the ItemID field in any PurchReqLine datasource.

Can you give as an example where the problem arises?

And What EXACTLY is your kernel version (identical on both sides?)

Maybe there is really something 'deep dark' which should be fixed in future updates and for similar problems.

regards

Douglas

Was this reply helpful? Yes No