Nested SELECT() within a SELECT() or SELECT() filtered by virtual column formula.

[Tom_Stevens]

Is there a way to use nested "Select()" statements to filter results from multiple tables? Or is there a way to use virtual columns to further filter "Select()" statements?

Here's the nuts and bolts:

I have 4 tables

1. Invoices - A place where you can make invoices, & gather purchases based on Tracking Numbers

2. Tracking Numbers - A list of numbers 1 to infinity 

3. Related Tracking Numbers - (A child of "Tracking Numbers") A list of Tracking Numbers and how they relate to each other if any relationship exists. For example Tracking Number 100 is the Parent to Tracking Numbers 101 to 110, and conversely Numbers 101 to 110 know that Tracking number 100 is their parent number. 

4. Purchase Logs - A list of purchases all with a related Tracking Number.

===========================

The issue is as follows:

Currently you create a new invoice entry, in doing so you pick a Tracking Number. You can then go on and select things in the purchase log that you want to add to the invoice. Currently the system limits your selections to just the tracking number that you have selected as follows:

SELECT(PURCHASE LOGS[Summary],[TRACKING NUMBER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID],TRUE)

This formula shows me a list of purchases in the purchase log by the Summary Data column and would look Something like this:

- Tracking number: 100 - x5 apples

- Tracking number: 100 - x6 oranges

- Tracking number: 100 - x1 plum

- Tracking number: 100 - x15 grapes

What I am attempting to do is have the valid if limit the list of purchases by either the selected Tracking number, or if the selected tracking number is present on the "Related Tracking Numbers" table have the list be filtered by Input tracking number and any related numbers it might find. For example: 

1. The user puts in Tracking Number 100, the system would show purchases for all of the related numbers in this case 100 (parent) and   101 to 110 (children).

or 

2. The user puts in any of the child Tracking Numbers (101 to 110),the system would still show purchases for all of the related numbers in this case 100 (parent) and   101 to 110 (children).

I have tried to make this happen a couple of ways.

My first attempt was to subtract a Select()-Select() in the "Valid if" as follows:

SELECT(PURCHASE LOGS[TRACKING NUMBER KEY ID],[Customer Name] = [_THISROW].[CUSTOMER],TRUE)
-
SELECT(RELATED TRACKING NUMBERS[TRACKING KEY ID], ([MASTER KEY ID] <> [_THISROW].[TRACKING NUMBER KEY ID]), FALSE)

But this result only shows the Key ID for the related tracking number, and I can't think of how I could get the summary to show

My second thought was to create a Virtual Column [Related Tracking Numbers] that would just list out all of the tracking numbers related to the number put in by the user. Then in the "Purchase" ref column have the Valid if constraint be Virtual Column [Related Tracking Numbers], but again this just lists all of the Tracking number key ID's and I can't think how to make it display the Purchase log[Summary]  in any type of "Valid if" formula with out it producing an error of a "Cannot compare List with Ref" type. 

Anyone have any thoughts / idea's on this? I'm sure it's simple, I've just been looking at it for too long.

[Tom_Stevens]

Further attempted this Valid If:

(
SELECT(PURCHASE LOGS[TRACKING NUMBER KEY ID], TRUE , FALSE)
=
SELECT(RELATED TRACKING NUMBERS[TRACKING KEY ID], ([MASTER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID]), FALSE)
)

But it seems to return everything in the purchase log 

[Tom_Stevens]

This formula works well, but only shows the Key ID's on the related items (the first select argument):

SELECT(RELATED TRACKING NUMBERS[TRACKING KEY ID], ([MASTER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID]), FALSE)
+
SELECT(PURCHASE LOGS[SUMMARY], ([TRACKING NUMBER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID]), FALSE)

[Tom_Stevens · Accepted solution]

BOOM GOT IT!

Added a Virtual Column: [Related Tracking Numbers] List (TEXT type) Formula: 

SELECT(RELATED TRACKING NUMBERS[TRACKING KEY ID], ([MASTER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID]), FALSE)
+
SELECT(PURCHASE LOGS[TRACKING NUMBER KEY ID], ([TRACKING NUMBER KEY ID] = [_THISROW].[TRACKING NUMBER KEY ID]), FALSE)

Then in the [PURCHASE LOGS] column Ref type under the "Valid if section" i added the following formula:

SELECT(PURCHASE LOGS[SUMMARY], IN([TRACKING NUMBER KEY ID],[Related Tracking Numbers]), FALSE)

IN() was the solution to make a "LIST" of KEY ID's a searchable / limiting field for a REF type column. The system now knows how to look at the main "Tracking Number" table and see if there are any related children on the  "Related Tracking Numbers" table and return a filtered list of items in the "Purchase Logs" Table for any item purchased with any value that is either the Master Tracking Number or any of the related child numbers.