De-reference

[reggieneo]

Hello again,
I have referenced the timestamp (a key from my D Table) by creating a column name “Timestamp Key”. From here I easily de-ref other values as “shift” and “ORDER ID”
e.g [Timestamp Key].[Shift] and [Timestamp Key].[Order ID] .
but when I tried to de-ref the “order value” using the same method it just couldn’t get it.
i made this through “part of” activated.
I have even went into doing Any and Select expression and still won’t give me the answer. No error. just blank.

I have tested to get Order ID and it returned the “correct ID”, but when I change and look for the “order value” instead it returned blank:

my expressions:
Any(Select(Dipstick[Order Diesel],[Order ID]=[_Thisrow].[Order ID])) this returns blank
Any(Select(Dipstick[Order ID],[Order ID]=[_Thisrow].[Order ID])) this returns the Order ID

what I am missing here?

\

Thanks

[hru]

Have you tried to move the expression from initial value to app-formula? and try to edit a record to update the spreadsheet?

[reggieneo]

reggieneo:

Any(Select(Dipstick[Order Diesel],[Order ID]=[_Thisrow].[Order ID]))

hi Heru,
it is currently in the app formula. and set to editable.

.

just doesnt work.

[reggieneo]

Hello anybody can help me with this? i have tried doing all and i am stuck. hate to think this is a bug??

[Suvrutt_Gurjar]

I believe you may wish to further add on following for community to help you better.

Is the D or Dipstick table the parent table? It sounds that [TimeStamp] is the key of your parent table? If [Timestamp] is the key of your parent table, could there be more than one record with the same order ID in that table?

You have given two expressions but could you please mention what dereference expression you are using for [Order Value] and is the standard expression [TimeStamp Key].[Order Value] just like other dereference expressions is also returning blank?

Also you have mentioned [Order Value] to extract at one place, however in your expressions sgared and picture shared are two other columns [Order Diesel] in Dipstick table and [Diesel Supposed Balance] in Sales table. Are these same as "Order value"in two tables?

[reggieneo]

Hi Suvrutt,
Sorry, the D captioned in my image is wrong, it should be the child Sales Table and D or Dipstick table is the parent table.

there is only one record (4000) using this Order ID in Parent Table.

I have tested " [Timestamp Key].[Order Diesel] " to get this Order value which is 4000 but it was blank.
When I used [Timestamp Key].[Order ID]" it has given me the correct result.
I have also use the expression to get the Order Diesel “Any(Select(Dipstick[Order Diesel],[Order ID]=[_Thisrow].[Order ID]))” but it was blank.

The name of the column where I need this Order Diesel is [Diesel Supposed Balance] in Sales Table (child) as I mentioned above.

I appreciate your help.

thanks

[Suvrutt_Gurjar]

@reggieneo,

Thank you for all the details. Have you added these formulas to compute “Order value” in child table after records existed in both the tables?If so , could you please once open and go into edit mode and save back for that child record where you are expecting 4000 with the formula [Timestamp Key].[Order Diesel]

[reggieneo]

the order value or order diesel is initially available in parent where i intend to extract and put in child sales table, column [Diesel Supposed Balance].
Yes I have added and tested in separate time the [Timestamp Key].[Order Diesel] and “(Select(Dipstick[Order Diesel],[Order ID]=[_Thisrow].[Order ID]))”, both returned blank.

i may need to tell you that there are 12 tables in this app and the record starts from the Dipstick table and the Timestamp key of child sales table has the "part of " activated ( part of Dipstick table).

[Suvrutt_Gurjar]

Hi @reggieneo,

What I mean is once you add these formulas in the respective columns and save the formula through editor save, have you tried to open the corresponding child record in the app, go to edit mode and save that record again. The formulas of real columns reflect in detail views and back end after the corresponding existing record is opened, edited and saved again.

[reggieneo]

yes i have saved and tried opening it in-app.
the column is not showing in the app as they returning blank.

[hru]

If all the column names were correct, and [Order ID] is available in both table, create 2 new Virtual Column for test, try with both app formula:

ANY(Select(Dipstick[Order Diesel],[Order ID]=[_Thisrow].[Order ID]))

or:

ANY(Select(Dipstick[Order Diesel],[Timestamp Key]=[_Thisrow].[Timestamp Key]))

[reggieneo]

hi,i have tried them both returned blank.

[hru]

If you have tried them in new virtual columns, it’s an indication that there is no [Order Diesel] data matching between both tables with that condition, either with [Order ID] or by [Timestamp Key]. Whiles [Timestamp Key] is a ref between the table.

I can only suggest to re-check the source data, re-check if there is a condition filtering the relation between the tables (maybe valid_if on Timestamp Key) or you might want to send email to support@appsheet.com for better assistance.

[Suvrutt_Gurjar]

Hi @reggieneo,

Thank you. Is the parent table column [Order Diesel] also of decimal or number or price type and editable ? Because your column type in child table seems to be Decimal type and is editable as you mentioned.

You may wish to reverify the parent record where value 4000 exists in column [Order Diesel] is the same that you are referring in child table.

Or else, I am sorry that I have no further suggestions. I could pull a decimal, price or number type column value into child table’s decima type column with a simple dereferencing expression.

[reggieneo]

I have re-verified but all is type aligned. really can’t make it work.
thanks much on your help

[reggieneo]

thanks Heru,
i have written email to the appsheet already.
may i ask you?
what i know is that when i am referring to “[_This]”, we are actually referring to the current value in the app environment like this in the image attached?:

because this is actually what i have right now and it seems to be working thus far with the others. that being said i think i have the matching criteria to extract the order value. I am correct to think this?

[hru]

For that it should be _THISROW, you got me wondering if on the same new virtual column, you can test with LOOKUP([_THISROW].[Order ID], “Sales Table”, "Order ID’, “Order Diesel”) ?

I hope you get the answer soon.

[reggieneo · Accepted solution]

Hello Suvrutt / Heru,
i have finally found the solution and thought i need to share my premature understanding.

it boils down to my confusion of this cross table relationship.
i was assuming that the criteria in select expression or my de-ref should be from the current sales app view/environment.or the existing parent tables values. apparently the order id is showing in sales table due to it was called in to pre-populate the order ID field in dipstick form.

So i copied the same approach and created an initial value formula to automatically fill in the order value in dipstick form view before it goes to the sales form view).

[Suvrutt_Gurjar]

Hi @reggieneo,

Glad to know that you ultimately got the root cause. Thank you very much for sharing the reason and the solution. I believe it always helps the community to know the solutions.

All the very best.