APPSHEET EQUIVALENT to GOOGLE SHEET COUNTBLANK(FILTER

[Aadam]

Hi All, 

I am looking for formula/expression to check if there is any blank rows for [TARIFF CODE] in STATS TABLE, but am searching for this from within PATIENT TABLE. 

The problem is a SELECT search is only looking at first value found, and the empty value could be anywhere down the column. 

I found a solution by adding a column [TARIFF CHECKER] in the PATIENTS TABLE and using a spreadsheet formula as follows: =COUNTBLANK(FILTER(STATS!J:J,STATS!D:D=K16))>0

(K16 = UNIQUE ID ((KEY) PATIENT TABLE)

STATS!J:J = TARIFF COLUMN (STATS TABLE)

STATS!D:D = PATIENT ID (REFERENCE to PATIENT TABLE)

This works perfectly with a virtual column IF Statement and returning true or false; as follows

IF([TARIFF CHECKER]=TRUE,"TARIFF CODES","")

Is there an appsheet equivalent formula?

This is my sort of outline below, have played around with SELECT as well, but how to make it find any blank and not just the first or last row (if I used MAXROW), I cant use index either as I don't know where the blank is, its anywhere in the column for that PATIENT ID. 

IF(ISBLANK(ANY(FILTER("STATS",[_THISROW].[UNIQUE ID]=[PATIENT ID]))),"TARIFF CODES","")

[Suvrutt_Gurjar]

Could you mention what action you would like for any record found with  blank [TARIFF CODE] value in the STATS table? Are you just counting those records or filling up with some code by some logic?

When you mention you are searching it from the PATIENTS table, do you mean you have a column in the PATIENTS table that makes a list of [TARIFF CODE] in the realetd child records?

If you could elaborate a little  more on your requirement , the community could help you better.

[Aadam]

Hi @Suvrutt_Gurjar , thanks for feedback. 

The below screenshot is from the PATIENT table detail view for a patient, 

[Query - What is still required] is a list type column that I have to help the user to know what is still missing before they can submit for payment claim. The tariff is found in the Child table “Stats” so the connection between the tables exists. 

As you can see in the screen shot it is working as expected, but I’m using a spreadsheet formula to do the checking, and wondered if there is an equivalent  expression to use in Appsheet, I have tried a few expressions but not getting to it. 

 I don’t need a count of how many blanks there are, I just need to know if any blanks exist, so it’s a yes/no, if yes then the list required will have “tariff code” text and if  it’s no  then the list will not contain any mention of tariff. In the example above there is also a missing A-Sheet but this is checked in the patient row in the table itself, so thats was easy to  apply yes/no to. 

Hope this makes sense. 

[Suvrutt_Gurjar · Accepted solution]

Thank you for the details.

Please try an expression of

IF(

COUNT([Related STATSs])>COUNT(SELECT([Related STATSs][TARIFF CODE], ISNOTBLANK([TARIFF CODE]))),

"Tariff code/s",

""

)

[Aadam]

Hi @Suvrutt_Gurjar thank you so much this worked as expected. 

Can I ask you in general terms would a GOOGLE sheet expression which is used as spreadsheet formula, and assuming it's a consistent formula all the way down the column be better at keeping values up to date, vs using an appsheet formula (in my case I have already created  actions with bots to make sure that any actions will update the related rows in either table after ADDS/UPDATES/DELETES) In other words is the only way to ensure updates in both tables by using an action/bot? What is preference, I guess there could be risk of the spreadsheet formula getting changed etc. but what are the real draw backs as it seems like a simpler option with fewer process (forgive my ignorance in advance I am just trying to understand so I can implement) 

And the other question is in your formula you have  [Related STATSs][TARIFF CODE] when does one use the expression without the "fullstop" i.e [Related STATSs].[TARIFF CODE]

[Suvrutt_Gurjar]

---

@Aadam wrote:

Can I ask you in general terms would a GOOGLE sheet expression which is used as spreadsheet formula, and assuming it's a consistent formula all the way down the column be better at keeping values up to date, vs using an appsheet formula (in my case I have already created  actions with bots to make sure that any actions will update the related rows in either table after ADDS/UPDATES/DELETES) In other words is the only way to ensure updates in both tables by using an action/bot? What is preference, I guess there could be risk of the spreadsheet formula getting changed etc. but what are the real draw backs as it seems like a simpler option with fewer process (forgive my ignorance in advance I am just trying to understand so I can implement) 

---

A spreadsheet formula will not work when the app is offline.  Spreadsheet formulas as well as virtual columns containing multirow expressions such as SELECT() ) can also impact app sync times. 

Please take a look at the article below

Improve the speed of Sync - AppSheet Help

However there is no generalization or water tight rules on either side.  Since the option of spreadsheet formulas is given in AppSheet, one can certainly judiciously use them.

I believe if a physical column that contains multirow expressions and does not require too frequent updates, can be updated by bot /actions. 

---

@Aadam wrote:

And the other question is in your formula you have  [Related STATSs][TARIFF CODE] when does one use the expression without the "fullstop" i.e [Related STATSs].[TARIFF CODE]

---

Without full stop ( or dot notation) is used when referring columns from the child table to the parent table ( Also called dereferencing a list)

So  [Related STATSs][TARIFF CODE] dereferences a list of values from child table to the parent table. The dereferencing a list will produce a list of values.

With full stop ( dot notation) is used to refer columns from the parent table to the child table.

So in your case you could refer [Visitor_Number] from the parent table to a column in the child table by using an expression something like [Ref column name in the child table that references the PATIENTS table].[Visitor_Number]

Please read the article below

Dereference expressions - AppSheet Help

[Aadam]

Thank you for sharing your experience and insight, it Is really helpful.