problem on lookup on three tables

[diegomarino]

hi,

i have a problem on lookup formula

i have three tables

1) Candidati (applicants), in which i have a IDCandidato and an ComuneID (which is the city)

2) Comuni, in which i have all the cities and the column IDcomune is the primary key for ComuneID present in Candidati table

3) calendario, in which i store every task related to my applicants, so there is a date and a CAndidatoID that's the foreign key for IDcandidato, so i can relate a task to an applicants

i wrote this formula

LookUp(
    Comuni;
    LookUp(
        Candidati;
        Candidato = IDcandidato;
        ComuneID
    ) = IDComune;
    Comune
)

i lookup in the CIties table (comuni) the ComuneID (that's the city related to an applicant) and i retun the "comune" column that's the city name

now, this formula gives me "false" as result, but if i just lookup for comuneiD i have the right result

LookUp(
    Candidati;
    Candidato = IDcandidato;
    ComuneID
)

why?

thanks

[v-monli-msft]

Hi @diegomarino ,

The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria. So your first formula that use a Lookup inside another Lookup does not make sense as one Lookup will already return a single record.

Regards,

Mona

[diego_marino]

thanks so what you suggest? maybe i should cut distance by storing th ComuniID in the calendar and then use lookup?