Grandchild from Parent table

Hi,

I have a Yes/No slice of a main Parent table [Full List Table]. Rows are selected baed on data in the child table “Order”.

The expression is below and works fine

ISNOTBLANK( FILTER( “Orders”, AND( ([Outlet No] = [_THISROW].[Outlet No]), [Complete Order Total Inc Vat]>0, ( [Delivery Date] = LOOKUP(
[Unique ID], “Running Sheet 2”, “Unique ID”, “Delivery Date” ) ), [Order Status Calc]=“Open”,
IN([Route] ,(LOOKUP( [Unique ID], “Running Sheet 2”, “Unique ID”, “Route” )))
) ) )

Rather than use “[Complete Order Total Inc Vat]>0” as part of the selection I would like use the grandchild table “Order Details” and only select Rows in the main table that have orders that satisfy the obove criteria but also only ones that have order details for those orders where [Product Name] is not “OTHER- pick up casks etc”.

“Orders” table uses [Order Id] to fetch order details from the grandchild table.

I’m struggling to write the expression with child and grandchild in there.

thank you

Phil

1 Like

Hi @Phil_Waite ,

If I have correctly understood your requirement, please try a sub expression something like below added as an additional condition to the AND () clause

NOT (IN( "OTHER- pick up casks etc”, ([Reverse Reference Column of Order Details in the Orders Table][Product Name])))

1 Like

Thanks Suvrutt,

I realised that if an order has 3 order details and one is “OTHER - pick up casks etc” then that is left out too. I want to exlude outlets in the main tabe that have orders with only an order detail that has “OTHER - pick up casks etc” and nothing else.

For example below the “OTHER” is present but there are more parts to the order.

The below example has the same order ID. I want to include this one as they are othe products under product name.

Delivery date OrderDetail Id Order Status Name & Town Outlet No Order Id Product Name
08/07/2020 68ab3f2c Open Bell Waltham St Lawrence 1825 f6ffc156 Lister’s Special Ale 9G 4.6%
08/07/2020 7234651b Open Bell Waltham St Lawrence 1825 f6ffc156 Lister’s Westmorland Dark Mild 9G 3.7%
08/07/2020 62bff266 Open Bell Waltham St Lawrence 1825 f6ffc156 Lister’s American Pale Ale 9G 4.2%
08/07/2020 e9b0b32a Open Bell Waltham St Lawrence 1825 f6ffc156 OTHER - pick up casks etc

thanks

Phil

Hey @Phil_Waite, I just wanted to chime in here and say something about SELECT() formulas inside SELECT() formulas.


ISNOTBLANK(
	FILTER(
		“Orders”,
		AND(
			[Outlet No] = [_THISROW].[Outlet No], 
			[Complete Order Total Inc Vat]>0,
			[Delivery Date] = LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Delivery Date” ),
			[Order Status Calc]=“Open”,
			IN([Route] ,LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Route” ))
		)
	)
)

If we take out the ISNOTBLANK() part and just focus in on the FILTER() expression, another re-write of this would be:

SELECT(Orders[OrderID], AND(
	[Outlet No] = [_THISROW].[Outlet No], 
	[Complete Order Total Inc Vat]>0,
	[Delivery Date] = ANY(SELECT(Running Sheet 2[Delivery Date], [Unique ID] = [_thisrow].[Unique ID])),
	[Order Status Calc]=“Open”,
	IN([Route] , ANY(SELECT(Running Sheet 2[Route], [Unique ID] = [_thisrow].[Unique ID])))
))

You’ve got a main SELECT() with TWO sub-select() formulas inside. :scream:

Generally speaking, it’s a bad idea to have a select() (or any port: LOOKUP(), FILTER() ) inside another SELECT() - the resulting cascade of calculations can REALLY slow things down inside your app, depending on the amount of data being parsed, the complications of the various formulas, etc.


If I’m interpreting your formula’s intention inside the slice, what you’re wanting to do is isolate out certain orders based on data from their order details right?

On of the things I like to do when building complicated multi-stage logic like this, is to create an “Order Status” virtual column that contains an App Formula that does these types of calculations - but on the order level.

It’s in the technical weeds on why this is a more efficient way of calculating things, but the short version is: Certain calculations are made a certain times, and others at others. If you can store the result of a calculation somewhere (typically in the form of a virtual column of some sort) then the system doesn’t have to re-do that calc over and over again.


I would see about creating a virtual column that would hold a version of this formula. A benefit from moving this into the Order table is that now you have easy and efficient access to the [Related Order_Details] column and the amazing feature called a “list dereference.”

As was suggested by:

As you can see in the " [Reverse Reference Column of Order Details in the Orders Table][Product Name] " part.

If you can move these calculations into a physical column, that’s even better; but this can only be accomplished in certain circumstances.


And in regards to the LOOKUP formulas inside your Filter():

Is there a way you could store that lookup result somewhere? What exactly is that part of the formula doing? If you could provide a little more detail on what that’s doing for the formula, maybe I can propose an efficient way to store it.

Anyways, sorry for the long essay of a post. :nerd_face:

3 Likes

Very well explained @MultiTech_Visions,

You are correct that one multi row formula within another can drastically slow the processing.

Typically I also mention this very valid point in post responses.

Your suggestions such as moving processing to VCs are very useful.

Thank you for an insightful post.

2 Likes

My general rule of thumb is to try and keep everything I can stored inside a PHYSICAL column - that way the calculations are only run when triggered.

2 Likes

Hi Muti_Tech,

Thank you. I under stand what you are saying. I will have a look at adding VC columns and break down the expression that way. If I am correct in understanding what you have suggested then in fact I have had to do that to get download actions to work on other parts of the app.

The download always came out with nothing but column headers. The function allowed the user to define criteria for filtering the main table data and there was an expression that lookedup the values etc. By breaking it down into VCs the download of the much simplified final expression worked.

Thanks

Phil

1 Like

Hi @Phil_Waite,

On your requirement of further classinfying order details in to exclusive “OTHER - pick up casks etc” where all rows belong to one single product name of that type, it could be done with following expression such as

NOT(COUNT([Reverse Reference Column of Order Details in the Orders Table][Product Name])= COUNT(SELECT([Reverse Reference Column of Order Details in the Orders Table][Product Name], [Product Name]="OTHER- pick up casks etc”)))

The expression checks the count of related order details for an order is equal to the count of those order details with product name as "OTHER- pick up casks etc” and if so that order is not to be selected.

However it will be unwieldly. As @MultiTech_Visions suggsted, you may wish to break your slice expression and use the above at appropriate place.

1 Like

I would use this as a sub-status column. I typically will have several columns involved in determining the status of a record.

  • I’ll have a manual entry, where people can physically change the status - like admin types closing something that was left open or canceled.
  • I’ll have formulas like what @Suvrutt_Gurjar suggested above, giving me an easy true/false to work with for if some criteria is met
  • Then one final [Auto_Something_Status] (as I typically call it) that’s watching all the various parts and making a final determination.

The real benefit of splitting things up is that you can use them later on in other integrations or automations you might build. :slight_smile:

2 Likes

Useful, practical inputs @MultiTech_Visions

1 Like

Thank you guys, the expressions works fine. I will split it up in to VC too

Cheers

2 Likes