error: Unable to find column "Follow Up status"

[Aditya_Vinayak_]

ISNOTBLANK(FILTER("Follow Up Test",AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN([Follow Up Status] = "PENDING",[Related Call Logs][Follow Up Status]))))

I am using the above formula in a format rule on a slice named by Follow Up Test.

It is showing me an error that Unable to find column "Follow Up status"

Can someone help?

[Suvrutt_Gurjar · Accepted solution]

This?

ISNOTBLANK(FILTER("Follow Up Test",AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status]))))

[Suvrutt_Gurjar · Accepted solution]

This?

ISNOTBLANK(FILTER("Follow Up Test",AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status]))))

[Aditya_Vinayak_]

Yes, actually i figured  it out but it still doesnt solve my problem idk why..

[Suvrutt_Gurjar]

Sorry, I missed one point in your first post that you are using the expression in format rules. Please do not use such heavy multirow expressions in format rules. Typically format rules should be with simpler expressions such as [Status]="Open" etc. Multirow format rules could slow down your app.

Please take a look at the below warning in the article on format rules.

Format Rules: The Essentials - AppSheet Help

Also possibly your expression could be simpler

AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status])) 

or something like 

NOT(AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status])))

depending on what status you are looking for. But even this expression could typically be avoided for format rules.

[Aditya_Vinayak_]

Okay, I understand.

My basic problem which I am trying to solve is in the below link.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Follow-Up-Status-of-current-day-on-the-previous...

Cn you think of any other way besides format rules to solve it?

[Suvrutt_Gurjar]

Could you try with the expression 

NOT(AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status]))) for the format rule and check if it is giving warning of an expensive expression such as "this expression could impact sync time?". If not please try that and update the community, if otherwise.

Typically it should not be a sync expensive expression.

[Aditya_Vinayak_]

Hi Suvratt,

Yes, the formula is not sync expensive. But the result is kinda wrong. Below I have shared a picture of my follow up screen there is a customer named Hema, whose call log history picture also I have shared. If you see the follow up date is 8th & status is pending but it still doesnt show the icon like it is showing for Arul.

I have shared Arul's call log also, for which it is showing the icon. Somewhere there is a mismatch in understanding.

[Suvrutt_Gurjar]

Please share where you are using the expression, I mean on which column on which table.

The two related call logs screen shots - are they for different parent records?

Also please state in plain language your desired goal. Meaning do you want the format rule when the status is pending or when the status is other than pending for today.

[Aditya_Vinayak_]

So I want to use this format rules feature on my follow up slice which is a slice from the call log table.

Currently when i try to create a format rule it only gives me options for the main tables. So i was doing the format rule on customer database table, on customer name column which i realized maybe wrong, i should have done it on call log table. What do you think?

Yes both the call logs are of different parent records. One belongs to Hema & one to Arul.

Goal: In the follow up slice (which shows all follow up for today) i have created from the call log table, i want clear visibility in terms whether the follow up is done or not for today through format rules.

So if the follow up date is today & the status is pending it should show a red icon, if the status is completed it should show green icon only on the follow up slice view & not in any other view.

i hope this helps... 

[Suvrutt_Gurjar]

Thanks for more details.

I believe customer name in the parent table will do to attach both the format rules.

Please try creating two format rules for the parent table column.

1. When there are no pending follow ups today

NOT(AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status])))

Please assign a green colour and some tick mark like icon for this format rule.

2. When there are pending follow ups for today

AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),IN( "PENDING",[Related Call Logs][Follow Up Status]))

Please assign a red colour and some cross mark like icon for this format rule.

I tested in a similar environment with two format rules as above. My testing for open orders with delivery date as today and no open orders for delivery date other than today or today.

1. Card view of Customers: Bob barker should have open orders for today.

2. For customer Ann Adams , there is one order with delivery date is 08/02/2023. However Order status is complete , so format rule applies green colour against the name Ann Adams.

3.  For customer Bob Barker , there is one order with delivery date is 08/02/2023. However Order status is open , so format rule applies red colour against the name Bob Barker.

[Aditya_Vinayak_]

So in case a follow up status is not updated, if its blank then in that case it is still showing green.

How can I just tell the system to not do anything in that case.

Also can this Icon and formatting be visible only in the follow up view and not generally in other views?

[Aditya_Vinayak_]

I used the below formula:

NOT(AND(IN(TODAY(),[Related Call Logs][Follow Ups V]),OR(IN( "Pending",[Related Call Logs][Follow Up Status]),IN( "Not Required",[Related Call Logs][Follow Up Status]),IN(ISBLANK([Related Call Logs][Follow Up Status]),[Related Call Logs][Follow Up Status]))))

But for some reason its not working from blanks.

Also, if I have multiple follow ups for the same person on the same day also then also it doesn't work accurately.

For Ex: if 2 follow ups have to be done for the same customer & if one is completed. It  will show the completed icon to the customer.

@Suvrutt_Gurjar 

[Suvrutt_Gurjar]

Originally discussion and requirement started with only "Pending" status and so the expression is around "Pending" Status. However your latest requirement shows that you also wish to incorporate other statuses such as "Not Required", "Blank" etc.

Please share combinations of Statuses for which you would like green, combination of statuses for which you would like it to be red and combination of statuses where it should be (probably) blank.

[Aditya_Vinayak_]

Apologies, i thought if i could get a start i should be able to manage the rest.

So basically there are 4 status, for Follow Up Status:

Completed: Green
Pending: Red
Not Required: Orange
Blank: Blue

[Suvrutt_Gurjar]

Thank you. However please share more details on certain combinations being there. For example if there are three Call Log records for a customer on the same day - one with Pending , another with Completed and yet another with "Not required". Or one with "Blank" and another with "Pending".  In the screenshots you shared, there are "Pending" and blank statuses on the same day.

What should be final status shown in the parent table's card view for such combinations? Or would you like three different format rules to be simultaneously shown.

[Aditya_Vinayak_]

So my final goal is that in the follow up today slice view, I want to know just by looking at the view outside itself if for today the follow up is completed or not.

So if there are 2 follow ups today, one is at 3 pm for which status is completed & another follow up for the same customer is created for 6 pm, then on the view it should still be pending, until its completed.

If in any case there is a not required follow up status, then that should be considered first.

So basically the latest update for the same day should be considered by the system, preferred by the system.

Does this help?

[Suvrutt_Gurjar]

---

@Aditya_Vinayak_ wrote:

So basically the latest update for the same day should be considered by the system, preferred by the system

---

---

---

@Aditya_Vinayak_ wrote:

So basically there are 4 status, for Follow Up Status:

Completed: Green
Pending: Red
Not Required: Orange
Blank: Blue

---

---

Thank you. It sounds that you may still have some requirements that have not come out in the description so far. But you could try the below  suggested approach based on the gist of your latest two posts, mentioned above.

Please create a virtual column in the Customers table called say [Latest Status] ( Ref type -it should auto create as a reference type when you enter expression) with an expression something like 

INDEX(ORDERBY([Related Call Logs], [Follow Up Date], TRUE), 1)

Then create another virtual column called say [Latest Status] in the Customers table with an expression something like 

[Latest Status].[Follow up Status]

Thereafter the format rules expressions can be

For Green:

AND( CONTEXT("View") = "Follow up View Name", [Latest Status] = "Completed" )

For Orange:

AND( CONTEXT("View") = "Follow up View Name", [Latest Status] = "Not Required")

For Red :

AND( CONTEXT("View") = "Follow up View Name", [Latest Status] = "Pending" )

For Blue:

AND( CONTEXT("View") = "Follow up View Name", ISBLANK([Latest Status]) )

Please replace "Follow up View Name" name with the actual view name you have for the view containing today's follow ups for the Customer table.

[Aditya_Vinayak_]

I am sorry Suvratt, maybe I am not able communicate my requirement properly. The above mentioned formula works well, the only problem is that it is updating the formatting based on the latest follow up, but not on the follow up done today.

For Ex: if i have a follow up scheduled for today & the status for it is completed. I will be adding a new call log for the same customer to follow up with him again on a future date, and for that the status is pending.

So rather than showing me whether the follow up for today is done or not, it shows the formatting based on the latest follow up date.

[Suvrutt_Gurjar]

Okay. Does adding the following to the expressions help?

AND( CONTEXT("View") = "Follow up View Name", [Latest Status] = "Completed", IN(TODAY(),[Related Call Logs][Follow Ups V] ) )

Where [Follow Ups V] is with expression DATE( [Follow Up Date])

[Aditya_Vinayak_]

No Suvratt, its not giving the right result 😞

[Suvrutt_Gurjar]

Could you elaborate, what issue it is giving? Also please share the expression you are using and where.

[Aditya_Vinayak_]

So, its not giving any error. It is taking the formula. But the result that is coming is not right.

Formula Used: AND( CONTEXT("View") ="Follow Up (Today)", [Latest Status 1] = "Completed", IN(TODAY(),[Related Call Logs][Follow Ups V] ) )

I have used it in the format rule for completed status.

If you see the below call log, Its for Madan. Follow Up date is Today & Status is pending but it is showing the green tick.

[Aditya_Vinayak_]

@Suvrutt_Gurjar  dud the above info help?

[Suvrutt_Gurjar · Accepted solution]

Yes, I think the latest screenshots have helped more, even though I believe there will still be some hidden requirements.

Please change the  expression for the [Latest Status]  ( Ref-type column) to the below expression:

INDEX(ORDERBY(SELECT([Related Call Logs][Key Column of Call Logs Table], DATE([Follow Up Date])=TODAY()), [Follow Up Date], TRUE), 1)

Then create another virtual column called say [Latest Status 1] in the Customers table with an expression something like 

[Latest Status ].[Follow up Status]

Thereafter the format rules expressions can be

For Green:

AND( CONTEXT("View") = "Follow up View Name", [Latest Status 1] = "Completed" )

Please repeat the expression in similar fashion for other statuses as discussed earlier,

[Aditya_Vinayak_]

Thank you soo much @Suvrutt_Gurjar . I think we have solved it 🙂

[Suvrutt_Gurjar]

Good to know. Thank you for the update. Please test well.

[Aditya_Vinayak_]

The issue is it is comparing lists. If in that whole list of checking the follow up status & todays date if there are 2 conditions..format rules gets confused on what to appoint.

Its not specifically checking a data in the row with respect to the today date. Its just comparing 2 lists.

[Aditya_Vinayak_]

@Steve can you help?