quick question about constraints. i am tryin...

[Seth_Berman]

quick question about constraints.

i am trying to create a detail view in my app that acts as a report for several tables by creating countif and sumif virtual columns.

i need to figure out how to use the SELECT () or FILTER() app formulas properly.

i can get it to work for a single constraint, as in all records after ‘table1’ [begin date] but i cant get it also limit the selection to only those records before ‘table1’[End Date]

i could not figure out using a combination of SELECT + AND, so i tried putting one select statement in the app forumla section and the other in the valid if section, which did not work.

does appsheet support complex selections with multiple constraints?

[Steven_Coile]

It’s not clear to me what you’re trying to accomplish, so let me make some assumptions:

1. You have a report table (Reports).

2. Each row of Reports constitutes one report.

3. The “detail view” you mention is the detail view for the rows of Reports.

4. The begin and end dates you mention are columns of the Reports table. We’ll call these columns BeginDate and EndDate.

5. You want a report to count some values from another table (Orders). We’ll count all non-blank values in the SpecialInstructions (e.g., the number of orders that had special delivery instructions for the period).

6. You want the report to sum values from yet another table (Deliveries). We’ll sum the DeliveryCharge column (e.g., the total delivery charges during the period).

7. The values counted and summed should be limited to rows with timestamps within the begin and end time for the report row. Each table has a DateDone column that has the timestamp.

8. The count total will go into the SICount column of Reports.

9. The sum will go into the DCTotal column of Reports.

The app formula for the SICount column of the Reports table:

=COUNT(SELECT(Orders[_ROWNUMBER], AND(ISNOTBLANK([SpecialInstructions]), ([DateDone] >= [_THISROW].[BeginDate]), ([DateDone] < [_THISROW].[EndDate])))

• SELECT(Orders[_ROWNUMBER],

…) gets a list of row numbers for select rows in Orders. I use _ROWNUMBER just because it’s a column in every table that will always have a value, ensuring every item in the generated list gets counted.

• AND(…, …, …) limits the SELECT() results to only those rows that match all of the conditions.

• ISNOTBLANK([SpecialInstructions]) ensures we only count rows that contain special delivery instructions.

• ([DateDone] >= [_THISROW].[BeginDate]) limits the count to only rows with a DoneDate no earlier than (>=) the report’s BeginDate.

• ([DateDone] < [_THISROW].[EndDate]) further limits the rows to those with dates before (<) the report’s end date.

• COUNT(…) counts the number of items in the result of the SELECT().

The app formula for the DCTotal column of the Reports table:

=SUM(SELECT(Deliveries[DeliveryCharge], AND(([DateDone] >= [_THISROW].[BeginDate]), ([DateDone] < [_THISROW].[EndDate]))))

For this one, I didn’t use ISNOTBLANK() because a blank DeliveryCharge value is equivalent to 0, which doesn’t adversely affect the sum.

[Steven_Coile]

You could instead use FILTER() for the count if you wanted:

=COUNT(FILTER(“Orders”, AND(ISNOTBLANK([SpecialInstructions]), ([DateDone] >= [_THISROW].[BeginDate]), ([DateDone] < [_THISROW].[EndDate]))))

FILTER(“Table”, …) is equivalent to SELECT(Table[KeyCol], …), where Table is the table name and KeyCol is the name of the table’s KEY column.

[Seth_Berman]

+Steve Coile

the table TestReport contains a virtual column with this SELECT expression in it.

SELECT(

testdata2[Pickup Date],

AND(

(testdata2[Pickup Date] >= [_THISROW].[Begin Date]),

(testdata2[Pickup Date] < [_THISROW].[End Date])

)

)

I get this error:

Cannot compare List with DateTime in (TESTDATA2[Pickup Date] >= [_RowNumber].[Begin Date])

had similar issue with both the Count(Filter) and Sum(Select) expressions.

clearly something is configured wrong, not sure where to look.

[Steven_Coile]

Ah, I see. Remove the table names within the conditions, as such:

SELECT(

testdata2[Pickup Date],

AND(

([Pickup Date] >= [_THISROW].[Begin Date]),

([Pickup Date] < [_THISROW].[End Date])

)

)

In AppSheet, when you combine a table name with a column value reference, as with testdata2[Pickup Date], this is a column list expression, and produces a list containing all of the values of that column in that table. So, in this:

(testdata2[Pickup Date] >= [_THISROW].[Begin Date])

you’re trying to compare a list of values (testdata2[Pickup Date]) to a date ([_THISROW].[Begin Date]), which is the problem. So remove the table name.

[Seth_Berman]

+Steve Coile that worked!

thank you for the explanation.

out of curiosity, if i want to select on a column in a row = something, and then return the whole row to list in the report, how would i do that?

[Steven_Coile]

You would use FILTER() to select the desired rows and return a list of KEY column values. When AppSheet sees a list of KEY column values, it will present the list as a table in a table view.

FILTER(

“testdata2”,

AND(

([Pickup Date] >= [_THISROW].[Begin Date]),

([Pickup Date] < [_THISROW].[End Date])

)

)

would return the rows from testdata2 with Pickup Date column values within the begin and end dates and present them as a table within the report’s detail view.

[Seth_Berman]

+Steve Coile THANK YOU very much

for such an

involved and complete response!

sorry my question was hard to understand.

we do have multiple tables with data that is input from forms.

we want to create a summary report that can be accessed frequently to get snapshots of how we are doing.

the report will need to be able to change its date range and selected client thru filters or constraints.

you basically hit the nail on the head.

i will work on implementing it this way to see how it works out.

[Steven_Coile]

I’ve added the COUNT() example to the documentation. COUNT() help.appsheet.com

[Steven_Coile]

And the SUM() example. SUM() help.appsheet.com

[Seth_Berman]

+Steve Coile sorry to bother you again.

when using your sum example:

SUM(

SELECT(

‘test-data2’[Specimens],

AND(

(‘test-data2’[Date] >= [_THISROW].[Begin Date]),

(‘test-data2’[Date] < [_THISROW].[End Date])

)

) )

its producing a syntax error:

Condition AND(“test-data2”, “test-data2”) has an invalid structure: subexpressions must be Yes/No conditions

table test-data2 has my sample data and contains column [Date], table test-report is where the virtual column for this expression is located.

[Begin Date], [End Date] columns are also in test-report.

[Steven_Coile]

@Seth_Berman remove the quotes from around the table names. Does that make a difference?

[Seth_Berman]

+Steve Coile that seemed to help, thank you, for other peoples info, removing the single quotes caused Appsheet to see the dash in the table name as a problem so after renaming

the tables it was happier.

i am still getting an error in the formula about comparing lists with dates but I am trying to track it down, i must have something configured wrong.

[Seth_Berman]

+Steve Coile i’ve spent several hours trying to figure adapt the expressions you suggested and not receive the error : “Cannot compare List with Date”.

do you have any pointers for me to follow up on that would help clean up the syntax?

[Steven_Coile]

@Seth_Berman What is the current expression?