populate gallery from Excel table with over 10000 registers

[dvalenzuela]

Hi all,

I hope you can help me. I'm trying to populate a gallery from a Excel table with over 10,000 registers.

With 2,000 registers I don´t have problem.

I trying to use a collections with column called "PrimaryKey", but I have a problem 

In OnSelect property Button, I have this:

Concurrent(
Collect(CollectionA;Filter(ExcelTable;PrimaryKey<2000));
ClearCollect(CollectionB;Filter(ExcelTable;PrimaryKey>=2000 && PrimaryKey<4000));
ClearCollect(CollectionC;Filter(ExcelTable;PrimaryKey>=4000 && PrimaryKey<6000));
ClearCollect(CollectionD;Filter(ExcelTable;PrimaryKey>=6000 && PrimaryKey<8000))
)

When run the app, and check in File -> Collections, I have:

CollectionA with 1999 registers

CollectionB with 1 registers

CollectionC with 0 registers

CollectionD with 0 registers

I dont know why starting second collection I dont have any data or what can I do.

I change File --> Advanced configuration (500 --> 2000). Save the app and restart the app

Thanks in advanced

[WarrenBelz]

Hi @dvalenzuela ,

That is because you have used ClearCollect instead of Collect on all but the first one and need ClearCollect on the first.

Concurrent(
   ClearCollect(CollectionA;Filter(ExcelTable;PrimaryKey<2000));
   Collect(CollectionB;Filter(ExcelTable;PrimaryKey>=2000 && PrimaryKey<4000));
   Collect(CollectionC;Filter(ExcelTable;PrimaryKey>=4000 && PrimaryKey<6000));
   Collect(CollectionD;Filter(ExcelTable;PrimaryKey>=6000 && PrimaryKey<8000))
)

Please click Accept as solution 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 Thumbs Up.

[dvalenzuela]

Thanks for your response:

My original code is:

Concurrent(
Collect(CollectionA;Filter(ExcelTable;ClavePrimaria<2000));
Collect(CollectionB;Filter(ExcelTable;ClavePrimaria>=2000 && ClavePrimaria<4000));
Collect(CollectionC;Filter(ExcelTable;ClavePrimaria>=4000 && ClavePrimaria<6000));
Collect(CollectionD;Filter(ExcelTable;ClavePrimaria>=6000 && ClavePrimaria<8000))
)

and I tried changing the symbols for text: like: && -> And

Concurrent(
Collect(CollectionA;Filter(ExcelTable;ClavePrimaria<2000));
Collect(CollectionB;Filter(ExcelTable;ClavePrimaria>=2000 And ClavePrimaria<4000));
Collect(CollectionC;Filter(ExcelTable;ClavePrimaria>=4000 And ClavePrimaria<6000));
Collect(CollectionD;Filter(ExcelTable;ClavePrimaria>=6000 And ClavePrimaria<8000))
)

But always have this result:

CountRows(CollectionA) -> 1999

CountRows(Collectionb) -> 1

This Excel table is in Sharepoint List and OneDrive, same result for both

Thanks,

[WarrenBelz]

Ok @dvalenzuela ,

Actually I missed something in my first post as I answered an almost identical question yesterday - so please disregard this as I was on the wrong path to the problem.

Firstly, get rid of the Concurrent for the moment and you can put back ClearCollect if you are using different collections- you can actually do this is one - firstly do this as Excel I believe returns numbers as text at times.

ClearCollect(
   CollectionA;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)<2000
   )
);
ClearCollect(
   CollectionB;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=2000 && 
      Value(ClavePrimaria)<4000
   )
);
ClearCollect(
   CollectionC;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=4000 && 
      Value(ClavePrimaria)<6000
   )
);
ClearCollect(
   CollectionD;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=6000 && 
      Value(ClavePrimaria)<8000
   )
)

If that works, try this

ClearCollect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)<2000
   )
);
Collect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=2000 && 
      Value(ClavePrimaria)<4000
   )
);
Collect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=4000 && 
      Value(ClavePrimaria)<6000
   )
);
Collect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=6000 && 
      Value(ClavePrimaria)<8000
   )
)

You can certainly put the Concurrent back on the first one - you need to test the second if you need to do this.

Please click Accept as solution 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 Thumbs Up.

[Pstork1]

You can't work with that many records in an Excel file.  There is an upper limit.  You can Use the Excel connector to import the records into a collection, but you won't be able to write them back.

[WarrenBelz]

Thanks @Pstork1 ,

My next suggestion was actually to look at SharePoint.

[dvalenzuela]

Hi,

I used this:

Concurrent(
ClearCollect(
CollectionA;
Filter(
ExcelTable;
Value(ClavePrimaria)<2000
)
);
ClearCollect(
CollectionB;
Filter(
ExcelTable;
Value(ClavePrimaria)>=2000 &&
Value(ClavePrimaria)<4000
)
);
ClearCollect(
CollectionC;
Filter(
ExcelTable;
Value(ClavePrimaria)>=4000 &&
Value(ClavePrimaria)<6000
)
);
ClearCollect(
CollectionD;
Filter(
ExcelTable;
Value(ClavePrimaria)>=6000 &&
Value(ClavePrimaria)<8000
)
))

And the result was:

"Items ColecctionA: " & CountRows(CollectionA)&"-"&"Items ColecctionB: " & CountRows(CollectionB)&"-"&"Items ColecctionC: " & CountRows(CollectionC)&"-"&"Items ColecctionD: " & CountRows(CollectionD)

Items ColecctionA: 1999-

Items ColecctionB: 1-

Items ColecctionC: 0-

Items ColecctionD: 0

Sorry, I dont know why the second collection always load just one register.

Thanks for your help

[dvalenzuela]

Hi,

I have followed your suggestion, I create a new App connecting to a Sharepoint List, this is my code:

Concurrent(
ClearCollect(
CollectionA;
Filter(
'Sharepoint List';
Value(ID)<2000
)
);
ClearCollect(
CollectionB;
Filter(
'Sharepoint List';
Value(ID)>=2000 &&
Value(ID)<4000
)
);
ClearCollect(
CollectionC;
Filter(
'Sharepoint List';
Value(ID)>=4000 &&
Value(ID)<6000
)
);
ClearCollect(
CollectionD;
Filter(
'Sharepoint List';

But my problem persists:

Items ColecctionA: 1999-Items ColecctionB: 1-Items ColecctionC: 0-Items ColecctionD: 0
Value(ID)>=6000 &&
Value(ID)<8000
)
))

Thanks

[dvalenzuela]

Hi,

I have also tried removing  function Value() in ID, but the same problem.

I dont knows the second collection only have 1 register

Thanks

[dvalenzuela]

Maybe the problem is with Filter? I have a message with blue line over ID<2000 that says:

Maybe dont work with large size files

Thanks