Creating a slice that excludes all records if a summarized condition is true

AppSheet AppSheet Q&A
1

I have a table of data that includes a row for each time a person (Name) serves in a position (PosID) and they may serve in a position multiple times. These positions have levels which are defined in a referenced table ([PosID].[Level]).

I would like to filter out all records for an individual (Name) if they have already served in a specific level (“5”), yet leave records for other individuals, but only if they have served at least once in specific levels (“3” AND “4” at least once).

I believe I need to create a slice with an expression to filter in/out the records, but am having trouble creating the expression.

I have tried this expression, but it includes records for level 3, but I only want them to show if at least one record exists for levels “3” AND “4”, but not level “5”.

OR(CONTAINS([PosID].[Level],“3”), CONTAINS([PosID].[Level],“4”))

2

I think a clearer explanation is required of your exact requirement, but based on the understanding ( which may be incorrect and so does the solution suggested below) so far, you may want to try an expression of
AND(
OR(CONTAINS([PosID].[Level],“3”), CONTAINS([PosID].[Level],“4”)),

NOT(CONTAINS([PosID].[Level],“5”))
)

If the above does not work, we may need more clarifications about the requirement.

1 Like
3

Thank you, but that expression only removes the level “5” record.

Instead the expression should count all occurrences of level 5 records for that individual and if at least one exists, leave out all records (1,2,3,4 & 5) for that individual. And if there is no level 5 record for that individual, I would like to see level 3 and 4 records, but only if there are BOTH level 3 and level 4 records for that individual in the dataset.

4

Could you mention what is the type of column [Level]? is it an enumlist?

Also , if possible, please post the screenshot of the relevant columns of the table(s) with their types.

1 Like
5

I would recommend splitting the logic up to help simplify the expressions and take advantage of lists made available. This does assume you have used REF columns to tie your parent/child tables together. For example, your Positions Served table has a Ref column to the Person table and has automatically created a column in the Person table similar to Related Positions Served

I would add to your Person table a Yes/No column maybe named “Served Level 5?”. Assuming you have a Related column with the rows of Positions served, the expression for this column would be

COUNT([Related Positions Served][ID], [Position Level] = “5”) > 0

I would add a second column named “Served 3 and 4?”, with an expression like:

AND(COUNT([Related Positions Served][ID], [Position Level] = “3”) > 0,

COUNT([Related Positions Served][ID], [Position Level] = “4”) > 0)

NOTE: Adjust [ID] part to the row key of your Position Served table. Also, adjust the data type for the levels.

Then in your Slice, the criteria could be simplified to something like:

AND(IN([Position Level], {“3”, “4”}),
NOT([Person].[Served Level 5?]), [Person].[Served 3 and 4])

I hope this helps!!

1 Like
6

I have a Y/N field that indicates if there is a “5” level

I am having trouble with the syntax to create this expression. This is what I have:

AND(COUNT(Position[PosID].Position[Level] = “3-CR-IN TALK” > 0),
COUNT(Position[PosID].Position[Level] = “4-CR-OUT” > 0))

but I receive an Arithmetic expression error. Can you assist with the correct syntax?

7

Sorry I made some assumptions that I probably should not have.

Based on the comment just above, one of core assumptions I made is that you have a Persons table of some sort and that it is referenced on your main data table (which I will call the “Positions Served Table”). It would be simpler and much more efficient if you did.

I strongly recommend inserting a Persons table if you don’t already have one

Otherwise, the full expression to place in the Slice criteria is below.

*****Full Slice Criteria Expression

WARNING: Using the expression below is very inefficient. It relies on several SELECT expressions that perform full table scans. These will get slower over time as the data table grows in size

AND(
IN([PosID].[Level], {“3-CR-IN TALK”, “4-CR-OUT”}),

COUNT(SELECT(Positions Served Table[Row ID],
AND([Name] = [_THISROW].[Name],
[PosID].[Level] = “5-*…” ))) = 0,

COUNT(SELECT(Positions Served Table[Row ID],
AND([Name] = [_THISROW].[Name],
[PosID].[Level] = “3-CR-IN TALK” ))) > 0,

COUNT(SELECT(Positions Served Table[Row ID],
AND([Name] = [_THISROW].[Name],
[PosID].[Level] = “4-CR-OUT”))) > 0

)

NOTE: Adjustments needed:

Replace “Positions Served Table” with the name of your data table.
Replace [Row ID] withe column that holds the key for your data table.
Insert the correct text for Level 5.

I hope this helps more!!

1 Like
8

Hi @WillowMobileSys ,
I also had similar queries about the data schema and column types @Larry_Bryant 's app has. That is why I had requested him to give the following inputs before suggesting the possible approach.

Later, I saw that you have pitched in and guiding @Larry_Bryant. I am sure you will continue to ably guide him.

1 Like
9

Thanks very much for that suggestion, but I agree that as the database grows in size I would prefer to not have the poor performance of that suggestion.

Answering questions about the data and structure, I have 4 tables that are used in this section of the app: MEMBERS (in 2 parts below), POSITION, SERVICE, and WEEKENDS.

MEMBERS (part 1):

image
image1523×822 66.6 KB

MEMBERS (part 2):

image
image1523×821 80.6 KB

POSITION:

image
image1544×562 42.4 KB

continued….

10

SERVICE:

image
image1534×793 67.7 KB

WEEKENDS:

image
image1518×823 72.2 KB

I have added a Y/N field to the MEMBER table to begin adding the automation inside the table. It is called “Lay Director” but uses a different method for determining if Position Level 5 is true.

Both of your assistance is greatly appreciated. I am weak with writing this complex (for me) query, but I am very grateful for both your assistance!

11

Hi @Larry_Bryant ,

Thank you for the relevant details. Since @WillowMobileSys has started guiding you, I believe it will be best for you to persist with his suggestions in this somewhat extensive requirement.

Typically, many requirements can have more than one solutions. As long as one is not significantly better than the other, I believe it is better to follow one suggestion path. I will definitely join, if I am able to suggest a significantly better solution.

1 Like
12

Ok, great! You have a Members table nad have already added a column for identifying if a Member has performed Level 5 service.

It appears the Service table is one where each service performed is recorded.

Now, add a second Yes/No Virtual Column to the Members table to detect a person has performed both Level 3 and Level 4 services. I will call it “Served 3 and 4”. It’s expression will now look like this (I think before I forgot the SELECT part):

AND(
SELECT([Related Services][SvcID], [PosID].[Level] = “3-CR-IN TALK”),
SELECT([Related Services][SvcID], [PosID].[Level] = “4-CR-OUT”)
)

With this second Virtual Column created, you should be able to insert the Slice Criteria very similar to what I first suggested:

AND(
IN([PosID].[Level], {“3-CR-IN TALK”, “4-CR-OUT”*}),
NOT([MemID].[Lay Director]),
[MemID].[Served 3 and 4]
)
*
Let me know if you have questions!

I hope this finally gets you to what you want!

13

This?

AND(
  ISBLANK(
    {"3-CR-IN TALK", "4-CR-OUT"}
    - [Related Services][Position Level]
  ),
  NOT(IN("5-...", [Related Services][Position Level]))
)

Replace 5-... with whatever the level-5 identifier is.

2 Likes
14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.