How to Pick The Correct Coding in Col B?

[desmond_lee]

Hi There

I would like an expression that pick the coding column value correctly. The condition is as per the table. For example [ETHINIC GROUP] =CHINESE, INDIAN, MALAY, OTHER [TYPE]=STAFF will pick CODING value what start with "S" and in this example is S753. And also CONTRACT and INTERN need only one condition.

I use this and it partially work

ANY(
SELECT(
CODING EMPLOYEE ID[CODING],
OR(
AND(
IN([_THISROW].[Ethnic Group], LIST("CHINESE", "INDIAN", "MALAY", "OTHERS")),
[_THISROW].[Type] = "STAFF"
),
AND(
IN([_THISROW].[Ethnic Group], LIST("CHINESE", "INDIAN", "MALAY", "OTHERS")),
[_THISROW].[Type] = "LOCAL"
),
AND(
IN([_THISROW].[Ethnic Group], LIST("MYANMAR", "NEPAL", "PHILIPPINES")),
[_THISROW].[Type] = "FOREIGNER"
)
)
)
)

[WillowMobileSys · Accepted solution]

I think you are confusing yourself.  Your expression should be simply like this:

ANY(
    SELECT(CODING EMPLOYEE ID[CODING], 
           AND(IN([_THISROW].[Ethnic Group], [Ethnic Group]),
               [_THISROW].[Type] = [Type]
           )
    )
)

This should work for INTERN and CONTRACT as well since the Type column will be blank.

I hope this helps!

[WillowMobileSys · Accepted solution]

I think you are confusing yourself.  Your expression should be simply like this:

ANY(
    SELECT(CODING EMPLOYEE ID[CODING], 
           AND(IN([_THISROW].[Ethnic Group], [Ethnic Group]),
               [_THISROW].[Type] = [Type]
           )
    )
)

This should work for INTERN and CONTRACT as well since the Type column will be blank.

I hope this helps!

[desmond_lee]

Thank you. Basically that 2 cell D2, D3 is blank but at the form enter to another table this is not blank . So it does not work very well. This table is the expected output at the form. It mean at the form if I select STAFF and CHINESE it pull S753 but If I replace the STAFF it change to 5285. The rest will be as follow

[WillowMobileSys]

---

@desmond_lee wrote:

It mean at the form if I select STAFF and CHINESE it pull S753 but If I replace the STAFF it change to 5285.

---

I see.  You are treating the blanks as "don't care" setting - meaning they could be anything.  In that case just a slight adjustment to the expression will work:

ANY(
    SELECT(CODING EMPLOYEE ID[CODING], 
           OR( 
              IN([_THISROW].[Type], {"STAFF", "LOCAL"}),
              IN([_THISROW].[Ethnic Group], {"INTERN", "CONTRACT"}),
              AND(IN([_THISROW].[Ethnic Group], [Ethnic Group]),
                  [_THISROW].[Type] = [Type]
               )
           )
    )
)

I hope this helps!

[desmond_lee]

Something is wrong somewhere. Couldn't figure where is it

ANY(
SELECT(CODING EMPLOYEE ID[CODING],
OR(
IN([_THISROW].[TYPE], {"STAFF", "LOCAL"}),
IN([_THISROW].[ETHNIC GROUP], {"INTERN", "CONTRACT"}),
AND(IN([_THISROW].[ETHNIC GROUP], [ETHNIC GROUP]),
[_THISROW].[TYPE] = [TYPE]
)
)
)
)

[desmond_lee]

Found it !

ANY(
SELECT(CODING EMPLOYEE ID[CODING],
OR(
IN([_THISROW].[TYPE], {"STAFF", "LOCAL"}),
IN([_THISROW].[ETHNIC GROUP], {"INTERN", "CONTRACT"}),
AND(IN([_THISROW].[ETHNIC GROUP], Coding Employee ID[ETHNIC GROUP]),
[_THISROW].[TYPE] = [TYPE]
)
)
)
)

[desmond_lee · Accepted solution]

Thank you WillowMobileSys. I got it

IF(
IN([_THISROW].[ETHNIC GROUP], {"MYANMAR", "NEPAL", "PHILIPPINES"}),
ANY(
SELECT(CODING EMPLOYEE ID[CODING],
AND(
[_THISROW].[Ethnic Group] = [Ethnic Group],
[_THISROW].[TYPE] = "FOREIGNER"
)
)
),
ANY(
SELECT(CODING EMPLOYEE ID[CODING],
AND(
IN([_THISROW].[TYPE], {"STAFF", "LOCAL", "INTERN"}),
[TYPE] = [_THISROW].[TYPE],
ISNOTBLANK([_THISROW].[ETHNIC GROUP])
)
)
)
)