Is there a shorthand method for a referencing Parent field value while on the Child record?

[WillowMobileSys]

I have a parent table WorkOrders and a child table Labor. I want to implement a SELECT() for a dropdown to provide a list of Services to choose from. This available list shown needs to be filtered by the Service Type as indicated on the parent record.

Normally to access the parent Service Type field while on the Child record, I would use dereferencing dot notation like this - [Order #].[Service Type].

Additionally, n a SELECT statement, due to scoping, I would normally preface the child column with [_THISROW] in order to access the child value within the SELECT.

But it is not valid to combine these two into a SELECT to get something like:

SELECT(Services[Service ID], [Type] = [_THISROW].[Order #].[Service Type])

I know I can replace [_THISROW].[Order #].[Service Type] with another SELECT (wrapped in an ANY()) in order to access the Service Type field I want and get the SELECT above to work.

However, I am wondering if there some sort of shorthand that would allow using the dot notation in this use case?

Of course, other suggestions are always welcomed!!

[1minManager]

Can you explain a little more.

How is the list of services stored on Workorders?

Detail how your tables and columns are structured (sheet shot if easiest)

Simon@1minManager.com

[WillowMobileSys]

@1minManager Thanks for your interest. The setup for the issue is a bit lengthy. I was avoiding it since my question more of a general nature. BUT, in case it sparks a suggestion that helps, I’ll describe it here. I’ll start in the “middle”, as it were.

I have a WorkOrder table that is keyed by [Order #]. There is a child of this row for Labor performed - a list of services associated with the workorder. That table structure looks like this:

In the Labor FORM, I reference a list of services to choose from that come from a static table that looks like this:

But I want the dropdown list to be filtered by the Type equal to that chosen on the Work Order - the parent record for the Labor record.

So, when the Work Order is an “Installation” and I am adding Labor entries, when I tap the Service dropdown I want the list to be filtered to only the “Installation” type services:

To accomplish this I would update the Valid_If property on the Service ID column of my Labor Form.

The Problem

To do the above I need show the Services on the Labor Form filtered by the Type matching that on the parent record - Work Order

Ideally in a SELECT() when there is no column clash, I could write the needed expression like this:

SELECT(Services[Service ID], [Type] = [Order #].[Service Type])

Note the dereference of the parent record by [Order #].[Service Type].

But, to reference current row columns in the SELECT they need to be prefaced with [_THISROW]. That makes the expression syntax look like this

SELECT(Services[Service ID], [Type] = [_THISROW].[Order #].[Service Type])

This syntax, [_THISROW].[Order #].[Service Type], is currently not valid in AppSheet.

My Question

Since [_THISROW].[Order #].[Service Type] is not valid syntax, I was asking if there is any other known shorthand way of dereferencing a parent record column value or other suggestions that might make this logic easier.

I know I can replace [_THISROW].[Order #].[Service Type] with another SELECT() to get at the value I want but that starts to make the Valid_If expression messy. Throw on top of that logic that removes items from the list as they are selected and the Valid_if gets very lengthy.

Please let me know if anything is nor clear! Look forward to your response.

[Steve]

WillowMobileSystems:

I am wondering if there some sort of shorthand that would allow using the dot notation in this use case?

Nope.

WillowMobileSystems:

other suggestions are always welcomed!!

The only other option of which I’m aware is to carry the desired parent values into the child, which is ugly.

[WillowMobileSys]

Thanks Steve! Carrying fields down to the child is an option and definitely worth considering when there is more than one field that may need to be compared with from the parent.

It certainly gets messy when you consider you also need to keep them in sync should the parent change.

For now, adding the SELECT within the SELECT seems the best option.

I think I’ll open a feature request. It seems the dot notation should be supported in this manner. The whole purpose of the Parent/Child construct is to avoid replication of common fields across rows while still being able to access easily those common values.

[Steve]

WillowMobileSystems:

For now, adding the SELECT within the SELECT seems the best option.

Please consider the performance impact here. The inner SELECT() will be evaluated for each row the outer SELECT() scans.

[WillowMobileSys]

Yes, thanks! I am aware. Which is why a feature enhancement is needed. “Messy” usually translates to “Inefficient” in software.

But now that you have me thinking, a hidden Virtual Column is a better option.