Hi All! Is there any possibility to recall a...

references
(Malaka Jayawardene) #1

Hi All!

Is there any possibility to recall a value of a column in a parent table to a virtual column in a child table? For example, my app has a table called “personal details” which behaves as the parent table, and in it there are multiple child tables referenced to it, one of which is “management” table. I would like to create a virtual column inside the management table that will be able to recall the name type column called “First name” in the “personal details” table (which is the parent table). Is this fundamentally possible?

Since it is possible to recall a value of a column in a child table to a virtual column in the parent table using the expresssion,

INDEX(SELECT([List type column for the referred table][specific column name that I want to recall from the referred table],TRUE),1),

I was wondering whether it’s possible to do the opposite.

Thank you for your help.

(Malaka Jayawardene) #2

@Harry Thank you. I tried both SELECT and LOOKUP expressions. However they didn’t return the value of “First name” column from the “Personal Details” table to the virtual column I created in the “management” table.

What I’m trying to achieve is the following. The user enters details in to the personal details form. This form (table) includes first name, last name, age etc. Then there’s a referenced table called “management” which is a child table to the “Personal Details” parent table, so this “management” table’s referenced list type column appears in the “Personal Details” form, and when the user presses it, it takes to the management table/form. I’m trying to create a virtual column inside this ‘management’ table/form, so that it could fetch the value in the “First name” column in the parent table “Personal Details” that user entered at the very beginning. I’m finding it difficult to get it to work.

PS:These tables are properly connected to each other with key columns having “UniqueIDs” and Ref type columns.

I Would really appreciate your input on this.

(Dinh Nguyen Nguyen) #3

@Malaka_Jayawardene

You can try using the SELECT expression to find the row values from a certain referenced row. Or you can use the LOOKUP expression. You can find more information about these expressions here:

help.appsheet.com - SELECT()

https://help.appsheet.com/expressions/functions/lookup SELECT() help.appsheet.com

(Malaka Jayawardene) #4

+Steve Coile @RezaRaoofi Great! I now understand a great deal about reference between tables than what I understood earlier. Thank you very much! You all rock! :smiley:

(Reza Raoofi) #5

@Malaka_Jayawardene when it comes to parent/child tables - and by that I mean child table has a Ref column that is referring to parent table via its key column - you can go in both directions and access the other columns from parent/child tables:

  1. From child table use De-reference expression: [RefColumn].[ParentColumn] to access a column from parent table.

  2. From parent table use filtering Ref lists and use a formula similar the one you mentioned yourself in your post: [Related Child Rows][ChildColumn]

Now, if you want to use any of these methods to hop across more than one parent/child relationship (more than one table) you will be able to do that by an extra Virtual Column in the intermediate table.

(Steven Coile) #6

In order to access the column of a particular row in the parent table (or any other table), you need a Ref column linked to the target table and containing the key of the target row in the target table.

You can then "deref* the Ref column value to gain access to any column in the referenced row.

For instance, when you create the child row, you might have explicitly linked it to its parent by expressly populating a column with the parent’s key value. If so, and this column were called Parent, you could access the First Name column of the parent with [Parent].[First Name].

If you didn’t expressly link the child to its parent, but each parent links to its (only one) child, you’ll have to infer the parent using FILTER(), SELECT(), or LOOKUP() from a virtual column in the child table:

=ANY(FILTER(“ParentTable”, ([ChildRef] = [_THISROW])))

or:

=ANY(SELECT(ParentTable[KeyCol], ([ChildRef] = [_THISROW])))

or:

=LOOKUP([_THISROW], “ParentTable”, “ChildRef”, “KeyCol”)

replacing ParentTable with the name of the parent table, KeyCol with the name of the parent table’s KEY column, and ChildRef with the name of the column in the parent table containing a reference to the child row. All three of the formulas above are equivalent. Dereference Expressions help.appsheet.com

(Malaka Jayawardene) #7

Thank you very much @RezaRaoofi +Steve Coile. [Parent].[First Name] did work for me as I have linked the child row to its parent by populating a column with the parent’s key value.

What I don’t understand is the part where you said “if you didn’t expressly link the child to its parent, but each parent links to its (only one) child” part. Is it when you create a parent and a child table without creating a unique key value for the child but use the same key from the parent table so that only one row can be created in the child table per one row in the parent table? Is that what you meant by “not expressly linking the child to its parent”?

(Steven Coile) #8

No, I was considering a one-to-one parent/child relationship where the parent links to the child but the child doesn’t link to the parent.

(Malaka Jayawardene) #9

In the AppSheet app I made, I have linked parent table ‘Personal details’ and child table ‘Management’ together by creating a column in the child table called “Management_Ref”. I made this column a “ref type column” and selected the ‘Personal Details’ table as the “ReferencedTableName” and made the “IsAPartOf” option to be “TRUE”, so that in the Personal Details Form view, a virtual list type column called “Related Managements” would appear. I have a UniqueID going on in the key column of ‘Personal Details’ table and another UniqueID going on in the ‘ManagementKey’ column in the ‘Management’ table. So in the ‘Management’ table, the “Management_Ref” column gets filled by the UniqueID of the Personal Details when ever I populate these tables. So I guess what I have created is a situation where I have only linked the ‘child to parent’ and not ‘parent to child’ is it? So the [Management_Ref].[First name] expression did work for me, because in my case it was a ‘child-to-parent’ link isn’t it?

What is the additional benefit by creating a ‘parent-to-child’ link also? (I have no requirement to get back to the parent table’s Form view through the child table’s Form view, so I have never had the need to create a ref column in the parent table and select the child table through that) If I create a parent-to-child link (then fundamentally the initial parent table anyway then becomes the child table and the child table becomes the parent table, isn’t it?) would I be able to easily use the same [Parent].[First name] expression you showed me earlier to recall a value from a column in the child table rather than using the INDEX(SELECT([Related Child Rows][Child Column],TRUE),1) expression? I’m trying to understand the ‘References between table’, that’s why I’m asking all these questions. Thank you for all your help! Really appreciate it!

(Reza Raoofi) #10

@Malaka_Jayawardene you have done a good job so far, and you can get what you need out of current relationship setup; what you explained makes total sense, and I think all is well. :slight_smile:

(Reza Raoofi) #11

Just to feel better about it, I suggest to read these articles, in case you haven’t already: http://help.appsheet.com/data/data-the-essentials

http://help.appsheet.com/data/references/references-between-tables

(Steven Coile) #12

@Malaka_Jayawardene

So I guess what I have created is a situation where I have only linked the ‘child to parent’ and not ‘parent to child’ is it?

Correct!

So the [Management_Ref].[First name] expression did work for me, because in my case it was a ‘child-to-parent’ link isn’t it?

Correct!

What is the additional benefit by creating a ‘parent-to-child’ link also?

None, given that you “have no requirement to get back to the parent table’s Form view through the child table’s Form view”.

If I create a parent-to-child link […] would I be able to easily use the same [Parent].[First name] expression you showed me earlier to recall a value from a column in the child table […]?

Yes!

:slight_smile: