Using Related Rows in Expressions? I am tr...

[Jason_Spicer]

Using Related Rows in Expressions?

I am trying to see if I can do a ref column in a table that uses a valid if expression to only show related rows of the the row being filled out in the form. Pretty much I have

a door table with a reference column to a materials order table and when selecting the column with the references materials order, I want it to only show rows related to that Door ID and if there is none, to be able to select new to add one?

I tried different expressions and get the error message that you can’t compare text in a list.

Thanks for any help with this.

[Bellave_Jayaram]

Try SELECT(Materials Order[KeyCol],[Door ID]=[_THISROW].[Door ID])

[Jason_Spicer]

Thanks but that just shows me all the material orders. Also, there is not [Door ID] in the Materials Order table, only the related Doors virtual column.

[Jason_Spicer]

I have seen related rows used in expressions such as SUM, but with the normal SELECT and using [_THISROW][Door ID]=[Related Doors] I get the cannot compare text with List error.

[Jason_Spicer]

I think I was able to get this to work. I skipped trying to use a related rows in my validation and instead of creating the column in the materials order for the door or any other table, I just validated it back to the reference column in the door table that references the material order table.

=SELECT(Materials[Material ID], [Material ID] = [_THISROW].[Door Material])

. When I create a new door in the form and pick the Door Material column (which references to the Material Order table) it shows nothing, then I select new, create the new material order, save it, and it brings me back to the same door form with the column fill out with what I just created, save the door form, and everything is there, the new door, the material order for that door.

Thanks for your help with trying to help me with this. Figuring out just made the possibilities for my apps to become even more amazing! Sometimes, you just have to bang your head for a while, try new things, and sometimes the answer just appears!

[Bellave_Jayaram]

Which table are you creating the row in and where/what is the Ref column and what column are you trying to put the Valid_If in?

[Jason_Spicer]

I am creating a new door row in the door table and the ref column is in the door table.

The ref column points to the material orders table. When selecting the ref column, I want it to show the related materials orders related to that door id (if editing it, would like it to show the one that was assigned to it) and if there are no related rows, to be able to select new and create a new materials order for that door id.

[Jason_Spicer]

Thanks for your help with this Bellave!!!

[Bellave_Jayaram]

I don’t understand why you want to do it that way. Why can’t you save the row and then add New (material orders) items via the inline view?

[Jason_Spicer]

It eliminates the separate step of adding the material item after the door row form is completed and there are 6 materials that will need to be added for that door and helps avoid forgetting to add one of those materials after you complete the door form and then trying to remember adding those six materials.

If they are in order while completing the form, it will be less likely missed. Also, each material column can filter diwn the materials dropdown list by what the material is. Example, “Needs hinges?” , if yes, the material column for that appears and only shows hinge materials.

[Bellave_Jayaram]

I haven’t tried this. @Aleksi_Alkio is sure to know how to do this, if it’s possible to create rows in a child table before the parent row is saved in the same form.

[pravse]

It sounds like you need the Ref to have the ‘IsAPartOf’ property. If so, the Material orders are really a nested part of the Door entry. These can be created together at one shot. See help.appsheet.com - References Between Tables References Between Tables help.appsheet.com

[Jason_Spicer]

I am able to limit the rows of the valid if dropdown by the select feature with using a a column shared by both such as room id or property id, but my material orders table doesn’t have a door id column, which is why I am trying to use related doors virtual column in the expression to filter down the valid if dropdown.

If I

need to create another reference in the materials order table for this to work, my materials order table will get huge with all the other tables that will need their column in there such as drywall, painting, flooring,

drywall, etc, etc.