powerappspowerapps-formulapowerapps-collection

Collections code issue to generate required output


I have made 1 collection as below:

ClearCollect(
    DistinctCountries,
    ForAll(
        Distinct('MIS-EnquiryFormList-Stage', UserCountry),
        { CountryName: Value , 
          MinYear: Text(Min(Filter('MIS-EnquiryFormList-Stage', UserCountry = Value), Year(DateValue(SubmittedOn)))),
          MaxYear: Text(Max(Filter('MIS-EnquiryFormList-Stage', UserCountry = Value), Year(DateValue(SubmittedOn))))
        }  // 'Result' is the default column name from Distinct        
    )
);

Output of above Collection:

Distinct Countries Collection Output

Category Master List: There is another list with name 'CategoryMaster-List' as shown below:

Category List

Requirement: I want to create collection using 'DistinctCollection' & 'CategoryMaster-List' in format as below: Output Format based on range of years of each country

As you can see in the output image Each country is would have ML & MIS values based on the MIN & MAX years range. This would be the required output format.

Issue After googling I have prepared a code to achieve required output:

ClearCollect(
    FormattedCollection,
    ForAll(
        DistinctCountries,
        With(
            { Country: CountryName,
              MinYear: Value(MinYear),
              MaxYear: Value(MaxYear) },
            ForAll(
                Sequence(MaxYear - MinYear + 1, MinYear),
                Collect(
                    FormattedCollection,
                    ForAll(
                        Filter('CategoryMaster-List', ValuesFor = "MIS"),
                        {
                            Country: Country,
                            Year: MinYear - 1, // This correctly calculates the current year
                            MicroLink: Blank(),
                            MIS: ColumnValues
                        }
                    ),
                    ForAll(
                        Filter('CategoryMaster-List', ValuesFor = "ML"),
                        {
                            Country: Country,
                            Year: MinYear - 1, // Same calculation for year
                            MicroLink: ColumnValues,
                            MIS: Blank()
                        }
                    )
                )
            )
        )
    )
);

But there would be some syntax or any logical error. I am new to power app therefore unable to identify.


Solution

  • You can combine your tables with an expression like the one below:

    Clear(FormattedCollection);
    ForAll(
      DistinctCountries As dc,
      With(
        {
          Country: dc.CountryName,
          MinYear: Value(dc.MinYear),
          MaxYear: Value(dc.MaxYear)
        },
        ForAll(
          Sequence(MaxYear - MinYear + 1, MinYear) As years,
          ForAll(
            'CategoryMaster-List' As cml,
            Collect(
              FormattedCollection,
              {
                Country: Country,
                Year: years.Value,
                MIS: If (cml.ValuesFor = "MIS", cml.ColumnValues, Blank()),
                MicroLink: If (cml.ValuesFor = "ML", cml.ColumnValues, Blank())
              }
            )
          )
        )
      )
    )
    

    The two nested ForAll calls would go through all items of the two collections, and when adding the new records, it would populate either the MIS or MicroLink column, depending on the value of the CategoryMaster-List row.