Filter Collection by a field and last record

[Muertepelá]

Hi, 

I have a couple of issues,

I have a collection called "RegistroSistemas" which is the results of filtering by a field called "Subsystem" of sharepoint list, it works fine.
In a brownsegallery shows the "RegistroSistemas" table, in this colecction I have several items with different values in other column, but what I want to do is just  show  the last two records of that collections by item, I have tried filter but it returs the last two records of the whole table, that means only two, I attached a picture to try clarify. 

Next level, once a submit form update my data source I would like to show on that browse gallery just the records that have not been update during this year. 

Any help it would be very appreciated. 

Thanks in advanced.

[v-xida-msft]

Hi @Muertepelá ,

Which column do you use to store the "MPS02002-09-C-1-C" value within your 'RegistroSistemas' collection? The POINT column?

Do you only want to display the latest two records for each MPS Item within your Gallery?

I have made a test on my side, please consider take a try with the following workaround:

Add the following formula within the OnSelect property of the "Subsystem" button:

ClearCollect(RegistroSistemas; Filter(Registros; Subsistema = DropSubsistema.Selected.SUBSYSTEM));;
ClearCollect(FinalResult);;
ForAll(
     GroupBy(
          SoftByColumns(
                    Filter(RegistroSistemas; TextSearchBox1.Text in POINT);
                    "POINT";
                    Descending;
                    "Fecha";
                    Descending
          );
          "Subsistema";
          "POINT";
          "GroupData"
     );
     ForAll(
            FirstN(GroupData; 2);
            Collect(
                    FinalResult;
                    {
                      Subsystem: Subsistema;
                      Point: POINT;
                      Date: Fecha
                    }
           )
     )
)

then set the Items property of the Gallery to following:

FinalResult

within your Gallery, you should reference the related column values through the following formula (set the Text property of Label to following😞

ThisItem.Subsystem

ThisItem.Point

ThisItem.Date

Please consider take a try with above solution, check if the issue is solved.

For your second question, do you want to show these records have not been updated this year? If you want to show these records have not been updated this year, you need to add additional filter condition to the Filter formula within the OnSelect property of the "Subsystem" button:

ClearCollect(RegistroSistemas; Filter(Registros; Subsistema = DropSubsistema.Selected.SUBSYSTEM; Year(Modified) <> Year(Today())));;   // Add formula here
ClearCollect(FinalResult);;
ForAll(
     GroupBy(
          SoftByColumns(
                    Filter(RegistroSistemas; TextSearchBox1.Text in POINT);
                    "POINT";
                    Descending;
                    "Fecha";
                    Descending
          );
          "Subsistema";
          "POINT";
          "GroupData"
     );
     ForAll(
            FirstN(GroupData; 2);
            Collect(
                    FinalResult;
                    {
                      Subsystem: Subsistema;
                      Point: POINT;
                      Date: Fecha
                    }
           )
     )
)

Best regards,

[Muertepelá]

Hi @v-xida-msft 

Thank you for your answer but I am still strugguling with the issue and I will try explain it better. 

I have a table with about 6000 register and growing up¡, in share point,  I use it as record data base of some measurement, the identifed name is called "POINT" and I store the value of that point at the column "RESULTADO" and the date of that measurement at the column "Fecha", beside I have severals column with information about the point like sistem which it belongs or its specification.  So, the column POINT can have several items with the same name but distinc date and values. 

In order to avoid delegate fuction, I created collection tables to filter, so far so good, I think, but I would like to created a gallery to display the data grouping by POINT and in a sub-level the dates for each point in order to know if during current year I have measurement of that point if not I go to register form bringing the all data of each column for the point selected at the gallery, except its value , column "Resultado", to register a new one. 

I have tried you suggestion but I am doing something wrong because it gives me a error of syntax, I think. Can you plese have a look and explain what I am doing wrong? 

But thanks to you suggestion, I have explored other ways, and I think I got it , but I have to check it yet. The follwing picture tries to explain it, but basically, putting a gallery inside other gallery. Thanks to @PaulD1 for his video "SubGallery using GroupBy"

The issue right now  is that when I created a details screen on the selected Item at the gallery.selected I dont get to show the data from other columns because is a nested table collection, only appers "POINT" and "GroupPoint", I can not see the way to do it. Despite of I tried put a reference like ThisItem.GroupPoint.XXXX but give me a error I supose that it is because there are several register with the same point. 

 If you or anybody could give me a clue to solve it , it will be aprecited. 

Many thanks

[v-xida-msft · Accepted solution]

Hi @Muertepelá ,

Based on the formula syntax error screenshot, I think you have not referenced proper column value as "Group By" column in your GroupBy function.

On your side, please make sure you have referenced proper column names within your GroupBy function as group column. These Group columns must be come from SortByColumns formula result.

Do you add a nested Gallery within your Main Gallery to list the "Fecha" value? Yes, I agree with this solution, another way to achieve your needs. But there is something wrong with your nested Gallery in your app.

I have made a test on my side, please consider take a try with the following workaround:

Add a nested Gallery within your Main Gallery, set the Items property of the nested Gallery to following:

ThisItem.GroupPoint

if you just want to show the latest two records, please type the following formula:

Sort(ThisItem.GroupPoint; Fecha; Descending)

within the nested Gallery, add a Label, set the Text property to following:

ThisItem.Fecha

set the OnSelect property of the ">" icon in your nested Gallery to following:

Navigate(DetailScreen1; ScreenTransition.Fade; {ItemID: ThisItem.ID})

Within your Detail Screen, set the Item property of the Display Form to following:

LookUp('Your SP List'; ID = ItemID)

Note: Connect your Display form to your SP list (Registros)

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[Muertepelá]

HI @v-xida-msft ,

Thank you¡¡ It works¡¡¡

I did not undestand very well  how works the reference to take the data to other form from galleries, now It is a little bit  clearer.
I am strugguling yet with the fuction group by, but let me practise and think about it,  I am completely new with power apps, if could not understand well, I hope you dont mind I will go back to the issue. 

Thanks.