Answered

Collection LookUp 3 Sharepoint Lists

(0) Share

Report

Posted on by lagoutten

Good afternoon,

I could not find in the forums any solution to fix my problem.
Sorry if it has already been addressed and in that case could you please redirect me to the right topic ?

I would like to create a collection that would return the columns from 3 different Sharepoint lists.

The lists' structure is as per below:

t_address

id_address
ref_stakeholder
street1
street2
ref_city

t_city

id_city
city_name
ref_country

t_country

id_country
country_name

I would like to create a collection which returns

street1 (from t_address)
street2 (from t_address)
city_name (from t_city)
country_name (from t_country)

To be noted, that I want a specific line from t_address which is filtered with the value a control named ComboBox6_3 (that returns the stakeholder reference)

So far, I could get a collection street1, street2 and city_name, but when I insert LookUp to get the country, the collection is empty.

Current but non working code :

ClearCollect(
 collect_address;
 ForAll(
 Filter(t_adresse;ref_stakeholder = ComboBox6_3.Selected.ref_company_defaut);
 {
 IdAddress : id_address;
 Street_1 : street1;
 Street_2 : street2;
 CityName : LookUp(
 t_city;
 id_city = ref_city;
 city_name
 );
 CountryName: LookUp(
 t_country;
 id_country = LookUp(
 t_city;
 id_city = ref_city;
 ref_country
 );
 country_name
 )
 }
 )
)

Thanks for your help

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

QuentinDURIEZ 577 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi,

You can do something similar to
Collect(collect_address ShowColumns( AddColumns( AddColumns( Filter(t_adresse;ref_stakeholder = ComboBox6_3.Selected.ref_company_defaut) As address, "city", LookUp(t_city As city, city.id_city = address.ref_city, {name : city.city_name , country_name : LookUp(t_country, id_country = city.ref_country, country_name) }) ) As address_with_city , "country_name", address_with_city.country_name "city_name", address_with_city.city.name ), "street1", "street2", "city_name", "country_name" ) )

I wrote it on NotePad so maybe you will get some semantic errors, but the idea should work.

Regards,

Was this reply helpful? Yes No
Verified answer

lagoutten 55 on at

Like (1)

Report
Copy link

Link copied!
@QuentinDURIEZ .

Thanks for your feedback!

Shall admit there was an error (a miss shall I say) in my code.
Code was missing a Value() for the 2nd LookUp and it works working like that too.

ClearCollect(
collect_address;
ForAll(
Filter(t_adresse;ref_stakeholder = ComboBox6_3.Selected.ref_company_defaut);
{
IdAddress : id_address;
Street_1 : street1;
Street_2 : street2;
CityName : LookUp(
t_city;
id_city = ref_city;
city_name
);
CountryName: LookUp(
t_country;
id_country = Value(
LookUp(
t_city;
id_city = ref_city;
ref_country
)
);
country_name
)
}
)

Thanks again.

Regards

Was this reply helpful? Yes No