If the Count of Related Parent Records is this then do this

I’m building an app for a ‘make-to-order garment manufacturer’
There is a 3 level hierarchy between within the order system,
Parent Table is ‘Orders’
Child Table is ‘Order Details’
Grandchild is ‘Order Detail Fabrics’

Once the user has created an Order, and added an Order Detail, and added an Order Detail Fabric, and then added a second Order Detail Fabric (for that same ‘Order Detail’), I would like the following to occur;

Inside the grandchild form (Order Fabrics Details Form) i would like the user to be able to choose from a range slider that controls the percent of the current Fabric they are adding vs the one they already added, this will be called the [Fabric Blend] field

So my first job is to count the ‘Order Detail Fabrics’ of the current Order Details Id.
I’m checking to see if there is a count of greater than 1,
if TRUE then show the Fabric Blend range slider field

I have created a VC in the ‘Order Details’ table called [Fabric Record Count] with the formula =
COUNT([Related Order Detail Fabrics][Order Id])
(Related Order Detail Fabrics is a List Type VC using the formula =
REF_ROWS(“Order Detail Fabrics”, “OrderDetail Id”)

This appears to be working although i cant figure out why as the COUNT formula is grabbing the Order Id so i’m already lost here, I think i need to change this formula to reflect the OrderDetail Id?
but assuming that is correct, I’m next trying to build a column in the ‘Order Detail Fabrics’ table called [Fabric Blend]
within the SHOW_IF, field my formula here should be something like =
IF(
SELECT(
Order Details[Fabric Record Count].[_THISROW] = [OrderDetail Id],
>1,
“Yes Blend”, “No Blend”
))

But I keep getting ‘Cannot compare List with Number’ error

I know I’m going about this wrong i think i need another VC with a text type that computes the yes/no value first?

Any assistance is welcome, cheers

Yes.

That’s because SELECT() produces a list of records - you’ll need to either SUM() the resulting list, or pull the first item in the list out with INDEX().

But I would imagine that there’s an easier way to get the data you want.

Check out de-references: I would think you could de-reference the value you want through the Order reference.

1 Like

Thanks your right, was struggling to wrap my head around that dereferencing but have got it now!

I’m stupid on smart day :roll_eyes:

2 Likes