Limit rows using a column from related table

Ray · 12-08-2020 03:28 PM

For the following statement - which is the start of a report - can I limit the number of Customer records that are displayed according to a setting in the related Orders table.

Open Orders Report <<NOW()>>

<<Start:SELECT(Customers[CustomerName], true,true)>>
Customer

Customer Name: <<[CustomerName]>>

The related table Orders has a column named Invoiced. Can I get this SELECT statement to only show records where [Orders][Invoiced] = FALSE?

Thank ou

Marc_Dillon

For sure. Please review this article for how to use SELECT statements.

And a bonus tip; always use FILTER instead of SELECT inside a START. FILTER is equivalent to SELECT( Table[key-column] … ) and you will never not be wanting to return a list of key values inside of START.

So in total:

<<START: FILTER( Customers , xxxx .....

Oops, I got this far without realizing a major detail about your question. I’ll leave the above still, perhaps just edit it for later:

How many Order records do you have per Customer? Do you want the customer to be shown in the workflow/report if a single Order record’s [Invoiced] column is FALSE. Or if all of them, or what exactly?

Ray

There can be several orders for the customers in the filtered list. But I only want to track the items that have not been invoiced so I’m not concerned about any orders that have been invoiced. If several orders appear in the list for a customer they should all match the FALSE condition (not invoiced).

Marc_Dillon

When you say “should”, are you saying that if one Customer has multiple Orders, they will always all be either invoiced or all not-invoiced?

Ray

It would be possible for a customer to have both invoiced and non invoiced orders within a billing cycle. The purpose of this report is to catch the uninvoiced records.

Marc_Dillon

Ok it sounds like you’re going to want nested START expressions.

We’ll start with the outer START expression to only select customers that have ANY non-invoiced Orders

Then the inner START expression will select only the related Orders that are non-invoiced.

<<START: FILTER( Customers , IN( FALSE , [Orders][Invoiced] ) )>>
##customer details display here##

<<START: SELECT( [Orders][key-column] , NOT( [Invoiced] ) )>>
##order details display here##

<<END>>

<<END>>

Oh yah, I just violated my bonus tip from above, haha. I used a SELECT in the inner START, but that’s because Appsheet doesn’t allow you to use a Virtual List of Refs type column ( i.e. [Orders] ) inside of a FILTER.

Ray

How does the relationship between Orders and Customers get established? Does the system make the connection automatically?
And what type of list is generated?
Shouldn’t the output from the OUTER expression be a list of Orders?

Marc_Dillon

The outer START expressions puts the workflow into the context of each Customer record, iteratively. The inner START then uses the related order virtual column which is already limited to only the Orders for each Customer.

START expressions always require a list of key values. Beyond that I’m not sure what this question means.

I don’t know. I was only guessing at what you need. You perhaps need to further explain your end goal.

Sounds like you may need to review this help article:

Ray

I really appreciate your patience. I’m having a hard time getting my head around the concept of the nested expressions. I will review the article you’ve suggested and then give my brain a rest . Perhaps it will make more sense in the morning.

Be safe and thanks again.
Ray

Marc_Dillon

Sure.

The suggestion above would produce an output similar to the following:

Customer A
    Order #768
    Order #982
Customer B
    Order #45876
Customer D
    Order #32375
    Order #20200

Where all displayed Orders are non-invoiced, and where a “Customer C” does not have any non-invoiced Orders, and thus is not listed. Is that what you’re wanting?

Ray

Exactly what I was looking for. But frustrating because I cannot get the concept… I’ll keep working through it.

Great to have your assistance.

Ray