IFS() dont work

Tiago_Pereira · 11-15-2021 09:19 AM

Hi

I’m trying to use this formula

IFS(

[_THISROW].[COMPANY]=LOOKUP([_THISROW].[COMPANY],“Pontos de venda”,“COMPANY”,“COMPANY”),
LOOKUP([_THISROW].[COMPANY],“Pontos de venda”,“COMPANY”,“Company_ID”),

[_THISROW].[Email da empresa]=LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“COMPANY_PERSON_EMAIL”,“COMPANY_PERSON_EMAIL”),
LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“COMPANY_PERSON_EMAIL”,“Company_ID”),

[_THISROW].[Email da empresa]=LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“EMAIL_1”,“EMAIL_1”),
LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“EMAIL_1”,“Company_ID”)

)

but only the first phase works
"[_THISROW].[COMPANY]=LOOKUP([_THISROW].[COMPANY],“Sale Points”,“COMPANY”,“COMPANY”),
LOOKUP([_THISROW].[COMPANY],“Sale Points”,“COMPANY”,“Company_ID”),
"
But when don’t find it in the first phase and move on to the second, it no longer works.
Is there something missing from the formula?

The key is [CRM_ID]

Steve

You’re using [_THISROW} where you shouldn’t be. Try this:

IFS(

[COMPANY]=LOOKUP([_THISROW].[COMPANY],“Pontos de venda”,“COMPANY”,“COMPANY”),
LOOKUP([_THISROW].[COMPANY],“Pontos de venda”,“COMPANY”,“Company_ID”),

[Email da empresa]=LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“COMPANY_PERSON_EMAIL”,“COMPANY_PERSON_EMAIL”),
LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“COMPANY_PERSON_EMAIL”,“Company_ID”),

[Email da empresa]=LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“EMAIL_1”,“EMAIL_1”),
LOOKUP([_THISROW].[Email da empresa],“Pontos de venda”,“EMAIL_1”,“Company_ID”)

)

Tiago_Pereira

WillowMobileSystems:

ANY(SELECT(Pontos de venda[Company ID], 
                        OR([Company] = [_THISROW].[Company], 
                           [Company Person Email] =  [_THISROW].[Email da empresa],
                           [Email 1] = [_THISROW].[Email da empresa])
          )
)

Hi Steve

I tried your solution but it doesn’t work, it’s the same. Do the first lookup and if you don’t find it, don’t do the second and third test.

WillowMobileSys

By the way, if ORDER of how you check the IFS doesn’t matter you can simplify the IFS into a SELECT like this:

ANY(SELECT(Pontos de venda[Company ID], 
                        OR([Company] = [_THISROW].[Company], 
                           [Company Person Email] =  [_THISROW].[Email da empresa],
                           [Email 1] = [_THISROW].[Email da empresa])
          )
)

Tiago_Pereira

Hi John
With this formula it always returns the first [company_id] it does not search for the key [CRM_id] of that line and returns the respective [company_id]

WillowMobileSys

There is some confusion then. Your IFS did NOT use [CRM_id] in anyway. It always returned [Company_ID].

I did mention that if order does NOT matter then you could use the SELECT. The issue with the SELECT is that depending on how your data is structured, there may be multiple rows that match. The SELECT then returns only the first row’s [Company_ID]. It seems this will not work for you.

It might be helpful to describe your data tables and what your goal is. We might be able to better help come up with a workable expression.

Tiago_Pereira

Tks for the Quick answer

Tiago_Pereira

What I’m trying to do is in the table “Customers” it compares the [Company] with the table “Points of sale” column [Company] and if it finds a match then in the table “Points of sale” extract the [Company_id].
If you don’t find it, go to the next search in this case the [Company E-mail] and if you find extract the [Company_id] in the third step the same thing. But it looks like he only does the logical test on the first one.