Help working with a collection

[Anonymous]

Hello guys, I need some help working with a collection.

I have a sharepoint list with 600-700 records now, I want to use the Date filter on a gallery based on a collection.

So I set the OnStart to Collect, and then when a record is submitted, I clear it and do it again, and also when the records is edited.

*Please tell me if there is a better way to do this!

I have a doubt on how to erase the record from the original data source and also the collection, I tried to do the Remove function on a gallery but it asks from the source that is not the original anymore...

[Drrickryp]

Hi @Anonymous,

If you are working with a collection of a datasource say colDS, and you want to save it to both the collection and the datasource, say DS, you will need take two steps.  Forms will not save to a collection so on the form you need to specify the datasource that you made the collection from say DS.   The Item property of the form should be Lookup(ds,ID=Gallery1.Selected.ID).   In the OnSuccess property of the form, ClearCollect(colDS,DS) // This will clear the old collection and recollect it with the new or edited record in it.  

For removing the record from both the collection and the gallery's Items ie. colDS. You will need to have a button that does both steps as well.  Remove(DS, Lookup(colDS,ID=Gallery1.Selected.ID)); Remove(colDS, Lookup(Gallery1.Selected.ID)).  You need to do it in that order because if you remove the record from the gallery's collection, it won't be available to remove it from the DS.

Tip:  You are probably making it more difficult than it needs to be.  If your DS is <2000, you probably don't need a collection at all.  You can increase the Delegation limit of your app.  File->App Settings->Advanced Settings-> then raise the delegation limit from 500 to 2000

[Anonymous]

Thanks so much for the attention @Drrickryp..

But believe me, I need to do it on a collection, because im having a issue on delegations, i have around 4-5 texts filters on the Sharepoint list gallery. if I put the Date Filter together, some of the records does not show properly when filtering...I have tried on a collection and it worked perfectly...thats why I need...

I will try your steps later!

Thanks so much!

[Drrickryp]

That makes sense. Hope my suggestion works for you.

[Anonymous]

hey @Drrickryp, im Trying to use the collection, but when I have the logic to use my filters, some records are not showing up on the gallery 😞

When I use the simple :

Sort(
Filter(colAciona ;
StartsWith( Ativo; Filtro_Eqp_1.Text));
ID ; Descending)

it shows ok, but my logics on filter are very large and complicated, the new records when submitted, not appears on the gallery..o0

(If(And(Filtro_Corredor_1.Selected.Value <> "CORREDOR";
Filtro_Motivo_1.Selected.Value <> "MOTIVO" ;
Filtro_Status_1.Selected.Value <> "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);
Corredor = Filtro_Corredor_1.Selected.Value;
Motivo = Filtro_Motivo_1.Selected.Value;
Status_x0020_Atendimento = Filtro_Status_1.Selected.Value

);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value = "CORREDOR";
Filtro_Motivo_1.Selected.Value <> "MOTIVO" ;
Filtro_Status_1.Selected.Value <> "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);

Motivo = Filtro_Motivo_1.Selected.Value;
Status_x0020_Atendimento = Filtro_Status_1.Selected.Value
);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value <> "CORREDOR";
Filtro_Motivo_1.Selected.Value = "MOTIVO" ;
Filtro_Status_1.Selected.Value <> "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);
Corredor = Filtro_Corredor_1.Selected.Value;

Status_x0020_Atendimento = Filtro_Status_1.Selected.Value);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value <> "CORREDOR";
Filtro_Motivo_1.Selected.Value <> "MOTIVO" ;
Filtro_Status_1.Selected.Value = "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);
Corredor = Filtro_Corredor_1.Selected.Value;
Motivo = Filtro_Motivo_1.Selected.Value
);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value = "CORREDOR";
Filtro_Motivo_1.Selected.Value = "MOTIVO" ;
Filtro_Status_1.Selected.Value <> "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);

Status_x0020_Atendimento = Filtro_Status_1.Selected.Value
);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value = "CORREDOR";
Filtro_Motivo_1.Selected.Value = "MOTIVO" ;
Filtro_Status_1.Selected.Value = "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text)
);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value <> "CORREDOR";
Filtro_Motivo_1.Selected.Value = "MOTIVO" ;
Filtro_Status_1.Selected.Value = "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);
Corredor = Filtro_Corredor_1.Selected.Value
);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value <> "CORREDOR";
Filtro_Motivo_1.Selected.Value <> "MOTIVO" ;
Filtro_Status_1.Selected.Value = "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);
Corredor = Filtro_Corredor_1.Selected.Value;
Motivo = Filtro_Motivo_1.Selected.Value);
ID;
Descending
);
If(And(Filtro_Corredor_1.Selected.Value = "CORREDOR";
Filtro_Motivo_1.Selected.Value <> "MOTIVO" ;
Filtro_Status_1.Selected.Value = "Status");
Sort(
Filter(
colAciona;
StartsWith(
Ativo;
Filtro_Eqp_1.Text
);
StartsWith(
Prefixo_x0020_Trem;
Filtro_Trem_1.Text
);

Motivo = Filtro_Motivo_1.Selected.Value
);
ID;
Descending
)))))))))))

[Drrickryp]

Yikes!  Even without the language difference, the complexity of your formula made my eyes cross. Perhaps @Anonymous could assign one of the staff to take a look at it.

[Anonymous]

hahaha @Drrickryp, basically, the logics is that, if and filters has or no value, do a filter, if not, if and other filters has no value, do another filter...and this repeats a lots of times...

I discovered the problem, the issue here is that the collection does not show up (with this formula) when one of the two of the filters have BLANK VALUES, ....

https://powerusers.microsoft.com/t5/General-Discussion/Filter-Gallery-Help/m-p/163496

Maybe you can help me here @Drrickryp?