Asking about table relation and how to display it correctly

(0) Share

Report

Posted on by Ken Manhattan

552

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,

I have the same question (0)

All responses (16)

Answers (1)

GirishS 27,827 Moderator on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

Hi Ken,

Have you set the Join source property on the Table B with Table A?

Also have you set the delete action property when you are creating a relation with Table A from Table B.

Try setting Insert if empty to No on the Table B DataSource and check.

Thanks,

Girish S.

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

Hi Ken, you need to set Delete action on table relation. Two ways to tackle it, either don't allow delete in TableA if related records exists in TableB (Restricted delete action) or delete all related records in TableB when record in tableA is deleted (Cascade delete action).

Was this reply helpful? Yes No
Ken Manhattan 552 on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

About the JoinSource in Datasource property of TableB of the form, yes, I have set it to TableA. And I did put "No" to "Insert if empty" or "insert at End" (btw, isn't this setting to allow auto insert when it is empty ?)

Forgive me, about the Delete action, I think it is not the concern here, since it is for "action" in TableB when record in TableA deleted, right ? Or I may mistaken, and it is related to the display issue ?

The issue is when I delete the record in TableA, which will make the upper grid empty (Lines), seems TableB (in Lines detail) then "lost" filter so it is showing all records instead displaying blank grid as well.

Thanks,

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

Do you have Datasource property on the Table B grid set to Table B?

If you set Insert If empty property to No it won't create new empty record when there is no record exist in the table.

Try setting the delete action under the relation node and check. Just give it a try.

Thanks,

Girish S.

Was this reply helpful? Yes No
Ken Manhattan 552 on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

Hi Girish,

Yes, datasource in bottom section (Lines detail) is set to TableB.

But I think you misunderstood me, the grid in TableB is not "auto" inserting new record, but it is showing all of its record (data) with no relation at all of TableA, whereby in TableA grid is empty. But it is not really empty also though, it is empty in the its own grid (Lines) because also it has filter in the higher level.

So, it is just like I said, TableB (bottom) is displayed correctly when in the TableA grid has record (because of its relationship), but when tableA grid empty, TableB grid showing all record inside the table.

Thanks,

Was this reply helpful? Yes No
Verified answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

You need to set the linktype property.

Try this.

dynamicsaxhari.blogspot.com/.../form-data-source-link-types-active.html

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Ken Manhattan 552 on at

Like (0)

Report

RE: Asking about table relation and how to display it correctly

Hi Bharani, It works, many thanks.

Set the link type as "inner join".

Was this reply helpful? Yes No
Ken Manhattan 552 on at

Like (0)

Report

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,

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

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

Was this reply helpful? Yes No