Problem with query data range if a value is TRUE

Question

https://docs.google.com/spreadsheets/d/1XFbSKOgwFlsRahaOOCq1-EZPR4JcmaT2ywUc2RQffnk/edit?usp=drivesdk

I use this sheet to list data of patients in my hospital. Also in the last tab (Possible_Discharge), I query all the patients that their value of column I is TRUE in the tab (All_Patients) using this query function: =query(All_Patients!B5:J,"select B,C,D,E,F,G,H where B is not null AND I =TRUE order by B",0)

I also embedded code to clear contents of the sheet whenever I want to fill new data.

The problem I face is every time I fill new data after clearing the contents, The function of query (TRUE) results doesn't work. So, every time I change the TRUE to 'TRUE' or "TRUE" or just TRUE without quotation marks. I wonder what is wrong with this ? Is there a way to rewrite the function?

NOTE:

• Data_Entry tab is where I fill the data of patients.
• All_Patients tab is generated by query function (view only) from Data_Entry tab.
• All_Patients tab Column I represents the column H in Data_Entry tab when the checkbox value is TRUE.

Answer 1

You are using the RangeList.clearContent() method to remove data. This makes the value in the checkbox cells null instead of false or true.

The query() function only accepts one data type per column. The type of a column is determined by the majority of the values in the beginning of the column. When you clear the data, the data type of column Data_Entry!H:H is set to null, which means that the values that are true will not match the column type and will be considered null by query().

There are two possible solutions:

1. Modify the ClearContents() function that clears the Data_Entry tab so that the checkboxes are set to false instead of setting them to null.

2. Replace your query() functions with filter(), like this:

All_Patients!B5:

=iferror( 
  sort( 
    filter( 
      { 
        Data_Entry!B5:B, Data_Entry!O5:O, Data_Entry!D5:E, Data_Entry!L5:L, Data_Entry!J5:K, Data_Entry!H5:H
      }, 
      Data_Entry!B5:B <> "Ward", 
      not(isblank(Data_Entry!F5:F)) + not(isblank(Data_Entry!G5:G)) 
    ) 
  ) 
)

Possible_Discharge!D7:

=iferror( 
  sort( 
    filter( 
      All_Patients!B5:H, 
      not(isblank(All_Patients!B5:B)), 
      All_Patients!I5:I = true 
    ) 
  ) 
)