Outer joins not working quite right

[KDean]

Let’s say I have three tables: User, Group, and User Group.

User Group is an outer join table, containing references to User and Group, as well as a single enum Role field to define the user’s role within the group.

Sample app here.

Problem #1

When I delete a User record, I want to delete all corresponding User Group records. So, I set “IsAPartOf” on the User field of User Group.

When I delete a Group record, I want to delete all corresponding User Group records. I can’t, because it generates the following error on save:

Table User Group had more than one Ref column with ‘IsAPartOf’ enabled, but a row may not be part of more than one other row. We disabled the ‘IsAPartOf’ option for column Group

This should be supported.

Problem #2

I would like to come at the User Group table from both directions. When displaying User in Detail view, I would like to see a table of User Group with the Group and Role fields displayed. When displaying Group in Detail view, I would like to see a table of User Group with the User and Role fields displayed.

I can setup multiple views of the same type for a table, but I can’t find any way to select a different Table view to be incorporated into a Detail view, so both the User and the Group Detail views are showing User and Role.

[Steve]

@KDean

This should be supported.

But it’s not.

@KDean

I would like to come at the User Group table from both directions.

You’ll have to implement this yourself.

[KDean]

@Steve

But it’s not.

Yes, I’ve done it, through a bot that monitors for deletion of “Group”. While there may be a reason under the covers for this, “cascade delete” on multiple columns is a well-supported database concept.

@Steve

You’ll have to implement this yourself.

I’m in the process of doing so, through a somewhat messy “session state” table architecture that I had to create to get around other limitations. If you have any other thoughts on how I can select different table views for the same data set, I’m happy to investigate.

[Jose_Arteaga]

Hello @KDean,

I searched through the community posts and I found this one:

Delete data in another table through action and workflow

I have been trying to edit and delete data from table 2 based on the same key column in table 1, and i have been reading resources on how to do it, including the url below, but I can’t seem to do it myself. I can use action and workflow to add a new row of data to another table, but i cannot edit or delete data. My 2 tables are setup to be able to add, edit and delete data. Any help is much appreciated

You will find plenty of information there

I hope this helps!

[TeeSee1]

If I understand your Problem #2 correctly, here is what you can do.

• Create two slices from User Group table, one for USER view and one for GROUP view.

• You can then create a reference view for each of the slices showing only desired columns, i.e GROUP and ROLE for the view used in USERS_Detail view.

• On USERS table, create a VC (Related UGUserView in the below example) with the formula REF_ROWS(“SL_UG_UserView”,”ref.user”). Create a similar VC for GROUP table.

Here is what you get..

USER_GROUPS table

Users_Detail view.

I have left the system generated Related USER_GROUPS view for comparison. You can simply remove it from the Detail view definition.

Groups_Detail view.

[Suvrutt_Gurjar]

Nice to see you back in the community after a while @TeeSee1.

[KDean]

@Jose_Arteaga

I searched through the community posts and I found this one:

Thanks. I’m already using the “Is a part of?” setting. The problem is trying to do it for an outer join table, where the join table is a part of two tables.

For User Group, I set “Is a part of?” for the User column.

To deal with Group, I have a bot that watches for deletions on the Group table. I then “Run action on rows”, set the target table to “Group User” (a slice of “User Group” with delete confirmation disabled), the filter to “FILTER(Group User, [Group] = [_THISROW])”, and the action to “Delete”. Not ideal, but it works.

[KDean]

@TeeSee1

If I understand your Problem #2 correctly, here is what you can do.

• Create two slices from User Group table, one for USER view and one for GROUP view.
• You can then create a reference view for each of the slices showing only desired columns, i.e GROUP and ROLE for the view used in USERS_Detail view.
• On USERS table, create a VC (Related UGUserView in the below example) with the formula REF_ROWS(“SL_UG_UserView”,”ref.user”). Create a similar VC for GROUP table.

Thanks. I hadn’t considered the “Slice” option. That will work just fine, with the added requirement that I have to override the “Row Selected” behaviour on each table view to go to a single common form using the original record (I don’t use the system generated views).