cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Collection with look up on other collection.

Hi everybody.

 

First sorry for my english i'll try to explain as better as i can my problem.

I have two SPT lists.

I want to creat 2 collections, and one of these collections with an added column from a lookup in the other collection. I'v seen a lot of subjects on this but for me that doesn't work.

 

I've an error on the '=', and if i replace it by an 'in', the lookup function just get me a unique value as you can see on the table who show the two field of the collection CollectionBordereauTM, the first filed is 'Nom de bordereau'.

 

hope you can help me.

 
 

Collection.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Collection with look up on other collection.

hi @v-yutliu-msft ,

 

thanks for your support.

 

I tried by deleting all the collections, save and restarted and that worked.

 

I used for one field 'Title' because this is his name in SPT and simplified the columns that i keep in the first collection and it worked

 

ClearCollect(CollectionCoefActu;ShowColumns('Base de donnee coef actu devis';"Agence";"Title";"CoefficientDactualisation"));;
Concurrent(
    ClearCollect(CollectionBordereauTM;AddColumns('Base de donnée bordereaux TM';"PrixUnitaireActualise";LookUp(CollectionCoeffActu;Title='Nom du bordereau').CoefficientDactualisation)))

 

Thanks for your help.

 

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: Collection with look up on other collection.

Please copy and paste the formula

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Community Support
Community Support

Re: Collection with look up on other collection.

Hi @Olly34 ,

Do you want to combine two collections based on two fields with same data?

Could you tell me the data type of the fields in these two collections?

I assume that they are all text type.

If so, you just need to make a little change on your original formula.

Firstly, let me explain where's your problem.

LookUp(CollectionCoefActuDevis;'Nom du bordereau'=CollectionBordereauTM.NomDuBordereau;CoefficientDactualisation)

'Nom du bordereau' is a field, CollectionBordereauTM.NomDuBordereau is a table.

You can not compare a field with a table with the relationship of "equal". So using "=" between them will have error.

You could compare a field with a table by using "in". However, since lookup field will always return the first filtered record, so if you use "in" between them, you will get the same value in all records.

 

Secondly, how to modify your formula?

I've made a similar test, my test formula:

ClearCollect(test1,{field1:"a",field2:"aa"},{field1:"b",field2:"bb"},{field1:"c",field2:"cc"});
ClearCollect(test2,{field11:"a",field3:"aaa"},{field11:"b",field3:"bbb"},{field11:"c",field3:"ccc"})
ClearCollect(test3,AddColumns(test2,"field4",LookUp(test1,field1 = field11,field2)))

The formula that I use:

AddColumns(test2,"field4",LookUp(test1,field1 = field11,field2))

is just to compare two fields: field1 and field11. Then the AddColumns function will evaluate value one by one record, which will make every record has different value.

 

On your side, you should try this formula:

ClearCollect(CollectionCoefActuDevis;'Base de donnee coef actu devis');;
ClearCollect(CollectionBordereauTM;
             AddColumns('Base de donnee bordereaux TM';"PrixUnitaireActualise";
                         LookUp(CollectionCoefActuDevis;'Nom du bordereau'=NomDuBordereau;CoefficientDactualisation)
                        )
             )

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Collection with look up on other collection.

@v-yutliu-msft ,

 

yes i want to combine two collection with same fields data from SPT lists.

 

This are Text fields.

 

BaseBordereauTM.JPGBaseCoeffActualisation.JPG

 

I understand that if i use 'in' i will have same value for all records.

 

I tried your solution but that doesn't work.

 

I tried too with  CollectionBordereauTM.Nom_x0020_du_x0020_bordereau, but for him it's a table. same with CollectionBordereauTM.NomDuBordereau.

 

I don't know how to tell him that it's a field value.

Highlighted
Community Support
Community Support

Re: Collection with look up on other collection.

Hi @Olly34 ,

I notice the column in 'Base de donnee bordereaux TM' list is named 'Nom du bordereau' too, not NomDuBordereau.

Please check whether you enter right listnames and columns and change to the real listnames and columns that you use.

Since my language is not French, some of my words may not be right, you need to correct them.

Also, if two lists both have the field named 'Nom du bordereau'(exactly same name), you need to use [@listname]fieldname to avoid ambiguity.

Try this formula:

ClearCollect(CollectionCoefActuDevis;'Base de donnee coef actu devis');;
ClearCollect(CollectionBordereauTM;
             AddColumns('Base de donnee bordereaux TM';"PrixUnitaireActualise";
                         LookUp(CollectionCoefActuDevis;'Nom du bordereau'=[@'Base de donnee bordereaux TM']'Nom du bordereau';CoefficientDactualisation)
                        )
             )

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Collection with look up on other collection.

hi @v-yutliu-msft ,

 

thanks for your support.

 

I tried by deleting all the collections, save and restarted and that worked.

 

I used for one field 'Title' because this is his name in SPT and simplified the columns that i keep in the first collection and it worked

 

ClearCollect(CollectionCoefActu;ShowColumns('Base de donnee coef actu devis';"Agence";"Title";"CoefficientDactualisation"));;
Concurrent(
    ClearCollect(CollectionBordereauTM;AddColumns('Base de donnée bordereaux TM';"PrixUnitaireActualise";LookUp(CollectionCoeffActu;Title='Nom du bordereau').CoefficientDactualisation)))

 

Thanks for your help.

 

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,631)