LinkedIn
Twitter
Hey @nocodebuilder, welcome to the community.
First: you might check out the following post, itโs full of tons of helpful tips to help you get stared with using the community to find answers.
To answer your question
To give you an example, hereโs a formula that I use to control content inside (what I call) an enhanced dashboard.
Enhanced Dashboard???
The Basic Dashboards Of AppSheet | AppSheet Explained - YouTube
and(
if(ISNOTBLANK(index(Current_User[User_Current_Fields], 1)),
in([Trapping_Location_Field_Link], LIVE_Scouting_Fields[Farm_FieldsID]),
in([Trapping_Location_Farm_Link], LIVE_Scouting_Farms[GrowerFarmsID])
),
if(ISNOTBLANK(index(Current_User[User_Crop_Type_Filter], 1)),
IN([Trapping_Location_Crop_Type],
split(CONCATENATE(Current_User[User_Crop_Type_Filter]), " , ")
),
true),
if(ISNOTBLANK(index(Current_User[User_Trap_Type_Filter], 1)),
isnotblank(INTERSECT(
[Trapping_Location_Trap_Types],
split(CONCATENATE(Current_User[User_Trap_Type_Filter]), " , ")
)),
true)
)
Hereโs another one from a report builder in a clientโs app
- It takes criteria entered by a user and finds the associated records that match all that criteria.
SELECT(patient_enrollment_records[PatientEnrollmentID],
AND(
IN([patient_link], [_ThisRow].[Report_Patient_Filter]),
IF(isnotblank([_ThisRow].[Report_Hospital_Link]),
[_ThisRow].[Report_Hospital_Link] = [Hospital_discharged_from],
TRUE),
IN([facilities_link], [_ThisRow].[Report_Facility_Filter_ID_List]),
SWITCH([_ThisRow].[Report_Status_Filter],
"Active",
AND(
[Enrollment_Active] = TRUE,
IF(IsNotBlank([_ThisRow].[Report_Date_Min]),
[Enrollment_Date] >= [_ThisRow].[Report_Date_Min],
TRUE),
IF(IsNotBlank([_ThisRow].[Report_Date_Max]),
[Enrollment_Date] <= [_ThisRow].[Report_Date_Max],
TRUE)
),
"Discharged",
AND(
[discharge] = TRUE,
IF(IsNotBlank([_ThisRow].[Report_Date_Min]),
[discharge_date] >= [_ThisRow].[Report_Date_Min],
TRUE),
IF(IsNotBlank([_ThisRow].[Report_Date_Max]),
[discharge_date] <= [_ThisRow].[Report_Date_Max],
TRUE)
),
OR(
AND(
OR(
[Enrollment_Date] >= [_ThisRow].[Report_Date_Min],
[discharge_date] >= [_ThisRow].[Report_Date_Min]
),
OR(
[Enrollment_Date] <= [_ThisRow].[Report_Date_Max],
[discharge_date] <= [_ThisRow].[Report_Date_Max]
)
),
AND(
[Enrollment_Date] <= [_ThisRow].[Report_Date_Max],
IsBlank([discharge_date])
)
)
),
IF(IsNotBlank([_ThisRow].[Report_Discharge_Reason_Filter]),
IN([status], [_ThisRow].[Report_Discharge_Reason_Filter]),
TRUE),
IF(IsNotBlank([_ThisRow].[special_status_filter]),
IN([Patient_Link].[Special_Status], [_ThisRow].[special_status_filter]),
TRUE),
IF(IsNotBlank([_ThisRow].[Report_Acute_Chronic_Filter]),
IN([Acute_or_chronic], [_ThisRow].[Report_Acute_Chronic_Filter]),
TRUE)
),
TRUE
)
I knowโฆ thatโs a bit crazy 
- To help break it down, hereโs a 7min long video explaining how it works.
Itโs all about getting the information the system needsโฆ into the systemโs hands, and figuring out the logic to accomplish what youโre looking for.
Youโll have to develop your own version, obviously column names and tables and things will be named differently; but yeah you should be able to devise a formula that takes one set of criteria and matches it against another.
Note:
- SELECT() in AppSheet is essentially the SELECT-function from the SQL world
- Only with AppSheet syntax, and you only get 1 column
- Itโs doing the same thing: itโs a query
- SELECT(table_name[column1], condition)
