Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Add datasource relatio...
Finance | Project Operations, Human Resources, ...
Unanswered

Add datasource relation and link in Form

(0) ShareShare
ReportReport
Posted on by vicky1234 64

Hi .

I have a form having itemid and inventdimid of an item stock stored in table lets say .ReqITemTable.

I need to show this on a cutom enquiry form which is simple.

Now the complex part is that I need to also show  the sales order which is having that same item and inventdimid on a separate tab in a grid  just like we have custinvoice journal form.

So for eg. if a SO123 is having an item ABC with site A and warehouse W1  then this will show in the tab against an item with ABC and site A and warehouse W1 however SO123 should not be shown for warehouse W2.

Same way I also need to show the sales quotation on a 3rd tab.

There is no direct relation between "ReqITemTable" and Sales table .So I will have to check the sales line and link it to the reqitemtable in the form .

I tried to do it int he form but I think i am doing something wrong with the relations.

I think in this case we can only specify links and relation in the code level only . Can anyone help me with the solution please.

  • Martin Dráb Profile Picture
    Martin Dráb 231,983 Most Valuable Professional on at
    RE: Add datasource relation and link in Form

    You made no progress on  "Parent datasouce is not embedded" because you keep ignoring the crucial topic: that your data sources aren't joined. Your code assumes that your ReqItemTable is a parent data source of SalesLine and that they're joined, but the error messages proves that it's not true. Your implementation is different.

    Please tell us which data sources you want to join and which ones you want to link (e.g. with the Delayed link). Then share with us the set up of Join Source and Link Type properties of your data sources.

    And didn't you say you had SalesTable data source too?

    By the way, you could replace this.query().datasourceTable(tablenum(salesline)) with mere this.queryBuildDataSource(). You should also use the camel casing for names, such as ReqItemTable instead of reqitemtable. Code is read much more often than written, therefore making it readable is very important.

  • vicky1234 Profile Picture
    vicky1234 64 on at
    RE: Add datasource relation and link in Form

    Hi Martin,

    What I meant by "I did that fix" -> parent ds was put first and then the corresponding child which I was doing incorrectly.

    I have 3 datasources in the form . 1) reqitemtable 2) inventdim 3) salesline

    in salesline init I have written the below code.

    public void init()

{

     super();

     this.query().datasourceTable(tablenum(salesline)).addlink(fieldnum(reqitemtable,itemid),fieldnum(salesline,itemid),reqitemtable_ds.name());

}

    I had even tried replacing the above code with querybuilddatasource but then too there is no link established.

    I have removed all other code.

    But still the same error while opening the form i.e. " that parent is not embeeded with child."

    As of now the only change i have is the above method and salesline datasource . 

  • Martin Dráb Profile Picture
    Martin Dráb 231,983 Most Valuable Professional on at
    RE: Add datasource relation and link in Form

    Just saying "I did that fix" doens't allow us to find the bug. Nevertheless you still have the same error, therefore you failed to address the problem. You're still adding join conditons between data sources that aren't actually joined.

    Please tell us which data sources you want to join and which you want to link (because you're doing something else what I suggested) and then show us your current code.

    Keeping the bug in place and trying to meddle with things like executeQuery() doesn't sound wise to me. Also, it again proves that you have linked, not joined data sources, therefore you need to either the link to a join, or you need different code. If the data sources aren't joined, you'd need a dynalink instead of a join link.

    The problem with empty rows in a grid is likely caused by a same problem (that you're trying to use fields from data sources that aren't joined).

  • vicky1234 Profile Picture
    vicky1234 64 on at
    RE: Add datasource relation and link in Form

    Hi Martin,

    I did that fix but unfortunately I got an error saying that "Parent datasouce is not embedded" .

    i tried to filter the secondary database by calling the execute query of the active of parent datasouce but that too give wierd results .eg. sometimes it shows correct values sometimes it shows everything blank,

    Also the grid shows lots of blank records even when "insert if empty/end" is set to No.

    I am not sure what is the actual issue .

    Can you recommend a STD sample form which can be considered for this case.

  • Martin Dráb Profile Picture
    Martin Dráb 231,983 Most Valuable Professional on at
    RE: Add datasource relation and link in Form

    Please always use Insert > Code (in the rich-formatting view) to paste source code.

    public void init()
{
	super();
	
	this.query().dataSourceTable(tableNum(SalesLine))
		.addLink(fieldnum(SalesLine, ItemId), fieldNum(ReqItemIable, ItemId), reqItemTable_ds.name());
}

    The error message says that you're trying to add a joinj condition to a data source that isn't joined. Please review your setup.\

    Also, the order of fiels in addLink() is wrong. The first parameter should be a field from the parent, but you used a field from the table that you're joining (SalesLine)

  • vicky1234 Profile Picture
    vicky1234 64 on at
    RE: Add datasource relation and link in Form

    Thanks again Martin for the insights.

    I have tried below code in the init method of the salesline however I am getting the error "The datasouce is not embedded with parent datasouce...."

    public void init()

    {

         super();

         this.query().datasourceTable(tablenum(salesline)).addlink(fieldnum(salesline,itemid),fieldnum(reqitemtable,itemid),reqitemtable_ds.name());

    }

    Apart from this the purchline is having no relation or join defined at the form database level.

  • Martin Dráb Profile Picture
    Martin Dráb 231,983 Most Valuable Professional on at
    RE: Add datasource relation and link in Form

    Think about it. Do you want to use different conditions every time when the query executes (e.g. the user sort the data set). Not really. Therefore executeQuery() isn't a good place.

    Will the conditions change when you swhich to another record. Nope, it'll be the same - you'll still want to have relation over the same fields. Therefore active() isn't the right place eirher.

    You want to set the relation just once, when opening the form, therefore init() method (of the form itself or a data source) is the best place.

  • vicky1234 Profile Picture
    vicky1234 64 on at
    RE: Add datasource relation and link in Form

    Thanks Martin for a really quick reply.

    Sorry for the typo mistakes.

    I need to create a relation with the inventdimid .

    Yes I agree that the relation will be with salesLine and reqitemtable however there is no direct relation at the table level

    I dont want to create a relation at the table level for this customization and was thinking of writing all the logic at the custom form level itself.

    What I am not able to figure out is that where exactly the logic needs to be written for this scenario, 

    Init, execute, active method will have to be modified at proper database level and I think the relation property for Salesline will be blank in this case as we are creating relation /link from the code.

  • Martin Dráb Profile Picture
    Martin Dráb 231,983 Most Valuable Professional on at
    RE: Add datasource relation and link in Form

    Unfortunetely your description isn't very detailed. It's not clear what you did (you said mere "I tried to do it int he form") and what problem you have with it ("something wrong with the relations."). Therefore it's impossible for us to say what you did wrong.

    But I can tell you something in general. Before you can start atempting an implementation, you need to decide whether you can do the relation over InventDimId, i.e. all inventory dimensions, or you want to use just site and warehouse (and other dimensions may differ). The solution will be quite different in these two cases.

    As you mentioned, SalesTable has no information about items, therefore you can't use it in a join over ItemId. You need SalesLine instead.

    If you want to create a relation where RegItemTable.ItemId = SalesLine.ItemId and RegItemTable.CovInventDimId = SalesLine.InventDimId,

    , I can imagine that you'll add a delayed link between SalesTable and SalesLine and join SalesLine with RegItemTable. Then you can use linkType() methods to add join conditions.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,278 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,983 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans