Form extension, new DataSource filter

(0) Share

Report

Posted on by Johnny

6,478

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

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

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

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report
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.

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

Like (0)

Report

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.

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

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.

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

Like (0)

Report

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?

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

U are right. I 've overlooked CovInventDimId.

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

thanks

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

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

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

Like (0)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Form extension, new DataSource filter

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Form extension, new DataSource filter

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates