Form extension, new DataSource filter

(0) Share

Report

Posted on by Johnny

6,455

Hello,

I have an extension of ReqTransPO form, where I have added new Datasource (InventSum table). I have set the InnerJoin to ReqTrans table.

I added a field from inventSum to the form grid.

I want to filter the InventSum datasource by "ItemId" and "InventDimId" of ReqTrans.

For this I have copied the "OnQueryExecuting" event from InventSum Datasource and have written the code as below:

But the problem is that this method isn't even executed when I open the form or making some actions on the grid. The breakpoint is also not hit if I try to debug it.

How can I achieve my goal?

[FormDataSourceEventHandler(formDataSourceStr(ReqTransPo, InventSum), FormDataSourceEventType::QueryExecuting)]
public static void InventSum_OnQueryExecuting(FormDataSource sender, FormDataSourceEventArgs e)
{
    FormRun formRun;
    FormDataSource  reqTransDS;
    ReqTrans reqTrans;

    formRun     = sender.formRun() as FormRun;
    reqTransDS  = formRun.dataSource(formDataSourceStr(ReqTransPo, ReqTrans));
    reqTrans    = reqTransDS.cursor();

    Query query = sender.query();
    QueryBuildDataSource    qbds = query.dataSourceTable(tableNum(InventSum));
    qbds.addRange(fieldNum(InventSum, ItemId)).value(reqTrans.ItemId);
    qbds.addRange(fieldNum(InventSum, InventDimId)).value(reqTrans.inventDim().inventDimId);
    reqTransDS.query(query);
}

All responses (10)

Answers (1)

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

Like (0)

Report

RE: Form extension, new DataSource filter

It's not defined in my code. If you want to join your InventSum data source to ReqTransTransferIssue rather than ReqTrans, change Join Source property of your DS to ReqTransTransferIssue.

Code for adding the link will stay the same, because it doesn't refer to the name of the parent data source. It refers just to the parent table, which remains the same.
Johnny 6,455 on at

Like (0)

Report

RE: Form extension, new DataSource filter

Hi Martin,

I have one more question.

On the form there is "ReqTransTransferIssue" Datasource. It is also a ReqTrans table. So we have ReqTrans table twice as datasource.

The field "From Warehouse" comes from ReqTransTransferIssue DS, field "CovInventDimId".

How can I modify your code so, that the InventSum DS is filtered by ReqTransTransferIssue?

I guess "ReqTransTransferIssue_ds" is initialized when we open Planned Transfer Orders
Johnny 6,455 on at

Like (0)

Report

RE: Form extension, new DataSource filter

U are right. I 've overlooked CovInventDimId.

I set join source back to reqtrans with outer join and it works now.

thanks
Martin Dráb 233,017 Most Valuable Professional on at

Like (0)

Report

RE: Form extension, new DataSource filter

I do see CovInventDimId field in ReqTrans table. Can you check it once more, please?

If it didn't exist, you wouldn't have compiled my code at all. But you did, didn't you?
Johnny 6,455 on at

Like (0)

Report

RE: Form extension, new DataSource filter

Which one is better to do?

By default we have a relation on InventSum table to ReqTrans table (InventSum.ItemId ==> ReqTrans.ItemId).

But ReqTrans does not have InventDimId field.

InventDimId field is on ReqPo only (CovInventDimId).

So, what would be a better table to join the InventSum on form datasources and which LinkType would you use?

On ReqPo, we have both, ItemId and InventDimId fields. So now I have outer joined InventSum to ReqPo.
Martin Dráb 233,017 Most Valuable Professional on at

Like (0)

Report

RE: Form extension, new DataSource filter

Your code would work if you joined InventSum with ReqPo, but you said you inner-joined it with ReqTrans. Your original code also used fields from ReqTrans, not ReqPO. That's why I used ReqTrans in my code.

The error suggest that - despite your statement - InventSum data source isn't actually joined to ReqTrans. Please review its Join Source and Link Type properties.
Johnny 6,455 on at

Like (0)

Report
RE: Form extension, new DataSource filter

Hi Martin,

Sorry, I verified the answer, but I'm getting the error message now when using code you provided:

"The data source is not embedded within a (parent) data source"

I modified the code like below, but still getting the same error:

public void init() { next init(); QueryBuildDataSource sumQbds = ReqPo_ds.queryBuildDatasource(); sumQbds.addLink(fieldNum(ReqPo, ItemId), fieldNum(InventSum, ItemId)); sumQbds.addLink(fieldNum(ReqPo, CovInventDimId), fieldNum(InventSum, InventDimId)); }

When I debugged the your code, "InventSum_ds.queryBuildDatasource();" was giving just "Select * from InventSum".

My modification "ReqPO_ds.queryBuildDataSource();" gives the complete query of Form, including the InventSum datasource, but it throws the error message on the first addLink line.
Verified answer

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

Like (0)

Report
RE: Form extension, new DataSource filter

Your code isn't suitable for a join. You seem to assume that ReqTrans records are fetched from database and then extra queries are executed to fetch InventSum records for each of them. But as I said, what happens is that a single query is sent to database, which fetches data for ReqPO, ReqTrans and InventSum in one go.

You can link joined data sources with addLink() method of QueryBuildDataSource class (if there aren't links added automatically based on relations, or they don't work for you). But that's something you don't have to (and shouldn't) repeat every time when the query gets executed. Do it just once, when initializing the form (using CoC on the form or the data source, or using an event handler).

For example, this is how you could do it with CoC on the form:

public void init() { next init() QueryBuildDataSource sumQbds = InventSum_ds.queryBuildDatasource(); sumQbds.addLink(fieldNum(ReqTrans, ItemId), fieldNum(InventSum, ItemId)); sumQbds.addLink(fieldNum(ReqTrans, CovInventDimId), fieldNum(InventSum, InventDimId)); }
Johnny 6,455 on at

Like (0)

Report

RE: Form extension, new DataSource filter

Hi Martin,

thanks for the suggestion.

ReqPO "OnQueryExecuting" is called when opening the form, but I can't understand how to add the ranges to query.

The code I showed in question, does not work.

sender.cursor or

reqTransDS = formRun.dataSource(formDataSourceStr(ReqTransPo, ReqTrans));

reqTrans = reqTransDS.cursor();

are both empty.

So how can I set the ranges to Query?

From InventSum I want to display the "AvailPhysical" field on grid and it must show the value based on ItemId, InventDimId combination.

I can't use the display method here, because customer wants this field to be filterable / sortable.
Suggested answer

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

Like (1)

Report

RE: Form extension, new DataSource filter

Try using the same event of ReqPO data source. I think it's the right place, because that's the root of joined data sources. Joined data sources are all included in a single query; they aren't execute as multiple individual queries.

Community site session details

Form extension, new DataSource filter

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Jonas ”Jones” Melgaard – Community Spotlight

Kudos to the March Top 10 Community Stars!

Leaderboard

Featured topics

Product updates

Community site session details

Form extension, new DataSource filter

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Jonas ”Jones” Melgaard – Community Spotlight

Kudos to the March Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates