Bug when trying to access columns from ref row

[Paras_Sood]

Hi

I am trying to access a column from ref rows column but it doesn't work. It's suggesting me the column name which I have already typed. Is this a bug or am I doing something wrong?

[Steve]

This error commonly occurs when you're referencing a slice rather than a table, and the slice does not include the column.

[Paras_Sood]

Hey Steve

I can confirm that I do not have any slices. I have a table "Items" and  another table called "Relationships"

Relationships has three columns - Key (Unique ID), Parent (Ref to items table) and Child (Ref to items table)

When I try to use the virtual column created because of this reference in the items table, I get this error.

[Marc_Dillon]

>>"Relationships has three columns - Key (Unique ID), Parent (Ref to items table) and Child (Ref to items table)"

Then why are you trying to pull a "Name" column from that table?

[Paras_Sood]

Name is just an example, I want to pull the column "SA" (contains value of stock available) across all parents.

And I am trying to pull this column because the ref rows contains a reference to parents which is a reference to the item table itself, if that makes sense?

[Marc_Dillon · Accepted solution]

Related Relationships... column is a reverse reference to the Relationships table. When using a list dereference on that column, the dereferenced column (the second one, where you put "name") needs to be in the referenced table. Your only choices would be Key,Parent,or Child, since those are the only columns.

https://help.appsheet.com/en/articles/4575708-list-dereference

----------------------

ohhhhh....

I think I just realized what you're trying to do. The relationships table is setting up a many-to-many reference between different Items records, right? You want a SUM of the [VA] column from all items records that are the "child" to the current item record?

Hmm. You made that difficult. Can items have more than one parent? If not, I would just add a [parent] column to the items table, and get rid of the Relationships table.

If they can have more than one parent, I would solve it by using an intermediate virtual column to pull the list of all child keys, with:

[Related...][child]

Make sure it is List type, base type Ref, pointing at the items table. Then you can do:

SUM( [new VC][SA] )

[Paras_Sood]

Thank you for your suggestion mate. I added the relationship table to fix a long standing problem that I have talked about in another post. I will add a link to the reference here: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Inventory-management-with-parent-child-relation...

Basically what I am trying to achieve is:

If I store 15 units of item C inside 1 unit of item B, and I store 12 units of item B inside of 1 unit of item A, then when I run my calculations, I want to see two different values:

1. Actual Stock Available: To see how many individual units of item A, B and C I have independent of whether they are inside anything.

2. Total Stock Available: To see how many units of Item C I have including whatever is present in item A and in item B.

If I have the following actual stock available:

Item A (contains 12 units of item B): 5 units

Item B (contains 15 units of item C): 3 units

Item C: 4 units

I would expect to see the following values in total stock available:

Item A (Child SKU - Item B): 5 units

Item B (Child SKU - Item C): 3 + 12 x 5 = 63 units

Item C (Child SKU: Blank): 4 + 63 x 15 = 949 units

This is an application that I have created to make stock take easier and find out how much value of stock we have.

[Paras_Sood]

What you are suggesting is pretty much what my approach was to solve this problem, but I was unable to do the calculations for tier 3 items (ie Item C inside of Item B inside of item A)

[Paras_Sood]

Maybe what I would have to do to fix this is potentially just have a tier 2 hierarchy. Parent-Child relationships and calculations, because grandchild calculations don't work out. I wanted to use the same app to create BoMs and Stock Take, that's why I thought this would be a good idea.

[diogolupcosta]

I can't see the . Between the 2 columns. Is it there?

[Paras_Sood]

I don't believe there should be a . right? Because [Related Relationships by Parent] is a ref_rows expression and hence a  list and for list dereference we don't use a .

Either way, I did try it out and I get the following error:

Column Related Relationships By Parent in expression '[Related Relationships By Parent].[Name]' does not contain a reference

[diogolupcosta]

So, if [Related Relationships By Parent] is not a ref type, is a list, you can't invoke it. I am sorry if I am miss understanding here.

[Paras_Sood]

Related Relationships By Parent is ref_rows expression right? or, a list of references.

What I am trying to do is that I am trying to add all of the column values where item in item table is the parent in relationships table

[diogolupcosta]

I get it now. I still can't understand the expression [Related Relationships By Parent][name]

Can u post a print of all the columns?

[Paras_Sood]

Note the column with the name "Ref Rows Parent" is just my attempt trying to fix this problem by using a select statement instead.