Virtual column in detail order with value from another table

[Filip_Kuna]

Hi,

I would like to ask a question about virtual columns.

I have an app which is about order management and I would like to add some values from different views to order detail view. I have made this formula, which seems right, but it doesn't show any value.

SELECT(Job[Hours], ([Order ID] = [_THISROW].[Order ID]), TRUE)

I need value from view Job, column Hours, which is on the same row as Order ID of my Order, value should be decimal. This value should be only informational, thats why I want to use virtual column.

Thanks for your time

[Craig_QREW]

Check to make sure you have used the proper column names. for instance say that:

Job table: 

Hours and order ID

Order table:

ID 

Order Info

It should go like this. SELECT(Job[Hours],[Order ID]=[_THISROW].[ID],TRUE) So the column that comes after [_THISROW] needs to be in the Order table, not from the Job table as the rest of this expression is. Please keep that in mind. 

Also this expression will return a list. If it is not returning any values then for whatever reason the condition you have as your second parameter is never true. 

Also this expression should be placed on your ORDER table as you want to know the hours for each order. If none of what I said is helpful then I would request to see a screen shot of your expression in AppSheet as well as where you are putting it and the columns for the tables you are using. With that I am certain I or someone else would happily help you figure this out. 

Craig

QREW Technologies

[Filip_Kuna]

Hi Craig,

Thanks for your reply.

Here is what I am trying to do and screenshots of it:

This is the view (Order detail) where I want to add the virtual column displaying value of Hours from Job table. Both rows in both tables have same ID which is "Zakázka". I would like to connect them together through this ID, because they are not unfortunatelly on the same row number.

Here is a Job view:

This value I would like to show on my Order detail view as a virtual column. Just informational not editable. 

Thank you so much in advance.

[Craig_QREW]

Thank you for waiting and for the screenshots. Sounds like you would use REF_ROWS(Práce, Hodiny) 

This would return a list of all the related Hodiny to the specific Práce. Also when AppSheet makes app it often times creates the column for you using this function but sometimes it does not do it so this is how you can do it yourself. 

This should be great for you. Below I have attached a few links that are helpful for this kind of thing. REF_ROWS confuses me sometimes but I believe it will solve your problem. Generally when you are trying to get information into a parent table from a child, REF_ROWS() is the answer. In the documentation link it actually shows you what the function is equivalent to if you were to use a select expression. 

So these two functions do the exact same thing. The select is what you were trying to write earlier but this is the easier way that has some amount of built in ease for you. 

• SELECT(Order Details[Order Details ID], ([_THISROW] = [Order ID])).

• REF_ROWS("Order Details", "Order ID")

I feel confident about this working but if it doesn't let me know because I am interested in being of help where I can be. 

Craig

QREW Technologies

REF_ROWS() Video  

REF_ROWS() Documentation 

[Filip_Kuna]

Hi Craig,

I have tried your solution and watched the tutorial and I think its not it or I might be just a little confused 😄

If I use REF_ROWS then Hours (Hodiny) from table Job (Práce) should be type Ref right? But I need them to be type decimal, because first I work with table Orders where I create details about order, then I create another row in Job table which contain column Hours. So default value for virtual column Hours would be " " and after job is done on the order then we add row to Job with Hours column. 

Here is our workflow for better idea how it works

1) Order table - creation of new order - a) https://prnt.sc/26qquya b) https://prnt.sc/26qqy22

2) Order detail > Job table - creation of new Job connected directly to our new order - a) https://prnt.sc/26qr038 b) https://prnt.sc/26qr2dq 

3) This is the result I want to accomplish after I create a job connected to our order - https://prnt.sc/26qr6c2 

Thanks for your time, I really appreciate it.

[Craig_QREW · Accepted solution]

Okay, here is what I think should work. In the column for hours. We should be able to enter something like this. 

SELECT(Práce[Hodiny],[_THISROW].[ID]=[zakazka])

This will find the hours associated with the order we have. This is not substaintially different than REF_ROWS() but if that wasn't working then this is another option. 

Then for the type of the column here it would be a decimal and you can set how many digits and such you would like. I believe this would work, I have tested it in an app with a similar structure.

The final thing I would say, I noticed that your zakazka ref column in your Práce table is; a reference, a key, and a label. I have never done that with something. It may be giving you some trouble having it be both a reference and the key I would recommend nott using the same column as both a foreign key and a primary key. You could have a column on the left be an ID column that serves as your primary key then keep that as the next one for the foreign key. (Column 1: ID column 2: foreign key ... everything else the same. Then regenerate structure.)

I believe this should solve it. If it still gives you trouble. I can try to mimic your app and include some screenshots but I believe this is the solution. 

Craig 

QREW Technologies

[Filip_Kuna]

Hi Craig,

It works, thank you so much man. I really appreciate your help and patience 🙂