Expansive IFS formula

eric · March 6, 2024, 3:16pm

Mostly written by the great Appster of course.

IFS(
    AND(
        OR(
            CONTAINS([Unit Type], "5000"),
            CONTAINS([Unit Type], "W5"),
            CONTAINS([Unit Type], "7000"),
            CONTAINS([Unit Type], "W7"),
            CONTAINS([Unit Type], "9000"),
            CONTAINS([Unit Type], "W9"),
            CONTAINS([Unit Type], "11000"),
            CONTAINS([Unit Type], "15000"),
            CONTAINS([Unit Type], "W15"),
            CONTAINS([Unit Type], "I15"),
            CONTAINS([Unit Type], "16000"),
            CONTAINS([Unit Type], "W16"),
            CONTAINS([Unit Type], "I16")
        ),
        ISNOTBLANK([UF])
    ),
    IF(
        AND(
            EOMONTH(
                DATE(
                    CONCATENATE([Install Year], "-", [Install Month], "-01")
                ),
                0
            ) <= EOMONTH(TODAY(), -36),
            CONTAINS([UF], "UF"),
            NOT(CONTAINS([Unit Type], "ICE")),
            NOT(
                IN(
                    "5 Filters",
                    SELECT(
                       Running Filter Change List[Filters Changed],
                        AND(
                            [Serial #] = [_THISROW].[Serial #],
                            [Date] > EOMONTH(TODAY(), -36)
                        )
                    )
                )
            )
        ),
        "ALL 5",
        "Base 3"
    ),
    AND(
        OR(
            CONTAINS([Unit Type], "5000"),
            CONTAINS([Unit Type], "W5"),
            CONTAINS([Unit Type], "7000"),
            CONTAINS([Unit Type], "W7"),
            CONTAINS([Unit Type], "9000"),
            CONTAINS([Unit Type], "W9"),
            CONTAINS([Unit Type], "11000"),
            CONTAINS([Unit Type], "15000"),
            CONTAINS([Unit Type], "W15"),
            CONTAINS([Unit Type], "I15"),
            CONTAINS([Unit Type], "16000"),
            CONTAINS([Unit Type], "W16"),
            CONTAINS([Unit Type], "I16")
        ),
        ISBLANK([UF])
    ),
    IF(
        OR(
            [High TDS Alert],
            AND(
                EOMONTH(
                    DATE(
                        CONCATENATE([Install Year], "-", [Install Month], "-01")
                    ),
                    0
                ) <= EOMONTH(TODAY(), -60),
                EOMONTH(TODAY(), -60) >= MAX(
                    SELECT(
                        Running Filter Change List[Date],
                        AND(
                            CONTAINS([Filters Changed], "5 Filters"),
                            [Serial #] = [_THISROW].[Serial #]
                        )
                    )
                )
            )
        ),
        "ALL 5",
        "Base 3"
    ),
    CONTAINS([Unit Type], "ICE"),
    "Ice Filter",
    OR(
        CONTAINS([Unit Type], "I12"),
        CONTAINS([Unit Type], "W12")
    ),
    IF(
        OR(
            [High TDS Alert],
            EOMONTH(TODAY(), -60) >= MAX(
                SELECT(
                    Running Filter Change List[Date],
                    AND(
                        CONTAINS([Filters Changed], "5 Filters"),
                        [Serial #] = [_THISROW].[Serial #]
                    )
                )
            )
        ),
        "ALL 5",
        "Base 2"
    ),
    AND(
        CONTAINS([Unit Type], "FQ"),
        ISBLANK([UF])
    ),
    IF(
        OR(
            [High TDS Alert],
            EOMONTH(TODAY(), -36) >= MAX(
                SELECT(
                    Running Filter Change List[Date],
                    AND(
                        CONTAINS([Filters Changed], "4 Filters"),
                        [Serial #] = [_THISROW].[Serial #]
                    )
                )
            )
        ),
        "ALL",
        "Base 3"
    ),
    AND(
        CONTAINS([Unit Type], "FQ"),
        ISNOTBLANK([UF])
    ),
    IF(
        EOMONTH(TODAY(), -24) >= MAX(
            SELECT(
                Running Filter Change List[Date],
                AND(
                    CONTAINS([Filters Changed], "4 Filters"),
                    [Serial #] = [_THISROW].[Serial #]
                )
            )
        ),
        "ALL",
        "Base 3"
    )
)

MultiTech_Visions · March 6, 2024, 7:03pm

Hey hey! Thanks for posting in the community!

Hopefully others can glean some help from the discussion, or even chime in if they’ve got good advice!

Ugh…

Appster still falls back to that dang SELECT() statement.
- I’ll tell you what… it’s proving exceedingly difficult to get that out of it’s mind. (I’ll figure it out I’m sure.)

A lot to unpack here…

for things like this:

AND(
OR(
CONTAINS([Unit Type], “5000”),
CONTAINS([Unit Type], “W5”),
CONTAINS([Unit Type], “7000”),
CONTAINS([Unit Type], “W7”),
CONTAINS([Unit Type], “9000”),
CONTAINS([Unit Type], “W9”),
CONTAINS([Unit Type], “11000”),
CONTAINS([Unit Type], “15000”),
CONTAINS([Unit Type], “W15”),
CONTAINS([Unit Type], “I15”),
CONTAINS([Unit Type], “16000”),
CONTAINS([Unit Type], “W16”),
CONTAINS([Unit Type], “I16”)
),
ISNOTBLANK([UF])
),

I might try to take this portion and create a slice with this criteria.
- This will then remove the necessity for you to make this computation here (in your formula), and instead only need to bring in the slice.
  - IN([Row_ID], SLICE[Row_ID])
  - IN(Row_Parent_Ref], SLICE[Row_ID])
  - something like that

You might alternatively be served by adding in some sort of Type table, which you could ref connect your records to.

This would give you a [Related Whatevers] on this Type table, which can often be used in clever ways.
- But this is a deep topic, which will require extensive reworking of your data schema.

You use this portion over and over, I’m wondering if there’s a way to use slices and reference to get this record ID in a [Related whatever] list somehow?

eric · March 6, 2024, 8:54pm

Yea I can’t quite figure out how to morph a slice into my data schema to lower this computational load. And yes I thought about trying to force it away from select, but it doesnt seem to be taking to long to sync at this stage.

MultiTech_Visions · March 6, 2024, 9:22pm

Where are you using this formula?

Formatting rule?
Security filter?

What is the purpose?

MultiTech_Visions · March 6, 2024, 9:23pm

Also… what is this?

Is Running Filter Change List a single record?
- Like something held in an “Active” or “Building” slice?

eric · March 6, 2024, 10:24pm

I am using this formula to fill in a column to tell my techs what filters to change on the next filter change.

Yup so here is a little breakdown.
Tables
Serial List - contains serial numbers and company names, as well as other pertinent info.
Company List - Contains a list of companies and what types of machines they have
For example, we install one machine at a company in January and another different type in June, it would be on this list twice.
App List - List of all companies with the company name as the key.
Filter Change PDF - Table where I load in what filter changes need to be done and the techs complete a form when they do the filter change, there is room for up to 20 serial numbers on this list so thats where “Running Filter Change List” comes in.

Running Filter Change List - Anytime a filter change is completed it puts the serial number and a reading we get off the machine on this list, that way I can Ref individual filter changes back on my serial list.

eric · March 6, 2024, 10:37pm

Read a single table	00:00:02.5877911	{TableName:Serial List,Timestamp:10.35.01.527426}
Compute virtual columns	00:00:01.5619667	{SchemaName:Serial List_Schema}

eric · March 6, 2024, 10:44pm

I just had a thought, what if I changed this to a real column, then had a bot daily calculate and change the column every day. My other option is to move this column somehow to my filter change table and then have it only calculating on those rows. When only looking at <> Done rows it would be way less rows.

MultiTech_Visions · March 7, 2024, 12:36am

Maybe what’s missing is a connection between the records and which you record the filter change activity and the unit in which it belongs to?

if they are reference connected, you should have a [Related Filter_Changes]
from this you could extract out the last filter out of the list, and you could do some work with that.
- Index([Related Filter_Changes], COUNT([Related Filter_Changes]))

Once you have the last child record like that, then you can begin to dereference information out of it and bring that into the unit layer.

you could extract out the date, and use this to help determine when something needs to be changed again.

Well I might actually do is create a field on the filter change record, that actually does the math of the next change date then and there when you make the record… This way when we need to know when the next due date for a filter change is, all we have to do is look at the column in the last filter change record.

and with that last filter change record held on the unit, we can easily de-reference out that next filter change date… And from that I can easily create a slice of all of the things that are approaching that date.

It’s all about creating these layers.

Do you think something like that might work?