Code correction for Joining 2 collection based on one column

(1) Share

Report

Posted on by MIA

I have One collection and one table. Want to join them.
All the records of Collection and matching records of the another table.

Collection "colAllList" has all the machine data, in which a Column is there name "Serial"
Another Table name "TableMasterDVR1" has the machine monitoring data, in which a Column is there name "SerialNo"

I want to make another New collection with below condition:
All the data of Collection "colAllList" and the matching records from TableMasterDVR1
WHERE Serial = SerialNo

For that I tried the below code, which gives error of "invalid argument" on the group when hovered, and expected identifier name when hover over the new column name.

Please review my requirement and code and guide me correction

ClearCollect(MachinesDataTogether,
    Ungroup(
        DropColumns(
            AddColumns(
                colAllList,
                "KomtraxGroupedColumn",
                Filter(
                    TableMasterDVR1,
                    SerialNo = colAllList [@Serial]
                )
            ),
            "SerialNo"
        ),
        "KomtraxGroupedColumn"
    )
);

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (0)

WarrenBelz 153,640 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
You simply need to get the correct field names in the two data sources that you are comparing. The fact that this does not produce an error
Filter( TableMasterDVR1, SerialNo = cr333_serial )

suggests that it is correct. Based on what you have posted, it should work if you have matching records. Unless you have some new information, there is nothing much more I can comment on.

Was this reply helpful? Yes No
MIA 22 on at

Like (0)

Report
Copy link

Link copied!
Attn:

@WarrenBelz

sorry, the revised part of the code did not work. it get error and underline

Filter( TableMasterDVR1, cr333_serialno = cr333_serial )

it underline red for cr333_serialNo

====================================================

Regarding the Columns name, table name, collection name and data, I re-checked all, its all ok, No mistake.

To track mistake i started testing as below :

Filter(

TableMasterDVR1,

Text(SerialNo) = Text(cr333_serial)

)

Result : long time taken but not reading the data from TableMasterDVR1

==================================

Tested again

Filter(

TableMasterDVR1,

Value(SerialNo) = Value(cr333_serial)

)

Result : Here found error popup that it cannot convert XLL1793112, which is one the value of the record in SerialNo.

I am aware that my serial and SerialNo Data type is text in the dataverse table

================================================

Therefore : Its proves that the issue is the comparison of:

SerialNo = cr333_serial

IF this will work, then issue will be resolved.

Requesting you to advise, if any other way of compare inbetween this two field

So that I can give the try.

Rest all the code is ok, no error, only the comparison issue remains.

Was this reply helpful? Yes No
WarrenBelz 153,640 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
I cannot see your data and can only rely on the field names that you post (you will need to adjust any that you have not posted correctly). The resulting output relies on the accuracy of the matching data Table returned - maybe you need
Filter( TableMasterDVR1, cr333_serialno = cr333_serial )

I am not sure that else I can add here.

Was this reply helpful? Yes No
MIA 22 on at

Like (0)

Report
Copy link

Link copied!

Attn:

@WarrenBelz

sorry, I tried as you advised with the minor change as it was not accepting, therefore had to change Serial to cr333_serialno.

Now there is no error, but the result is the problem.

Its brings all the data from colAllList

BUT it does not bring the result from TableMasterDVR1

I checked both the place, in the collection table, and also checked in the gallery. For example, I inserted several text box, set the text properties related to the collection. which ever field is related to the colAllList is working and which ever I related to TableMasterDVR1 like

ThisItem.cr333_addfieldb

Its not working.

I dont know where is the mistake I am making

Kindly check and advise.

ClearCollect(

MachinesDataTogether,

DropColumns(

Ungroup(

AddColumns(

ShowColumns(

colAllList,

Serial,

CustomerName,

DelDate

),

KomtraxGroupedColumn,

Filter(

TableMasterDVR1,

SerialNo = cr333_serial

)

),

KomtraxGroupedColumn

),

cr333_serialno

)

)

Was this reply helpful? Yes No
WarrenBelz 153,640 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
OK - now I know the columns you need in the collection, this should work, however I note your comment on my data will grow. None of the code is remotely Delegable as ClearCollect, DropColumns, AddColumns and Ungroup are "local" operations and are all subject to your Data Row Limit.

ClearCollect( MachinesDataTogether, DropColumns( Ungroup( AddColumns( ShowColumns( colAllList, Model, Serial, CustomerName, DelDate ), KomtraxGroupedColumn, Filter( TableMasterDVR1, SerialNo = cr333_Serial ) ), KomtraxGroupedColumn ), SerialNo ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
MIA 22 on at

Like (0)

Report
Copy link

Link copied!

Attn:

@WarrenBelz

Thanks for your advise. I tried as you advise by a little change. Error gone but code result does not come up, it gets into continious search for few minutes i wait then I checked the table of collection via variable option, found no data there, at last has to close power app.

The code I used finally was :

ClearCollect(

MachinesDataWithKomtrax,

DropColumns(

Ungroup(

AddColumns(

TakeColumnOfMachData,

KomtraxGroupedColumn,

Filter(

TableMasterDVR1,

SerialNo = cr333_serial

)

),

KomtraxGroupedColumn

),

cr333_serialno

)

)

==================================================

Just to test to bring the data in another way. I tried the Lookup method, it worked, took time, but worked, the code I used is as below

ClearCollect(
TakeColumnOfMachData,
ShowColumns(
colAllList,
Model,Serial,CustomerName,DelDate
)
);

ClearCollect(
GetMachDataTogether,
AddColumns(
TakeColumnOfMachData As MainMachData,
ForAddMyColumn,
LookUp(
TableMasterDVR1,
SerialNo = MainMachData.cr333_serial
)

)
)

==================

My question remains, here is it ok, to work with Lookup, I am worried, as my data will grow.

Please guide

Was this reply helpful? Yes No
WarrenBelz 153,640 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It is not much use exploring other code - what has been supplied should work, except as I mentioned, you need to use either DropColumns or ShowColumns (whichever has the less to show or drop) when collecting colAllList and specify only the columns you (don't or do) need - that will avoid the duplication of Owner.

I have tested the code here with the exact syntax duplicating your data source and field names from two related test lists and it produces the expected results.

I have to assume that the field SerialNo is in tblMasterDR1 and the field Serial is in colAllList - you should not get an error on this if they were present.

Was this reply helpful? Yes No
MIA 22 on at

Like (0)

Report
Copy link

Link copied!

Attn:

@WarrenBelz

Regarding the Serial and SerialNo Column, I checked, both is ok, no mistake here.

Regarding the Owner, Its not my added column manually, Its added auto as both table is stored/belongs to Teams - Dataverse Table.

Lets avoid my tried code, The requirement I am posting again below Is there any other way to obtain this solution :

I have One collection and one table. Want to join them.
All the records of Collection and matching records of the another table.

Collection "colAllList" has all the machine data, in which a Column is there name "Serial"
Another Table name "TableMasterDVR1" has the machine monitoring data, in which a Column is there name "SerialNo"

I want to make another New collection with below condition:
All the data of Collection "colAllList" and the matching records from TableMasterDVR1
WHERE Serial = SerialNo

Please guide.

Was this reply helpful? Yes No
WarrenBelz 153,640 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You have two separate issues -

Firstly SerialNo- that is from your posted code (so I am not sure why it does not exist) where you have indicated that you are filtering TableMasterDVR1 based on the field SerialNo matching the field Serial in colAllList - I cannot see your data, but have tested the syntax on a test list here using the same structure and it is valid assuming those two fields match in the two tables.

The next one is that you seem to have a field called Owner in both lists - you will need to use DropColumns in collecting one to remove this.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
MIA 22 on at

Like (0)

Report
Copy link

Link copied!

Attn:

@WarrenBelz

The advise code did not work, no option to show, I made the image to show you the errors at 2 palces

Kindly advise

Was this reply helpful? Yes No