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,
