filter option with enum list

[jaichith]

Hello buddies,

I am struck with an problem. I have created the filtered dashboard with pdf creation. filtered dashboard works on the slice with row condition below. 

and(
if(isnotblank(Filter DASHBOARD[COURIER NAME]),in([COURIER NAME],Filter DASHBOARD[COURIER NAME]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(Filter DASHBOARD[AGENCY NAME]),in([AGENCY NAME],Filter DASHBOARD[AGENCY NAME]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(Filter DASHBOARD[DISPATCH ASSIGN TO ]),in([DISPATCH ASSIGN TO ],Filter DASHBOARD[DISPATCH ASSIGN TO]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[ORDER START DATE])),[ORDER DATE]>=any(Filter DASHBOARD[ORDER START DATE]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[ORDER END DATE])),[ORDER DATE]<=any(Filter DASHBOARD[ORDER END DATE]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[DISPATCH START DATE])),[DATE OF DISPATCH]>=any(Filter DASHBOARD[DISPATCH START DATE]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[DISPATCH END DATE])),[DATE OF DISPATCH]<=any(Filter DASHBOARD[DISPATCH END DATE]),isnotblank([CONSIGNMENTS ID]))
)

and I have made it appear as enum list by using valid if condition 

SELECT(CONSIGNMENTS[COURIER NAME],TRUE). 

these expressions is not listing (empty ) cells option as shown in the screenshot. but appsheet filter which given by default has this empty cells option. how to make this possible? please help me with expressions with need syntax. 

[SmartsheetGuru]

@jaichith I've encountered something similar with needing to list "all" selections in an ENUM.  You can add the string "empty" to the list and then look for that in your filter.  Remember you can add lists together. List1 + List2 or in your case:

 SELECT(CONSIGNMENTS[COURIER NAME],TRUE). + list("Empty")

and that should add "empty" to your ENUM selections.  

You can then incorporate looking for "empty" into your filter logic.

[jaichith]

SELECT(CONSIGNMENTS[COURIER NAME],TRUE). + list("Empty") this expression has been included but i dont know the filter logic expression. kindly text here the expression

[Aurelien]

Hi 

---

@jaichith wrote:

and I have made it appear as enum list by using valid if condition 

SELECT(CONSIGNMENTS[COURIER NAME],TRUE). 

---

@jaichith 

Can you try with this expression in Suggested value rather than Valid_If ?

[jaichith]

where is suggested value sir?

[Aurelien]

Hi @jaichith 

Here:

[jaichith]

SIR, I HAVE TRIED IT IS NOT WORKING

[Aurelien]

can you try this:

CONSIGNMENTS[COURIER NAME] + LIST("Empty")

[jaichith]

bro, by using this SELECT(CONSIGNMENTS[COURIER NAME],TRUE). + list("Empty") 

 it search searches for the string "empty" in rows. but it is not finding the blank cells

[Aurelien]

OK, I misunderstood your request.

I would try to use a "mirror" column that would set a "empty" value that would be selectable. This way:

- add a virtual column in your table CONSIGNMENTS, same type than COURIER NAME which would have this name: "_COURIERNAME", and this expression:

IF(ISNOTBLANK([COURIER NAME]),
  [COURIER NAME],
  "[Empty]"
)

Then, use this expression in the Valid_If field of the COURIER NAME column in your filter dashboard:

CONSIGNMENTS[_COURIERNAME] + list("[Empty]") 

[jaichith]

I have doubt

CONSIGNMENTS[_COURIERNAME] + list("[Empty]")

Why you are adding the [ ] to Empty ? what does it mean?

[AleksiAlkio]

To be sure what your challenge is..
#1 - Is the Filter Fashboard table the one, where you select all your 7 filtering criterias?
#2 - Do you have one or multiple rows in that table?
#3 - If multiple rows, does every app user has their own rows?
#4 - Does everything work properly when selecting for example from "Courier name" column?
#5 - Is the only problem.. you should be able to select "blank" value as well when selecting the "Courier name" etc?

Before trying to solve the challenge, we first need to be 100% sure what the structure is and what the goal is.

[jaichith]

@AleksiAlkio Dear friend,

I have created row filter condition with multiple filter choice. It works fine. But I couldn't include blank values as one of the choice since select () (SELECT(CONSIGNMENTS[COURIER NAME],TRUE) returned unique values (did not returned blank values as mentioned). Now I want the blank values also as choice. This helps me to take filtered  reports. 

Where I Am :

 

What I expect : 

 

Last option (" Empty") I have arrived with the help of community member (SELECT(CONSIGNMENTS[COURIER NAME],TRUE). + list("Empty") ). But he has asked me to change the row filter expression according to what I want. Here I got stuck.  I want alter the Old Expression so that it includes blank values also if it is given as one of the choice.

and(
if(isnotblank(Filter DASHBOARD[COURIER NAME]),in([COURIER NAME],Filter DASHBOARD[COURIER NAME]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(Filter DASHBOARD[AGENCY NAME]),in([AGENCY NAME],Filter DASHBOARD[AGENCY NAME]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(Filter DASHBOARD[DISPATCH ASSIGN TO ]),in([DISPATCH ASSIGN TO ],Filter DASHBOARD[DISPATCH ASSIGN TO]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[ORDER START DATE])),[ORDER DATE]>=any(Filter DASHBOARD[ORDER START DATE]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[ORDER END DATE])),[ORDER DATE]<=any(Filter DASHBOARD[ORDER END DATE]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[DISPATCH START DATE])),[DATE OF DISPATCH]>=any(Filter DASHBOARD[DISPATCH START DATE]),isnotblank([CONSIGNMENTS ID])),

if(isnotblank(any(Filter DASHBOARD[DISPATCH END DATE])),[DATE OF DISPATCH]<=any(Filter DASHBOARD[DISPATCH END DATE]),isnotblank([CONSIGNMENTS ID]))
)

[AleksiAlkio]

I modified my earlier post with bullet points. Would you please check them and answer based on those questions.

[jaichith]

Point 5 is the only problem I face

[AleksiAlkio]

But please answer them. It's clear for you, but not for others. Yes or no is enough. Maybe there is a better way to do them as well, or maybe the approach is totally wrong. That's why we always need to know them.

[jaichith]

1.  Yes 

2. It has multiple rows

3. No

4.yes

5. Yes. That is the problem I want to include blank values when it is given as one of the choice. 

[AleksiAlkio]

So.. the app user needs to add a new row to "Filter Dashboard" for the PDF generation. This PDF is then filtered with user's selections, right?

[jaichith]

No.... PDF is last output. 

I am finding difficulty in expressions. Which picks empty cells too if user specified.

 

[AleksiAlkio · Accepted solution]

It's a little bit weird that the slice works properly as if you have multiple rows in your "Filter Dashboard" slice and your formula is like..
if(isnotblank(Filter DASHBOARD[COURIER NAME]),in([COURIER NAME],Filter DASHBOARD[COURIER NAME]),isnotblank([CONSIGNMENTS ID])),..
For example in the first evaluation you are checking if that slice has zero rows or not. Then again the same with the 2nd evaluation where you check it throught the whole slice. Maybe you are filtering the slice with something else later, don't know.

But in generally, you should add the "empty" possibility to all of your statements. For example to this..
IF(
ISNOTBLANK(Filter Dashboard[Courier Name]),
IN([Courier Name],Filter Dashboard[Courier Name]),
ISNOTBLANK([Consignment ID])
),
it should be..

IF(
ISNOTBLANK(Filter Dashboard[Courier Name]),
OR(
IFS(IN("Empty",Filter Dashboard[Courier Name]),ISBLANK([Courier Name]))),
IN([Courier Name],Filter Dashboard[Courier Name])
),
ISNOTBLANK([Consignment ID])
)

[jaichith]

Dear,  please check the parentheses. I think some where closed  brackets ")" are extra . 

[AleksiAlkio]

If there is, just remove it 😉

[jaichith]

TONS OF THANKS FOR SOLVING THIS PROBLEM.

[AleksiAlkio]

You're welcome!