Create collection with custom column

[Yerlasdutco]

Hi Folks,

I created 2 lists on sharepoint. 

I need to create Collection with 2 columns: New_tagnumber : table1.newtagnumber; and Status column, that will be Complete if Table2.new_tagnumber existing in Table1.new_tagnumberid, else "Non complete"

[Waegemma]

Hi @Yerlasdutco ;

This should get you a collection (colStatus) that has the newtagnumbers from table2, and a status linked to the presence these newtagnumbers in table1:

ClearCollect(colInit,ShowColumns(table2, "newtagnumber"));
ClearCollect(colStatus,AddColumns(colInit,"Status", If(IsEmpty(LookUp(table1, newtagnumberid = ThisRecord.newtagnumber)),"Non Complete","Complete")))

Marc

[Yerlasdutco]

I received Error: 

my code is:

ClearCollect(colInit,ShowColumns('TagNumber table', "new_tagnumber"));
ClearCollect(colStatus,AddColumns(colInit,"Status", If(IsEmpty(LookUp('Drawing table', 'TagNumber table'.new_tagnumberid = 'Drawing table'.new_tagnumber)),"Non Complete","Complete")))

what is problem here?

[Waegemma]

Hi @Yerlasdutco ,

Presuming you want to check if a new_tagnumberid from the 'TagNumber Table' has an entry in the 'Drawing Table':

ClearCollect(colInit,ShowColumns('TagNumber table', "new_tagnumberid"));
ClearCollect(colStatus,AddColumns(colInit,"Status", If(IsEmpty(LookUp('Drawing table', new_tagnumber = ThisRecord.new_tagnumberid)),"Non Complete","Complete")))

Marc

[Yerlasdutco]

@Waegemma 

No success, If(IsEmpty(LookUp can't compare column from different tables

Any other ways to compare?

[Waegemma · Accepted solution]

Hi @Yerlasdutco ,

This should work (tested it several times 🙂 )

ClearCollect(colInit, ShowColumns('TagNumber table', "new_tagnumberid"));
ClearCollect(colStatus, AddColumns(colInit,"Status"; "Non Complete"));
UpdateIf(colStatus, new_tagnumberid in 'Drawing table'.new_tagnumber, {Status: "Complete"})

Grtz,

Marc

If it does the job, please accept this as a solution.

[Yerlasdutco]

@Waegemma 

Great, Thank you.

I made some changes, but works well