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
Solved! Go to Solution.
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
So your table ref hierarchy is like this, correct?
Orders
/\
/ \
Sales Returns Order Details
|
|
Sales Returns 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]
)
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
Is the [Product Id] column in Sales Return Details Table a Ref type?
It was of the type โTextโ. I have tried putting it to Ref. but giving error.
โฆwhich is?
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.
Same error message if Reference table โOrder Detailsโ is selected
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?
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
Ref to Products table solved that problem. Only those products are appearing in the list that are previously sold.
Thank you so much!
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.)
I donโt really understand your question. Does the above expression not return the value that you expect?
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.
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
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |