Re: Expression only using first value in LIST

Phil_Waite · 04-05-2022 08:11 AM

Hi,

I have an expression that returns a list based on two user defines values. They input them into [First Call List to Combine] and [Second Call list to Copmbine].

The expression is below and it works but it is long and messy. I alos want to be able to combine more than two criteria without extending this expresion each time.

I am looking to combine them by making [First Call list to Combine] an enumlist. Please see second expression for this further down.

OR(AND([Assign Outlet to User] = USEREMAIL(),[Temporary Prospect Next Call]>TODAY()-200),

(AND(IN( [Spare], SELECT( User Assign Details 2[District], [User id] = LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" ) ) ),

IN( [Outlet Rating], SELECT( User Assign Details 2[Outlet Rating], [User id] =LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" ))),

IN( [Price List], SELECT( User Assign Details 2[Price Band], [User Id] =LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" ))),

[Temporary Prospect Next Call]>TODAY()-200,

OR( AND([Assign Outlet to User] = USEREMAIL(),[Temporary Prospect Next Call]>TODAY()-200),
[Assign Outlet to User]= LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "User Id Viewer" )

,[Assign Outlet to User]="",[Assign Outlet to User]="NONE")),

AND([Assign Outlet to User]= LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "User Id Viewer" ),

[Temporary Prospect Next Call]>TODAY()-200)))

Revised expression below that uses IN() rather than "=" but it only results in the rows that match the first value in the [First Call List to Combine] rather than all values in the list.

OR(AND([Assign Outlet to User] = USEREMAIL(),[Temporary Prospect Next Call]>TODAY()-200),

(AND(IN( [Spare], SELECT( User Assign Details 2[District], IN([User id] ,LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" )) ) ),

IN( [Outlet Rating], SELECT( User Assign Details 2[Outlet Rating], IN([User id],LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" )))),

IN( [Price List], SELECT( User Assign Details 2[Price Band], IN([User Id],LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" )))),

[Temporary Prospect Next Call]>TODAY()-200,

OR( AND([Assign Outlet to User] = USEREMAIL(),[Temporary Prospect Next Call]>TODAY()-200),
[Assign Outlet to User]= LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "User Id Viewer" )

,[Assign Outlet to User]="",[Assign Outlet to User]="NONE")),

AND([Assign Outlet to User]= LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "User Id Viewer" ),

[Temporary Prospect Next Call]>TODAY()-200)))

Thanks

Phil

SkrOYC

Idk what you want help with

Phil_Waite

Hi,

The second expression only results in rows that match the first value in the enumlist. The first expression is too long - especially if I want to have 10 values in the list. I have clarified below.

Thanks

Phil_Waite

To clarify

The expression aims to combine two other users pre chosen combination of criteria.

Below shows USER 1 wanting to show all rows that match the second USERS criteria and the third users criteria.

At the moment the sec0nd expression in the above post ony shows USER2 rows which is the first in the ENUMLIST.

User Asign Alpha table below

	First Call List To Combine
USER1	USER2, USER3

User Assign Alpha 2 table below

	Spare(postcode district)	Outlet Rating	Price List
USER2	PE12	Active	Band 1
USER3	BN18	Propect	Band 2

Joseph_Seddik

Would you please post screenshots of your three tables and column configuration inside each table? Thanks.

Phil_Waite

sdfsfd

Phil_Waite

I have attached the three tables

Alpha and Bravo (in table 3) each return Outlet 1 and Outlet 2 from the main table and the long expression works if the user defined values of Alpha and Bravo are in their own columns [First call list to combine] and [Second Call list to combine] in the 2nd table.

I want to have Alpha and Bravo in the [First Call list to combine] column as an enumlist so that as many [User Id]'s can be selected from the third table as the user wants without adding a new part to the expression for each, 2nd, 3rd, 4th [Call list to Combine].

Full List Table
Outlet No	Name	Outlet Rating	Price List	Spare
1	Example Outlet	Active	Band 3	GU47
2	Example 2 outlet	Email Only	Band 3	TN39

User Assign Alpha
Unique Id	User Id	First Call List To Combine	Second Call List To Combine	Third Call List To Combine	Fourth Call List To Combine	Fifth Call List To Combine	Sixth Call List To Combine	Seventh Call List To Combine	Eighth Call List To Combine	Ninth Call List To Combine
9E3F3C3B	bob@listersbrewery.com	Alpha	Bravo	0	0	0	0	0	0	0


User Assign Details 2
Unique Id	Postcode	District		User Id	Outlet Rating	Counties To Include	Call Next Run	User Con	User Type	Price Band
Call List 1		GU47 , BN1 , BN3		Alpha	Active , Active HQ					Band 3 , Custom , Band 2
Call List 2		GU47 , BN1 , BN3		Alpha2	Prospect, Email Only					Band 3 , Custom , Band 2
Call List 3		TN39 , TN21 , TN33		Bravo	Email Only					Band 3 , Custom , Band 2
Call List 4		TN39 , TN21 , TN33		Bravo2	Prospect , Inactive But Revisit					Band 3 , Custom , Band 2

Phil_Waite

ff

Phil_Waite

How the Second table would look with enumlist

User Assign Alpha
Unique Id	User Id	First Call List To Combine
9E3F3C3B	bob@listersbrewery.com	Alpha, Bravo

Steve

Expression 1, formatted:

OR(
  AND(
    [Assign Outlet to User] = USEREMAIL(),
    [Temporary Prospect Next Call] > TODAY() - 200
  ),
  (
    AND(
      IN(
        [Spare],
        SELECT(
          User Assign Details 2[District],
          [User id] = LOOKUP(
            USEREMAIL(),
            "User Assign Alpha",
            "User Id",
            "First Call List To Combine"
          )
        )
      ),
      IN(
        [Outlet Rating],
        SELECT(
          User Assign Details 2[Outlet Rating],
          [User id] = LOOKUP(
            USEREMAIL(),
            "User Assign Alpha",
            "User Id",
            "First Call List To Combine"
          )
        )
      ),
      IN(
        [Price List],
        SELECT(
          User Assign Details 2[Price Band],
          [User Id] = LOOKUP(
            USEREMAIL(),
            "User Assign Alpha",
            "User Id",
            "First Call List To Combine"
          )
        )
      ),
      [Temporary Prospect Next Call] > TODAY() - 200,
      OR(
        AND(
          [Assign Outlet to User] = USEREMAIL(),
          [Temporary Prospect Next Call] > TODAY() - 200
        ),
        [Assign Outlet to User] = LOOKUP(
          USEREMAIL(),
          "User Assign Alpha",
          "User Id",
          "User Id Viewer"
        ),
        [Assign Outlet to User] = "",
        [Assign Outlet to User] = "NONE"
      )
    ),
    AND(
      [Assign Outlet to User] = LOOKUP(
        USEREMAIL(),
        "User Assign Alpha",
        "User Id",
        "User Id Viewer"
      ),
      [Temporary Prospect Next Call] > TODAY() - 200
    )
  )
)

Steve

Second expression, formatted:

OR(
  AND(
    [Assign Outlet to User] = USEREMAIL(),
    [Temporary Prospect Next Call] > TODAY() - 200
  ),
  (
    AND(
      IN(
        [Spare],
        SELECT(
          User Assign Details 2[District],
          IN(
            [User id],
            LOOKUP(
              USEREMAIL(),
              "User Assign Alpha",
              "User Id",
              "First Call List To Combine"
            )
          )
        )
      ),
      IN(
        [Outlet Rating],
        SELECT(
          User Assign Details 2[Outlet Rating],
          IN(
            [User id],
            LOOKUP(
              USEREMAIL(),
              "User Assign Alpha",
              "User Id",
              "First Call List To Combine"
            )
          )
        )
      ),
      IN(
        [Price List],
        SELECT(
          User Assign Details 2[Price Band],
          IN(
            [User Id],
            LOOKUP(
              USEREMAIL(),
              "User Assign Alpha",
              "User Id",
              "First Call List To Combine"
            )
          )
        )
      ),
      [Temporary Prospect Next Call] > TODAY() - 200,
      OR(
        AND(
          [Assign Outlet to User] = USEREMAIL(),
          [Temporary Prospect Next Call] > TODAY() - 200
        ),
        [Assign Outlet to User] = LOOKUP(
          USEREMAIL(),
          "User Assign Alpha",
          "User Id",
          "User Id Viewer"
        ),
        [Assign Outlet to User] = "",
        [Assign Outlet to User] = "NONE"
      )
    ),
    AND(
      [Assign Outlet to User] = LOOKUP(
        USEREMAIL(),
        "User Assign Alpha",
        "User Id",
        "User Id Viewer"
      ),
      [Temporary Prospect Next Call] > TODAY() - 200
    )
  )
)

Steve

Note that LOOKUP() only returns a singular value. If the column LOOKUP() is told to get contains a List or EnumList value, the list is made into a textual value, which doesn't work properly with IN(). Consequently, to use the value provided by LOOKUP() as a list, you must use SPLIT() to convert the textual value back into a list.

OR(
  AND(
    [Assign Outlet to User] = USEREMAIL(),
    [Temporary Prospect Next Call] > TODAY() - 200
  ),
  (
    AND(
      IN(
        [Spare],
        SELECT(
          User Assign Details 2[District],
          IN(
            [User id],
            SPLIT(
              LOOKUP(
                USEREMAIL(),
                "User Assign Alpha",
                "User Id",
                "First Call List To Combine"
              ),
              " , "
            )
          )
        )
      ),
      IN(
        [Outlet Rating],
        SELECT(
          User Assign Details 2[Outlet Rating],
          IN(
            [User id],
            SPLIT(
              LOOKUP(
                USEREMAIL(),
                "User Assign Alpha",
                "User Id",
                "First Call List To Combine"
              ),
              " , "
            )
          )
        )
      ),
      IN(
        [Price List],
        SELECT(
          User Assign Details 2[Price Band],
          IN(
            [User Id],
            SPLIT(
              LOOKUP(
                USEREMAIL(),
                "User Assign Alpha",
                "User Id",
                "First Call List To Combine"
              ),
              " , "
            )
          )
        )
      ),
      [Temporary Prospect Next Call] > TODAY() - 200,
      OR(
        AND(
          [Assign Outlet to User] = USEREMAIL(),
          [Temporary Prospect Next Call] > TODAY() - 200
        ),
        [Assign Outlet to User] = LOOKUP(
          USEREMAIL(),
          "User Assign Alpha",
          "User Id",
          "User Id Viewer"
        ),
        [Assign Outlet to User] = "",
        [Assign Outlet to User] = "NONE"
      )
    ),
    AND(
      [Assign Outlet to User] = LOOKUP(
        USEREMAIL(),
        "User Assign Alpha",
        "User Id",
        "User Id Viewer"
      ),
      [Temporary Prospect Next Call] > TODAY() - 200
    )
  )
)

Steve

There is an extraneous parenthesis in your expression:

Also seen in my reformatted view:

Why is it there? I would expect it to prevent the expression from working as intended even if Expression Assistant doesn't report an error.

Phil_Waite

I'm not sure why that is there.

I still can't get the expression to combine the two critera that have been set out in the third table.

Instead i am adding a virtual column to the first main table to give a value to each row (alpha or bravo for example) based on whether the main table row/outlet meets the criteria of those.

Then I will just use a slice to show rows in the main table where the new vitual column has a value that appears in the enumlist [First call to combine]

Here is the VC for the main table - but I can't get that to work and it should be a simple one!

I'm getting no result!

SELECT(User Assign Details 2[User ID], AND(IN([Spare],User Assign Details 2[District]),

IN([Outlet Rating],User Assign Details 2[Outlet Rating]),

IN([Price List],User Assign Details 2[Price Band])))

Phil_Waite

Just one column lookup works and gives a result of "Alpha"

ANY(SELECT(User Assign Details 2[User ID], IN([Outlet Rating],User Assign Details 2[Outlet Rating])))

Neither of the others work for some reason. All are an enumnlist in the lookup table.

The outlet rating is an enum and so is the price list in the main table and the "spare" is text.

Steve

Try this instead:

ANY(
  SELECT(
    User Assign Details 2[User ID],
    ISNOTBLANK(
      INTERSECT(
        [Outlet Rating],
        SPLIT(("" & User Assign Details 2[Outlet Rating]), " , ")
      )
    )
  )
)

Note that it'd be a lot easier to help you if you could explain in plain language what you are trying to accomplish. Your table and column names are not at all informative, which will make it very difficult for someone else to make changes to your app should you be unavailable.