Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Asking about table relation and how to display it correctly

(0) ShareShare
ReportReport
Posted on by 516

Hi guys,

If I have 2 tables, lets say Table A and TableB, and in TableB I created a relation to table TableA with some key (two fields).

Then in my form I've created design like this ->

When TableA (Lines) has record, the display (Line details) in TableB already correct, to display the record with the relation to TableA. But issue is when I delete the record in TableA, then grid in TableB will display all its records. What setting I didn't maintain which makes this behavior ? Means to say, tableB grid supposedly to not display anything.

Thanks,

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Asking about table relation and how to display it correctly

    Calling research method will rerun the existing form query against the database and returns active record. Simply its used to refresh the form when there are any changes on the form - Since you have added the code in the executeQuery method of Table B DataSource calling research method is not needed. You can remove that and check.

    Thanks,

    Girish S.

  • Ken Manhattan Profile Picture
    Ken Manhattan 516 on at
    RE: Asking about table relation and how to display it correctly

    Btw, I feel the process a bit lagging though. Is it because the customization on ExecuteQuery() ? and may I know if the "research()" necessary in this method ?

    Thanks

  • Ken Manhattan Profile Picture
    Ken Manhattan 516 on at
    RE: Asking about table relation and how to display it correctly

    I think it's working now. But I put the executeQuery on TableB side, cause it is where it's working (and I also think the correct place based on my logic, not really sure)

    Anyway, just wondering why it needs this "manual code" to make this happens ?

    But thanks again for all the helps.

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,552 Super User 2024 Season 1 on at
    RE: Asking about table relation and how to display it correctly

    Hi, You can try below code in executeQuery method after super

    Str tableAQueryStr = this.query().toString();

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Asking about table relation and how to display it correctly

    TableA_ds.query() - Try this in the info log and check how the query was build in.

    Thanks,

    Girish S.

  • Ken Manhattan Profile Picture
    Ken Manhattan 516 on at
    RE: Asking about table relation and how to display it correctly

    Hi Girish,

    May I know how to check the query string after the this "addrange", because currently there is no records show, I think I need to look at the query to find what went wrong.

    Thanks,

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Asking about table relation and how to display it correctly

    I think this is because of join type. Change that to Delayed type. Inner join will return matching records in both the tables.

    To solve this error, you can do one thing - On Table A executeQuery method - Add a range to Table B with related fields as a value to it. SO whenever you change some value on the header table executeQuery will get triggered and TableB filtered accordingly.

    public void executeQuery()
    {
        // I am assuming relation between TableA and TableB will be ItemId. If there is more
        then one relation between TableA and TableB add that also to the range.
        TableB_ds.queryBuildDatasource().addRange(fieldnum(TableB, Item)).value(queryValue(TableA.ItemId));
        TableB_ds.researc();
        super();
        
    }

    Thanks,

    Girish S.

  • Ken Manhattan Profile Picture
    Ken Manhattan 516 on at
    RE: Asking about table relation and how to display it correctly

    Hi,

    Found something interesting, but not right after put the linktype to innerjoin, on the 2nd Datasource (TableB). It turns out, when TableB has x record(s), TableA grid then will display x record(s) as well.

    So for example TableA has 1 record of ID=001, TableB in relation to TableA will have 2 records, the grid in TableA will displayed 2 rows which actually duplicated since if I checked in the database directly it is correct only 1 record

    Something like this image ->

    Now I'm wondering why this behavior ?

    Thanks,

  • Ken Manhattan Profile Picture
    Ken Manhattan 516 on at
    RE: Asking about table relation and how to display it correctly

    Hi Bharani, It works, many thanks.

    Set the link type as "inner join".

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Asking about table relation and how to display it correctly

    Yes I understood the issue.

    Try to print the Table B Datasource query using toString method and check. 

    Thanks,

    Girish S.

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

Announcing Our 2025 Season 1 Super Users!

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

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans