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]
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โ)
)
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.
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])
)
)
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]
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.
Tks for the Quick answer
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.
User | Count |
---|---|
43 | |
26 | |
23 | |
14 | |
11 |