Re: Reverse references to parent table

vivian · 10-20-2021 12:11 AM

Hi all,

I have a table with unique data on each row.

Users are allowed to add multiple comment/ note (with specific topic for selection on the form) related to that parent table.

I would like to create slices & cluster the specific topic in difference views with reverse references to the parent table.

Learnt from this articles
https://help.appsheet.com/en/articles/961426-references-between-tables
that reverse reference in the opposite direction will be added automatically by AppSheet but seem that I can’t see any extra column. Is reverse references the method or will IsAPartOf, etc expression can help?

Thanks in advance!

Aurelien

Hi @vivian

Can you show us the way you set reference for your column ?
You must have clicked on the black pen next to your column name and have selected the source table.

vivian

Hi Aurelien

the relation between the parent & child are set with ref_rows.

Ryan_Mortimer

By this, do you mean that you want to see values from the PARENT table displayed in the CHILD table/slice

or do you mean that you want to see an Inline view / link in the CHILD that goes back to the PARENT?

vivian

Hi Ryan, I just want to show a reference icon for going back to the parent record only. not so sure do I need to add more column in child table or it will be good for ref_rows too, I am trying it out.

Ryan_Mortimer

Does your PARENT row share any values with the CHILD ROW? If so, this is nice and easy

In this example, the KEY for the PARENT ROW is listed in a column in the CHILD ROW

Create a Virtual Column in your child table

SELECT(PARENT TABLE[PARENT ID], ([PARENT ID] = [_THISROW].[PARENT ID]))

If you don’t carry your key column over, you can just bring that over in a virtual column easily.

To do this, create a virtual column in your CHILD TABLE, with this as the formula:

[REF COLUMN].[KEY COLUMN FROM PARENT]

I can help you further if you provide more information about your data structure / table names / column names

vivian

Thanks so much Ryan!

What I set:

The parent table is called LIST with an unique id ID, showing the outstanding tasks that users need to do.
The child table is called NOTES with an unique id ID. Recording the process/ comment/ remark of the tasks.
The 2 tables are linked with a virtual column called Related Notes on table LIST, formula is REF_ROWS(“Notes”, “Task”). The [Task] here is a auto generated code by AppSheet & refer back to LIST table with Ref type.
I have a action with a icon for user to add notes on the LIST with a form, formula is LINKTOFORM(“Notes_Form”, “Task”, [ID]).
I have another set of readable code [Job Number] on LIST table & it was also on the NOTES table but using vloopup on the google sheet >n<

I plan to have:

a slice with “deck” showing the notes with specific topic (e.g. contacted contractor to handle) for user to select notes to read
a “detail” view for showing the content of the notes
a ref icon or card on the “detail” view to navigate user back to the parent table to read the original task

Ryan_Mortimer

So, if I am understanding this correctly.

Your LIST (parent) and NOTES (child) share a value that is unique? - This column is [Job Number]

If that is correct, this should be the correct expression to use in a virtual column within your child table (NOTES?)

SELECT(LIST[JOB NUMBER], ([JOB NUMBER] = [_THISROW].[JOB NUMBER]))

Personally. I would add a virtual column that references the [KEY/ID] of the parent table in the child record (as per my last comment).

Aurelien

Hi @vivian

Ryan_Mortimer

This is a much easier way to execute this! 10/10

vivian

Thanks so much but sorry for a further question… I should put this deep link to the virtual column? or on actions for creating the icon? I tried both but seem it’s not working.

Aurelien

Hi @vivian

here is an example about how to do it, based on one of my apps:
Please note the “button” appears as soon as you set the display option: prominently, overlay, and so on, and will be displayed along with the UX view based on the table your action is attached (not sure I expressed clearly, but you get what I mean )

Ryan_Mortimer

I think I misunderstood the question of linking back to the parent. I was under the impression that this was hoping to be achieved in the detail view.

An interesting question I have is, that if your CHILD row has “many” PARENT tables, would you then need to use an expression like the one that I suggested?

I learn something new every day here!

vivian

in my case, 1 child row got 1 parent row only but 1 parent row may have many child row.

I tried the LINKTOPARENTVIEW() on actions & added an overlay icon on the detail view successfully

I am trying to add slices to filter the specific topic on child table but seem that the overlay icon disappear not sure did I set anything wrongly trying my best to figure it out.

vivian

@Aurelien, when I am on deck view of my slice, I can see the LINKTOPARENTVIEW() action icon but it’s not working when clicking on it. & when I click into the detail view, the overlay icon is disappeared.

But when I go back to the general detail veiw of my child table record. it works without any problems

vivian

hi, anyone can help on this? i am still struggling with this… action can only be applied on table but not slice… but seem that the same expression can’t put on slice. or should I use another method here?

to specific, I need to create slices to cluster the content into different views across the CHILD & PARENT table:

case 1)

check if the CHILD table [Notes] = “inspected & ready for next step”
if it’s true, show them in a deck view for a list & detail view for detail, & can be navigated back to the PARENT for reading the original duty to follow up

case 2)

check if PARENT table’s condition on duty = maintenance work
If it’s true, check if the CHILD table [Notes] <> “contacted contractor”
if it’s true, show them in a deck view for a list & detail view for detail, & can be navigated back to the PARENT for reading the original duty to follow up

Steve

Show where?