Reference to column in another Child Table of Parent Table

[murad_athar]

Hi Guys!

I would like help with a reference expression for Product Id Column in Sales Returns Details table which is child of Sales Returns table which itself is child table of Orders table. Order Details is another child table of Orders table and not directly related to Sales Returns table.

Sales Returns Details table has reference of the sort ‘is part of’ (of the Sales Returns table)

I would like that the Product Id column in the Sales Returns Details table should show in drop down list only the products against Order Id of the Orders table that were previously sold and are being returned.

What should be the expression for Product Id column in Sales Returns Details table. It is correct uptill the following:

[Sales Returns Id].[Order Id].[Order Details]

It gives error if I add [Product Id] further in the chain above.

Kindly guide

[Marc_Dillon]

So your table ref hierarchy is like this, correct?

                Orders
                   /\
                  /  \
    Sales Returns     Order Details
        |
        |      
  Sales Returns Details

murad_athar:

[Sales Returns Id].[Order Id].[Order Details]

Here you try to go up to the grandparent record, then back down to a child (“uncle” of original record) ?

Dereferencing with the dot “.” only works through Ref type columns, which reference only a single record. Going back “down”, you have multiple records, which you can dereference a List with [ref_rows VC][column].

So, theoretically, your expression could/should be:

[Sales Returns Id].[Order Id].[Order Details][Product ID]

Notice the absence of the dot for the last one. HOWEVER , unfortunately , Appsheet does not support this.

You’ll have to run a SELECT across the Order Details Table. Probably something like this:

SELECT( 
  Order Details[Product Id] ,
  [Order ID] = [_THISROW].[Sales Returns Id].[Order Id]
)

[murad_athar]

Thank you!

It worked when put in ‘Suggested Values’ column. However, while in feeding in the form, list of Product Id appears, while feeding in Open Order Details form, list appears in the form of Product Name (lable).

However, I want that it gets stored in database as Id but appear in form as label.

Kindly guide if there is a solution for above because it is convenient for user to select from name, otherwise, no problem, it works fine.

Thanks again

[Marc_Dillon]

Is the [Product Id] column in Sales Return Details Table a Ref type?

[murad_athar]

It was of the type ‘Text’. I have tried putting it to Ref. but giving error.

[Marc_Dillon]

murad_athar:

but giving error.

…which is?

[murad_athar]

Column Name ‘Product Id’ in Schema ‘Sales Returns Details_Schema’ of Column Type ‘Ref’ has invalid ‘Suggested Values’ of ‘=SELECT(Order Details[Product Id], [Order ID] = [_THISROW].[Sales Returns Id].[Order Id])’. The type of the Suggested Values does not match the column type.

[murad_athar]

Same error message if Reference table ‘Order Details’ is selected

[Marc_Dillon]

Is Order Details[Product Id] a Ref type to another Table that you’ve yet to mention, a “Products” Table?

What exactly are you wanting for the Sales Return Details[Product Id] column? Is it supposed to reference a record in Order Details, or reference a record in Products?

[murad_athar]

Order Details[Product Id] is Ref of ‘Products’ Table. I want that it is supposed to reference a record in Products but only those that appear against that particular Order Id in Order Details. I want to reduce the chance of a product being entered in Sales Returns Details that was never sold against the Order Id and entered in Order Details table.

Please let me check if ref to Product table solves the problem

[murad_athar]

Ref to Products table solved that problem. Only those products are appearing in the list that are previously sold.

Thank you so much!

[murad_athar]

Hi Marc

Can you please also guide how to get the exact Rate of Sales Return if [Rate Per Unit] column is in ‘Order Details’ table.

The following function gives random chosen value:

ANY(SELECT(Order Details[Rate Per Unit], ([_THISROW].[Product Id] = [Product Id])))

How can exactly the same (single) value be chosen which when multiplied by quantity returned, gives value of sales return.

Now Product table is parent of Sales Returns Details Table and Order Details table (after ref of Products table was given in [Product Id] column of Sales Returns Details table.)

[Marc_Dillon]

murad_athar:

ANY(SELECT(Order Details[Rate Per Unit], ([_THISROW].[Product Id] = [Product Id])))

I don’t really understand your question. Does the above expression not return the value that you expect?

[murad_athar]

Hi Marc

I want that the same rate Value is returned from the expression at which the product being returned was previously sold. User just selects the product and enters quantity.

Above expression is chosing one of the many rates in the [Rate Per Unit] column in the Order Details table at which the product was sold.

If you may please guide a dereference expression that product Id and Order Id in Order Details table is equal to Order Id in Sales Returns table and Product Id in its child table Sales Returns Details.

[murad_athar · Accepted solution]

murad_athar:

SELECT(Order Details[Rate Per Unit], ([_THISROW].[Product Id] = [Product Id]))

I have put the following formula in a virtual column [Sales Returns Rate] in Sales Returns Details table and it has worked

ANY(SELECT(Order Details[Rate Per Unit], AND(([_THISROW].[Product Id] = [Product Id]), [Order Id] = [_THISROW].[Sales Returns Id].[Order Id])))

Thanks again